C# MySql 0x80004005): an error in SQL syntax;

Question

an error in my code Occasionally(windows service). I've used breakpoint,but nothing got.Who can help me

 private void SetGPSIsLogOut(int shopId,int status)
    {
        MySqlConnection conn = ConnectDataBase();
        strcmd.Clear();
        strcmd.AppendLine("update multi_shop_gps_location_log set gps_is_logout="+status+" where shop_id=" + shopId + " ");
        MySqlCommand myCommand = new MySqlCommand(strcmd.ToString());
        myCommand.Connection = conn;
        try
        {
            conn.Open();
            myCommand.ExecuteNonQuery();
            conn.Close();
        }
        catch (MySqlException e)
        {
            UpdateServicestatus(-1);
            WriteLog("SetGPSIsLogOut 更新失败" + e);
        }
    }

exception:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'update multi_shop_gps_location_log set gps_is_logout=0 where shop_id=513' at line 2 在 MySql.Data.MySqlClient.MySqlStream.ReadPacket() 在 MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) 在 MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) 在 MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) 在 MySql.Data.MySqlClient.MySqlDataReader.NextResult() 在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) 在 MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() 在 ys_service.Service1.SetGPSIsLogOut(Int32 shopId, Int32 status) 位置 E:\service\service\Service1.cs:行号 645

help

please

First thing to do: stop embedding the values directly in your SQL. Use parameterized SQL instead. Even if you think it won't hurt for integers, it's worth getting into the habit. (Heck even numbers aren't really safe.) — Jon Skeet
Try using string variable in place of StringBuilder strcmd — Ranjan Kumar
I think you need to change concatenated values with parameters: update multi_shop_gps_location_log set gps_is_logout=@status where shop_id=@shopId & use a string variable for MySqlCommand. Note that parameters must be declared as value type of int. — Tetsuya Yamamoto

apomene apomene · Accepted Answer · 2017-08-10T09:34:52

The error is caused due to the fact that you are not using quotes on your concatenated query string.

However the correct way is to use parameters and not string concatenation:

...
string query ="update multi_shop_gps_location_log set gps_is_logout=@status where shop_id=@shopId";
MySqlCommand myCommand = new MySqlCommand(query);
myCommand.Parameters.Add("@status",status);
myCommand.Parameters.Add("@shopId ",shopId );
...

C# MySql 0x80004005): an error in SQL syntax;

1 Answers