Tutorial :“Syntax error in INSERT INTO statement”. Why?



Question:

My code is below. I have a method where I pass in three parameters and they get written out to an MS Access database table. However, I keep getting a syntax error message. Can anyone tell me why? I got this example from the internet.

        private static void insertRecord(string day, int hour, int loadKW)      {          string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\LoadForecastDB.accdb";          OleDbConnection conn = new OleDbConnection(connString);            string ins = @"INSERT INTO Forecasts (Day, Hour, Load) VALUES (?,?,?)";            OleDbCommand cmd = new OleDbCommand(ins, conn);            cmd.Parameters.Add("@day", OleDbType.VarChar).Value = day;          cmd.Parameters.Add("@hour", OleDbType.Integer).Value = hour;          cmd.Parameters.Add("@load", OleDbType.Integer).Value = loadKW;            conn.Open();            try          {              int count = cmd.ExecuteNonQuery();          }          catch (OleDbException ex)          {              Console.WriteLine(ex.Message);          }          finally          {              conn.Close();          }      }  


Solution:1

I think this could be because your column names (Day and Hour) are also keywords. Maybe you can put ` (inverted single quotes) around them (that works in MySQL, don't know about MS Access)


Solution:2

Try changing

string ins = @"INSERT INTO Forecasts (Day, Hour, Load) VALUES (?,?,?)";   

To:

string ins = @"INSERT INTO Forecasts ([Day], [Hour], [Load]) VALUES (@day, @hour, @load)";   


Solution:3

Another option might be to refer bind variables with numbers:

    cmd.Parameters.Add(1, OleDbType.VarChar).Value = day;      cmd.Parameters.Add(2, OleDbType.Integer).Value = hour;      cmd.Parameters.Add(3, OleDbType.Integer).Value = loadKW;  

Note I do not know C#, but similar approach works for Java and JDBC.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »