Syntax error in INSERT INTO

▲
0
▼

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 ?

c#
.net
ms-access
oledb
oledbconnection
asked on Stack Overflow Jun 23, 2016 by test test

2 Answers

▲
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.

answered on Stack Overflow Jun 23, 2016 by Yogi • edited Jun 23, 2016 by Yogi
▲
0
▼

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()
answered on Stack Overflow Jun 23, 2016 by Nikhil Pradeep

User contributions licensed under CC BY-SA 3.0