Mysql parameter error with C#

Question

string sql = @"UPDATE ivendas SET nritem=(@rank:=(@rank+1)) where id_venda = 20";
try
{
    MySQLBase basemysql = new MySQLBase();
    MySqlCommand cmd = basemysql.connection.CreateCommand();
    cmd.CommandText = sql;
    cmd.CommandTimeout = 1000;
    cmd.Parameters.AddWithValue("@rank",0);
    cmd.ExecuteNonQuery();
    basemysql.Closer();
}
catch (Exception erro)
{
    string teste = erro.ToString();
    Console.WriteLine(teste);
}

Remembering my RANK and my counter. Therefore a parameter or variable.

My mistake and this one ->

"Mysql.Data.Mysqlclient.Mysqlexception (0x80004005): You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near ':=(0+1)) Where id_venda = 20' at line 1 r n at Mysql.Data.Mysqlclient.MySqlStream.Readpacket() r n in Mysql.Data.Mysqlclient.NativeDriver.Getresult(Int32& affectedRow, Int64& insertedId) r n in Mysql.Data.Mysqlclient.Driver.Getresult(Int32 statementId, Int32& affectedRows, Int64& insertedId) r n on Mysql.Data.Mysqlclient.Driver.Nextresult(Int32 statementId, Boolean force r n in Mysql.Data.Mysqlclient.MySqlDataReader.Nextresult() r n in Mysql.Data.Mysqlclient.MySqlCommand.Executereader(Commandbehavior behavior) n in Mysql.Data.Mysqlclient.MySqlCommand.Executenonquery() r n on Restaurante.Ivendas.Organizaritensdavenda(String idvenda)"

My bank

nritem | id_venda 
 2     | 20
 3     | 20
 4     | 20

I need you to stay like this:

nritem | id_venda 
 1     | 20
 2     | 20
 3     | 20

Answer 1

After the AP clarify better the answer would have to be different from the below:

Not possible without some trick with SQL itself. Could use a stored Procedure that has a variable that is being incremented. Or you could use a temporary auxiliary table that takes the values already incremented.

And you wouldn’t need to parameterize the query.

Maybe I can do some window function, but do not know how much Mysql is able to handle this or how to do since I have no experience this mechanism.

---

You probably want this:

@"UPDATE ivendas SET nritem = @rank + 1 where id_venda = 20"

However, if the value is 0, then do this, and you don’t even need parameters:

@"UPDATE ivendas SET nritem = 1 where id_venda = 20"

I put in the Github for future reference.

This exception capture does not make sense there, only causes problem, and the closure of the connection in this way can cause problems in some situations. I talk about it all the time.

Answer 2

Try with the following code:

  update itens set nritem = 
  ( 
      select 
          aux.iterator 
      from (
          select
          @i:=@i+1 AS iterator,
          nritem,
          venda
          from itens, (SELECT @i:=0) AS foo
          where venda = 20) as aux 
      where aux.venda = itens.venda 
      and aux.nritem = itens.nritem)
  where venda = 20;

Before execution:

Sqlfiddle: http://sqlfiddle.com/#! 9/fcd41/1

---

After execution:

Sqlfiddle: http://sqlfiddle.com/#! 9/5c0b6d/1