Parameter error in MySQL with C #

Question

Parameter error in MySQL with C #

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

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 that my RANK is my accountant. So a parameter or variable.

My error is the one from here - >

"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 sales_id = 20 'at line 1 \ r \ n MySql.Data.MySqlClient.MySqlStream.ReadPacket () \ r \ n MySql.Data.MySqlClient.NativeDriver.GetResult (Int32 & affectedRow, Int64 & insertedId) \ r \ n MySql.Data.MySqlClient.Driver.GetResult (Int32 statementId, Int32 & affectedRows, Int64 & insertedId) \ r \ n MySql.Data.MySqlClient.Driver.NextResult (Int32 statementId, Boolean force) \ r \ n MySql.Data.MySqlClient.MySqlDataReader.NextResult () \ r \ n MySql.Data.MySqlClient.MySqlCommand.ExecuteReader (CommandBehavior behavior) \ r \ n MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery () \ r \ n Restaurante.Ivendas.OrganizarItensdaVenda (String idvenda) "

My bank

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

I need you to look like this:

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

mysql sql c# .net

asked by anonymous 16.01.2018 / 18:37

2 answers

1

Try 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: link

After execution:

SQLFiddle: link

16.01.2018 / 19:31

How to get the number of rows from a StringList and apply? C # Application Console. Read file Json

score 2 · Accepted Answer

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

It is not possible without some trick with SQL itself. You could use a stored procedure that has a variable that is incremented. Or you could use an auxiliary temporary table that takes values already incremented.

And I would not need to parameterize the query.

Maybe I can do some window function , but I'm not sure how MySQL is able to handle it 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 do not even need parameters:

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

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