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!

5
  • If you debug this, pull the values out and run the statement exactly on the database, what happens? Commented Sep 22, 2017 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. Commented Sep 22, 2017 at 23:12
  • Can you print the SQL generated and post it? That's usually an excellent debug check. Commented Sep 22, 2017 at 23:13
  • I cant print the generated SQL statement because i am using parameters. see here: stackoverflow.com/questions/16971267/… But the user_id = 70, the release is 0 Commented Sep 22, 2017 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? Commented Sep 22, 2017 at 23:32

1 Answer 1

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

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

3 Comments

Thanks! I appreciate it.
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
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.