0

I have this simple method that is supposed to insert a row into a DB. It is throwing an exception.

private void AddToLiveQueue(int user_id, bool release = false)
    {
        string sql = "INSERT INTO live_support_queues (user_id, release, created_at, updated_at)";
        sql += " VALUES(?user_id, ?release, ?created_at, ?created_at)";

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;

        cmd.Parameters.AddWithValue("?user_id", user_id);
        cmd.Parameters.AddWithValue("?release", release);
        cmd.Parameters.AddWithValue("?created_at", DateTime.UtcNow);


        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            SendEmail email = new SendEmail();
            email.Send(ex.Message + "      " + ex.ToString());
        }
    }

I am getting this error: 

"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 'release, created_at, updated_at) VALUES(70, 0, '2017-09-22 23:00:16.686741', '20' at line 1"

Here is the SQL columns

Any help is greatly appreciated. Thanks!

Devon Quick
  • 348
  • 1
  • 17
  • If you debug this, pull the values out and run the statement exactly on the database, what happens? – Seano666 Sep 22 '17 at 23:12
  • the id is auto-generated and is not in the insert statement. the user_is is not auto-generated and is in the insert statement. – Devon Quick Sep 22 '17 at 23:12
  • Can you print the SQL generated and post it? That's usually an excellent debug check. – Simon Hobbs Sep 22 '17 at 23:13
  • I cant print the generated SQL statement because i am using parameters. see here: https://stackoverflow.com/questions/16971267/get-generated-sql-statement-from-mysqlcommand But the user_id = 70, the release is 0 – Devon Quick Sep 22 '17 at 23:24
  • I could be remembering wrong, but I seem to recall AddWithValue() not handling bool arguments well. That said, your error seems to indicate MySQL is having an issue well before it would reach that; is it possible `user_id` or `live_support_queues` have a trailing whitespace in their names? – Uueerdo Sep 22 '17 at 23:32

1 Answers1

3

release is a reserved word, and needs escaped with ` symbols if used as an identifier.

https://dev.mysql.com/doc/refman/5.7/en/keywords.html

Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • Thanks! I appreciate it. – Devon Quick Sep 22 '17 at 23:38
  • in future when an a query doesn't run, try to extract the exact query and run it for testing... it will help you quickly solve such issues – Neville Nazerane Sep 22 '17 at 23:40
  • I find the best practice is to delimit all identifiers with the ` ; that way if a new reserved word is added (or a keyword that could cause some ambiguity) you're already covered. – Uueerdo Sep 22 '17 at 23:58