Access Insert Error

  • Thread starter Thread starter J. Clay
  • Start date Start date
J

J. Clay

I am trying to run the following insert into an Access DB. For some reason
I continually get a Syntax error in INSERT INTO statement. I have been
looking at it way too long. There has got to be something relatively
obvious that I am missing. Any suggestions would be greatly appreciated.


conDb = New OleDbConnection( strConString )
'Set up insert statement for order header
strSql = "Insert Orders (UserID, FirstName, LastName, billStreet, billCity,
billState, billZip, " & _
"shipStreet, shipCity, shipState, shipZip) VALUES (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?)"
cmdSql = New OleDbCommand( strSql, conDb )
cmdSql.Parameters.Add( "@userID", OleDbType.Guid).Value = guidUserID
cmdSql.Parameters.Add( "@firstname", OleDbType.VarWChar).Value =
txtfirstname.Text
cmdSql.Parameters.Add( "@lastname", OleDbType.VarWChar).Value =
txtlastname.Text
cmdSql.Parameters.Add( "@billStreet", OleDbType.VarWChar).Value =
ctrlBillingAddress.Street
cmdSql.Parameters.Add( "@billCity", OleDbType.VarWChar).Value =
ctrlBillingAddress.City
cmdSql.Parameters.Add( "@billState", OleDbType.VarWChar).Value =
ctrlBillingAddress.State
cmdSql.Parameters.Add( "@billZip", OleDbType.VarWChar).Value =
ctrlBillingAddress.Zip
cmdSql.Parameters.Add( "@shipStreet", OleDbType.VarWChar).Value =
ctrlShippingAddress.Street
cmdSql.Parameters.Add( "@shipCity", OleDbType.VarWChar).Value =
ctrlShippingAddress.City
cmdSql.Parameters.Add( "@shipState", OleDbType.VarWChar).Value =
ctrlShippingAddress.State
cmdSql.Parameters.Add( "@shipZip", OleDbType.VarWChar).Value =
ctrlShippingAddress.Zip
conDb.Open()
cmdSql.ExecuteNonQuery()


The stack trace is as follows:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
+41

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
+92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66


Thanks,
J. Clay
 
Hi,

I think the beginning is wrong:
strSql = "Insert Orders...
it should be
strSql = "Insert Into Orders..."

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

J. Clay said:
I am trying to run the following insert into an Access DB. For some reason
I continually get a Syntax error in INSERT INTO statement. I have been
looking at it way too long. There has got to be something relatively
obvious that I am missing. Any suggestions would be greatly appreciated.


conDb = New OleDbConnection( strConString )
'Set up insert statement for order header
strSql = "Insert Orders (UserID, FirstName, LastName, billStreet, billCity,
billState, billZip, " & _
"shipStreet, shipCity, shipState, shipZip) VALUES (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?)"
cmdSql = New OleDbCommand( strSql, conDb )
cmdSql.Parameters.Add( "@userID", OleDbType.Guid).Value = guidUserID
cmdSql.Parameters.Add( "@firstname", OleDbType.VarWChar).Value =
txtfirstname.Text
cmdSql.Parameters.Add( "@lastname", OleDbType.VarWChar).Value =
txtlastname.Text
cmdSql.Parameters.Add( "@billStreet", OleDbType.VarWChar).Value =
ctrlBillingAddress.Street
cmdSql.Parameters.Add( "@billCity", OleDbType.VarWChar).Value =
ctrlBillingAddress.City
cmdSql.Parameters.Add( "@billState", OleDbType.VarWChar).Value =
ctrlBillingAddress.State
cmdSql.Parameters.Add( "@billZip", OleDbType.VarWChar).Value =
ctrlBillingAddress.Zip
cmdSql.Parameters.Add( "@shipStreet", OleDbType.VarWChar).Value =
ctrlShippingAddress.Street
cmdSql.Parameters.Add( "@shipCity", OleDbType.VarWChar).Value =
ctrlShippingAddress.City
cmdSql.Parameters.Add( "@shipState", OleDbType.VarWChar).Value =
ctrlShippingAddress.State
cmdSql.Parameters.Add( "@shipZip", OleDbType.VarWChar).Value =
ctrlShippingAddress.Zip
conDb.Open()
cmdSql.ExecuteNonQuery()


The stack trace is as follows:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
+41
Click to expand...
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
+92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66


Thanks,
J. Clay
Click to expand...
 
Your right. I resolved it late last night. I've been programming in
reqular SQL for quite a while where the INTO is optional that I didn't think
about it until the very end. Also, I think that "UserID" may be a reserved
word. I put it in brackets to make sure of no problems and haven't had time
to look up reserved words. I will have to look up at verify later.

Thanks again,
Jim


Miha Markic said:
Hi,

I think the beginning is wrong:
strSql = "Insert Orders...
it should be
strSql = "Insert Into Orders..."

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

J. Clay said:
I am trying to run the following insert into an Access DB. For some reason
I continually get a Syntax error in INSERT INTO statement. I have been
looking at it way too long. There has got to be something relatively
obvious that I am missing. Any suggestions would be greatly appreciated.


conDb = New OleDbConnection( strConString )
'Set up insert statement for order header
strSql = "Insert Orders (UserID, FirstName, LastName, billStreet, billCity,
billState, billZip, " & _
"shipStreet, shipCity, shipState, shipZip) VALUES (?, ?, ?, ?,
Click to expand...
?,
?,
?, ?, ?, ?, ?)"
cmdSql = New OleDbCommand( strSql, conDb )
cmdSql.Parameters.Add( "@userID", OleDbType.Guid).Value = guidUserID
cmdSql.Parameters.Add( "@firstname", OleDbType.VarWChar).Value =
txtfirstname.Text
cmdSql.Parameters.Add( "@lastname", OleDbType.VarWChar).Value =
txtlastname.Text
cmdSql.Parameters.Add( "@billStreet", OleDbType.VarWChar).Value =
ctrlBillingAddress.Street
cmdSql.Parameters.Add( "@billCity", OleDbType.VarWChar).Value =
ctrlBillingAddress.City
cmdSql.Parameters.Add( "@billState", OleDbType.VarWChar).Value =
ctrlBillingAddress.State
cmdSql.Parameters.Add( "@billZip", OleDbType.VarWChar).Value =
ctrlBillingAddress.Zip
cmdSql.Parameters.Add( "@shipStreet", OleDbType.VarWChar).Value =
ctrlShippingAddress.Street
cmdSql.Parameters.Add( "@shipCity", OleDbType.VarWChar).Value =
ctrlShippingAddress.City
cmdSql.Parameters.Add( "@shipState", OleDbType.VarWChar).Value =
ctrlShippingAddress.State
cmdSql.Parameters.Add( "@shipZip", OleDbType.VarWChar).Value =
ctrlShippingAddress.Zip
conDb.Open()
cmdSql.ExecuteNonQuery()


The stack trace is as follows:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
+41
Click to expand...
Click to expand...
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
+92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66


Thanks,
J. Clay
Click to expand...
Click to expand...
 
Back
Top