C# - MySQL syntax error for INSERT into DB

Question

I am encountering a MySqlException error in my program. It is a simple Test program where I am trying to insert log errors with time stamps into a table in my database.

// -------------PROGRAM----------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;  


string Log = "Sample Log Error";
string key = "11111";

string date;
string time;
int unixT;
TimeSpan unix_time;

// geting current date in yyyy-mm-dd format     
date = DateTime.UtcNow.ToString("yyyy-MM-dd"); 

//getting current time in hh:mm:ss format
time = string.Format("{0:HH:mm:ss tt}", DateTime.Now); 

// Getting Unix Time
unix_time = (System.DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0)); 

//converting Unix time to seconds
unixT = Convert.ToInt32(unix_time.TotalSeconds); 

MySqlConnection myConn = new MySqlConnection("Server=172.178.2.143;" +            "Port=3306;" + "Database=DB;" + "Uid=user;" + "Pwd=pass;");                

 try
 {
   myConn.Open();
 }
 catch (MySqlException e)
 {
   Console.WriteLine(e);
 }                   

 MySqlCommand comm1 = new MySqlCommand("INSERT INTO Logger(date_stamp,         time_stamp, log, unix_time, key) VALUES(@date_stamp, @time_stamp, @log, @unix_time, @key)", myConnection);

 //inserting each element from GPS split message into its respective table    and column in the database                
comm1.Parameters.AddWithValue("date_stamp", date);              
comm1.Parameters.AddWithValue("time_stamp", time);                
comm1.Parameters.AddWithValue("log", Log);                
comm1.Parameters.AddWithValue("unix_time", unixT);
comm1.Parameters.AddWithValue("key", key);

try
{
  comm1.ExecuteNonQuery(); //executing the INSERT Query Above
}

catch (MySqlException e)               
{                    
  Console.WriteLine(e);                
}

myConnection.Close();

// ------------END PROGRAM------------------

I am getting the following 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 'key) VALUES('2013-09-13', '16:21:15 PM', 'Sample Log Error', 1379103676, '11111'' at line 1"

Steve · Accepted Answer · 2013-09-13 20:49:25Z

The word KEY is a reserved keyword for MySql

If you want to use that name in your code you need to add backticks around it

 MySqlCommand comm1 = new MySqlCommand("INSERT INTO Logger(date_stamp," + 
                          "time_stamp, log, unix_time, `key`) " + 
                          "VALUES(@date_stamp, @time_stamp, @log, @unix_time, @key)",
                           myConnection);

If it is not too late in the development stage of your application I suggest you to change that name because every time you touch this field you will have the same problem to remember.

I wish also to suggest the using statement around your code and just one try catch

try
{
    using(MySqlConnection myConn = new MySqlConnection(.....))
    using(MySqlCommand comm1 = new MySqlCommand(......))
    {
         myConn.Open();
         comm1.Parameters.AddWithValue("date_stamp", date);              
         .....
         comm1.ExecuteNonQuery(); 
    }
}
catch (MySqlException e)               
{                    
    Console.WriteLine(e);                
}

In this way you get a correct handling of edge case scenarios (fail to open the connection, fail to execute the command) and your connection will be correctly closed and disposed when the code flows out of the closing brace.

Sammitch · Accepted Answer · 2013-09-13 20:41:40Z

0

key is a reserved word, wrap it in backticks so that MySQL knows you're talking about a column name.

answered Sep 13, 2013 at 20:41

Sammitch

32.5k7 gold badges57 silver badges92 bronze badges

Collectives™ on Stack Overflow

C# - MySQL syntax error for INSERT into DB

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related