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
– Jon Skeet, Commented Aug 10, 2017 at 9:09
Try using string variable in place of StringBuilder strcmd — Ranjan Kumar
– Ranjan Kumar, Commented Aug 10, 2017 at 9:15
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
– Tetsuya Yamamoto, Commented Aug 10, 2017 at 9:27

apomene · Accepted Answer · 2017-08-10 09:34:52Z

3

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 );
...

answered Aug 10, 2017 at 9:34

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related