Why doesn't UPDATE c# work?

Question

SQLite

There is a query to the SQLite database:

UPDATE products SET manager = (
SELECT user FROM brandUsers WHERE firstCom IS NOT NULL AND prodBrand = brandUsers.brand AND firstCom = brandUsers.user
) WHERE manager IS NULL AND firstCom IS NOT NULL;

It works fine without errors and updates the data in SQLiteStudio,
but when I execute this code in VS, nothing is updated:

using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};", dbPath)))
{
  string sqlCom = [email protected]"UPDATE products SET manager = (
                SELECT user FROM brandUsers WHERE firstCom IS NOT NULL AND prodBrand = brandUsers.brand AND 
                firstCom = brandUsers.user
    ) WHERE manager IS NULL AND firstCom IS NOT NULL";
    
  connection.Open();
  
  SQLiteCommand command = new SQLiteCommand(sqlCom, connection);
  try
  {
    command.ExecuteNonQuery();
    connection.Close();
    return true;
  }
  catch
  {
    File.AppendAllText(Environment.CurrentDirectory + @"\SQLError.txt", DateTime.Now.ToString() + "\t" + sqlCom + Environment.NewLine);
    connection.Close();
    return false;
  }              
}

The whole brain is broken, I can not understand what could be the problem?

Answer 1

prodBrand = brandUsers.brand

just a string, you need either string.Format(), or use interpolation, or command parameters (which is best)

Answer 2

Perhaps a semicolon is required at the end of the expression?

Answer 3

Add an Exceprion to the catch block and, when debugging, run through this code and look at the error message.
It's just that if the query works fine in SQLiteStudio, then maybe your connection fails?

Answer 4

I don't know if it's still relevant or not. The first thing to check is whether auto-commit is enabled or not. Should be enabled by default.
sqlite3_get_autocommit
Second, the subquery must always return a scalar expression (one value per row).
You can use the Update method on sqlliteconnection. updates
is a package in nuget sqllite-net-pcl ,
well, this is completely extreme.
Better use a parameterized query and try splitting your query into two : select and update.