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 — D. Petrov
– D. Petrov, Commented Jun 23, 2016 at 9:54

Yogi · Accepted Answer · 2016-06-23 10:06:45Z

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, 2016 at 10:06

answered Jun 23, 2016 at 9:59

Yogi

9,8693 gold badges49 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

test test Over a year ago

parameterized queries means ?

Yogi Over a year ago

@testtest - Sharing a nice link for understanding parameters - stackoverflow.com/questions/5893837/…

Nikhil Pradeep · Accepted Answer · 2016-06-23 10:50:00Z

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()

Collectives™ on Stack Overflow

Syntax error in INSERT INTO

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related