Syntax error in INSERT INTO

Question

I have first time created a table in Access it's name is punchMachineData it has following columns with their respective types given on the link http://prntscr.com/bjxs2v

i create a dynamic insert query like this :

  string str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Sara\Desktop\punchMachineDataBase.accdb";
                    OleDbConnection conn = new OleDbConnection(str);
                    try
                    {
                        conn.Open();       
                        String my_querry = "INSERT into punchMachineData (empID,date,time,bstatus) Values('" + vSEnrollNumber + "','" + Convert.ToString(vYear) + "/" + String.Format("{0:D2}", vMonth) + "/" + String.Format("{0:D2}", vDay) + "','" + String.Format("{0:D2}", vHour) + ":" + String.Format("{0:D2}", vMinute) + "','" + bstatus + "')";

                        OleDbCommand cmd = new OleDbCommand(my_querry, conn);
                        cmd.ExecuteNonQuery();
                        Console.WriteLine("saved");                      
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Failed due to" + ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }

on debugging my_querry i get this : INSERT into punchMachineData (empID,date,time,bstatus) Values('1','2016/06/22','18:19','1')

and error i get in exception is : Syntax error in INSERT INTO statement

System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Why i have it and How to fix it ?

Embrace your position like `into [punchMachineData]`... [Source](http://stackoverflow.com/questions/16345530/sql-syntax-error-when-trying-to-insert-new-record-into-access-database) — D. Petrov, Jun 23 '16 at 09:54
Be careful with [little Bobby Tables](http://bobby-tables.com/). — Sergey Kalinichenko, Jun 23 '16 at 09:54

Yogi · Accepted Answer · 2016-06-23T10:06:45.170

1

The column names are different than the ones shared in the screenshot.

Use -

INSERT into punchMachineData (empID,dates,times,bstatus)...

Instead of -

INSERT into punchMachineData (empID,date,time,bstatus)...

Or change the name in schema instead.

On a side note, such commands are prone to SqlInjection so suggest to use parameterized queries in place of plain sql statements.

edited Jun 23 '16 at 10:06

answered Jun 23 '16 at 09:59

Yogi

9,174
2
46
61

parameterized queries means ? – test test Jun 23 '16 at 10:25
@testtest - Sharing a nice link for understanding parameters - http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database – Yogi Jun 23 '16 at 10:31

score 0 · Answer 2 · answered Jun 23 '16 at 10:50

you need to encapsulate the table name and column name with square brackets

"INSERT into [punchMachineData] ([empID],[date],[time],[bstatus])......

A part from this, do not use string cancatenation to build sql commands. This practice leads to syntax error when in your input there is a single quote or do you have other fields that require a particular formatting of the input value. But the worst of all is the problem of Sql Injection

So your code should be written in this way:

INSERT into punchMachineData (empID,date,time,bstatus) Values
cmdInsert.CommandText = "INSERT INTO [punchMachineData ] (empID, date, time, bstatus) VALUES " + "(?,?,?,?)"
cmdInsert.Parameters.AddWithValue("@p1",pass param1 here)
cmdInsert.Parameters.AddWithValue("@p2",pass param2 here)
cmdInsert.Parameters.AddWithValue("@p3",pass param3 here)
cmdInsert.Parameters.AddWithValue("@p4",pass param4 here)
cmdInsert.Connection = cnnOLEDB
cmdInsert.ExecuteNonQuery()

Syntax error in INSERT INTO

2 Answers2