SQL syntax error when trying to insert new record into Access database

Question

I have a simple asp.net form for an individual to fill out, and the code I used on this particular page works in several other locations, but this page is giving me an issue.

It's saying there is a syntax error in the INSERT statement. Can you see anything wrong with it?

The click event code:

cmdInsert.CommandText = "INSERT INTO Position (Com_ID, Stu_ID, Pos_StartDate, Pos_Type, Pos_Description, Pos_Title) VALUES (' " & ddlCompany.SelectedValue & " ', ' " & ddlStudent.SelectedValue & " ', #" & CalStartDate.SelectedDate.Date & "#, ' " & ddlPositionType.SelectedValue & " ', ' " & txtDescription.Text & " ', ' " & txtPositionTitle.Text & " ');"

'MsgBox(cmdInsert.CommandText)
cmdInsert.CommandType = CommandType.Text
cmdInsert.Connection = cnnOLEDB
cmdInsert.ExecuteNonQuery()

txtPositionTitle.Text = ""
txtDescription.Text = "Record inserted."
CalStartDate.SelectedDates.Clear()
cmdInsert.Dispose()

The data captured from the form lines up with the data type in the database. Any ideas?

Here's the stack trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1081356
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +247
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +167
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
   Tiger_Recruiting.WebForm3.btnSaveStudentRecord_Click(Object sender, EventArgs e) in C:\Users\Garrett\Downloads\Tiger Recruiting(3)\Tiger Recruiting(3)\Tiger Recruiting(3)\Tiger Recruiting\Tiger Recruiting\position.aspx.vb:28
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +141
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +149
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +39
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +37
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +87
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4225

I don't see anything wrong with the query. However, it will be better to define a string variable that holds the query instead of directly assigning it to `CommandText`. That will help you figure what is wrong with the syntax. — shahkalpesh, May 02 '13 at 18:53
I would point the blame at Pos_StartDate, access requires the date in a specific format. - can you post an example of the resultant INSERT statement from the concatenation? — Chris, May 02 '13 at 18:58
@shahkalpesh, That's probably a good idea. Do you mean define it like "@StartDate" or something like that? I've seen people declare every field instead of just using CommandText. — Sev09, May 02 '13 at 19:07
@ChrisBain - The weird thing is, it seems to work just fine on other forms that use the same datetime picker. — Sev09, May 02 '13 at 19:11
@user2344314: Here - `dim query as String query = "INSERT INTO Position (Com_ID, Stu_ID, Pos_StartDate, Pos_Type, Pos_Description, Pos_Title) VALUES (' " & ddlCompany.SelectedValue & " ', ' " & ddlStudent.SelectedValue & " ', #" & CalStartDate.SelectedDate.Date & "#, ' " & ddlPositionType.SelectedValue & " ', ' " & txtDescription.Text & " ', ' " & txtPositionTitle.Text & " ');" msgbox(query)` This will help you see the query formed and figure the syntax error. — shahkalpesh, May 02 '13 at 19:14
@shahkalpesh, I like that method. Here's what the messagebox produced: "INSERT INTO Position (Com_ID, Stu_ID, Pos_StartDate, Pos_Type, Pos_Description, Pos_Title) VALUES ('1', '1', #5/1/2013#, 'FullTime', 'Programs things', 'Developer');" So... it looks like it's working ok? — Sev09, May 02 '13 at 19:26

score 2 · Accepted Answer · edited May 23 '17 at 11:46

The word POSITION is a reserved keyword in MS-Access-Jet.
You use it for a table name thus you need to encapsulate it with square brackets

cmdInsert.CommandText = "INSERT INTO [Position] .......

A part from this, your code is very bad. 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 Look here for a funny explanation

So your code should be written in this way:

cmdInsert.CommandText = "INSERT INTO [Position] (Com_ID, Stu_ID, Pos_StartDate, Pos_Type, " + 
                        "Pos_Description, Pos_Title) VALUES " + 
                        "(?,?,?,?,?,?)"
cmdInsert.Parameters.AddWithValue("@p1",ddlCompany.SelectedValue)
cmdInsert.Parameters.AddWithValue("@p2",ddlStudent.SelectedValue)
cmdInsert.Parameters.AddWithValue("@p3",CalStartDate.SelectedDate.Date)
cmdInsert.Parameters.AddWithValue("@p4",ddlPositionType.SelectedValue)
cmdInsert.Parameters.AddWithValue("@p5",txtDescription.Text)
cmdInsert.Parameters.AddWithValue("@p6",txtPositionTitle.Text)
cmdInsert.Connection = cnnOLEDB
cmdInsert.ExecuteNonQuery()

That was it! I can't believe it was that simple. Thanks for your help, I really appreciate it. I've always used stackoverflow for issues I've had, but this was the first question I asked. Not disappointed. I also used the parameters as you suggested. Thanks again. — Sev09, May 02 '13 at 19:56

SQL syntax error when trying to insert new record into Access database

1 Answers1

Linked