Insert query complaining about syntax?

Question

I am trying to insert record from c# window form to access 2007 database, but I am getting this error -

Error: Syntax error in INSERT INTO statement. A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

But I am not seeing anything wrong with my code-

                try
            {
                string sday = "Sun";
                s1 = comboBox180.SelectedItem.ToString();
                t1 = comboBox10.SelectedItem.ToString();
                d1 = comboBox17.SelectedItem.ToString();
                string bla="XYZ";
                aCommand5 = new OleDbCommand("INSERT INTO weekly(batch_code,day,period_no,teacher1,time1,teacher2,time2,teacher3,time3,teacher4,time4,teacher5,time5,teacher6,time6,teacher7,time7,teacher8,time8,teacher9,time9,teacher10,time10,teacher11,time11,teacher12,time12) VALUES ('" + code + "','" +sday+"','" + no_of_period + "','" + t1 + "','" + d1 + "','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"','"+bla+"')", main_connection);
                int check = aCommand5.ExecuteNonQuery();
                if (check == 1)
                {
                    MessageBox.Show("Data Saved");
                }
            }
            catch (OleDbException oldex)
            {
                Console.WriteLine("Error: {0}", oldex.Errors[0].Message);

            }

t1 and d1 both are string variable.

Can you say [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection)? Use [parameterized queries](http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html), not string concatenation. — Oded, May 16 '13 at 07:45
are you sure you are getting values for s1,t1,d1 and all other variables? None of them are null? Have you tried logging the insert statement in a messagebox or a file and verifying if the created statement is valid? — ViSu, May 16 '13 at 08:00
@VisuFor testing I fixed all the values like this in my query `"INSERT INTO weekly(batch_code,day,period_no,teacher1,time1,teacher2,time2,teacher3,time3,teacher4,time4,teacher5,time5,teacher6,time6,teacher7,time7,teacher8,time8,teacher9,time9,teacher10,time10,teacher11,time11,teacher12,time12) VALUES('code2','MON','no_of_period3','t2', 'dur2',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')"` then also I am getting Syntax error — user2241865, May 16 '13 at 08:10

Soner Gönül · Answer 1 · 2013-05-16T08:02:27.760

First of all, you should always use parameterized queries. This kind of codes are open for SQL Injection attacks.

Second of all, DAY is a reserved keyword for MS Access 2007. You should use it with square brackets like [day] ;

aCommand5 = new OleDbCommand("INSERT INTO weekly(batch_code, [day], period_no, teacher1, time1, teacher2, time2, teacher3, time3, teacher4, time4, teacher5, time5, teacher6, time6, teacher7, time7, teacher8, time8, teacher9, time9, teacher10, time10, teacher11, time11, teacher12, time12) 
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                              main_connection);

 aCommand5.Parameters.AddWithValue("@p1", code);
 aCommand5.Parameters.AddWithValue("@p2", sday);
 aCommand5.Parameters.AddWithValue("@p3", no_of_period);
 aCommand5.Parameters.AddWithValue("@p4", t1);
 aCommand5.Parameters.AddWithValue("@p5", d1);
 aCommand5.Parameters.AddWithValue("@p6", bla);
 aCommand5.Parameters.AddWithValue("@p7", bla);
 aCommand5.Parameters.AddWithValue("@p8", bla);
 aCommand5.Parameters.AddWithValue("@p9", bla);
 aCommand5.Parameters.AddWithValue("@p10", bla);
 aCommand5.Parameters.AddWithValue("@p11", bla);
 aCommand5.Parameters.AddWithValue("@p12", bla);
 aCommand5.Parameters.AddWithValue("@p13", bla);
 aCommand5.Parameters.AddWithValue("@p14", bla);
 aCommand5.Parameters.AddWithValue("@p15", bla);
 aCommand5.Parameters.AddWithValue("@p16", bla);
 aCommand5.Parameters.AddWithValue("@p17", bla);
 aCommand5.Parameters.AddWithValue("@p18", bla);
 aCommand5.Parameters.AddWithValue("@p19", bla);
 aCommand5.Parameters.AddWithValue("@p20", bla);
 aCommand5.Parameters.AddWithValue("@p21", bla);
 aCommand5.Parameters.AddWithValue("@p22", bla);
 aCommand5.Parameters.AddWithValue("@p23", bla);
 aCommand5.Parameters.AddWithValue("@p24", bla);
 aCommand5.Parameters.AddWithValue("@p25", bla);
 aCommand5.Parameters.AddWithValue("@p26", bla);
 aCommand5.Parameters.AddWithValue("@p27", bla);

 aCommand5.ExecuteNonQuery();

score 0 · Answer 2 · edited May 23 '17 at 10:25

The source of the Syntax error is the word DAY. It is a reserved keyword in MS-Access 2007 and thus, you need to encapsulate it with square brackets

 aCommand5 = new OleDbCommand("INSERT INTO weekly(batch_code,[day],.....")

However let me say that this is the worst case of string concatenation that I ever seen. Do not use string concatenation to build sql queries, use ALWAYS parametrized queries

This is an example of your sql statement build with a parametrized query

 aCommand5 = new OleDbCommand("INSERT INTO weekly (batch_code,day,period_no,teacher1,time1," +
                              "teacher2,time2,teacher3,time3,teacher4,time4, " + 
                              "teacher5,time5,teacher6,time6,teacher7,time7,teacher8,time8,"+
                              "teacher9,time9,teacher10,time10,teacher11,time11,teacher12,time12)"+
                              "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?",
                              main_connection);

 aCommand5.Parameters.AddWithValue("@p1", code);
 aCommand5.Parameters.AddWithValue("@p2", sday);
 .... and so on for the other 25 parameters
 .....
 aCommand5.ExecuteNonQuery();

In this way, you leave the work of correctly parsing your values to the framework code avoiding syntax error on single quotes, decimal point, date formatting etc.. but you avoid also the Sql Injection problem

Pay attention to the correct database type of this field. If you have a field numeric or datetime remember to call the appropriate Convert.ToXXXX on the value that you pass to the database using AddWithValue

Insert query complaining about syntax?

2 Answers2