SQL syntax error in INSERT INTO statement

  • Thread starter Nathan Sokalski
  • Start date
N

Nathan Sokalski

When trying to submit data to an Access database using ASP.NET I recieve the
following error:

[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
PAPresenters.register.btnSubmit_Click(Object sender, EventArgs e) in
c:\inetpub\wwwroot\PAPresenters\register.aspx.vb:220
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

The SQL in the CommandText property immediately before the the
ExecuteNonQuery method, which I got using Visual Studio's debugger, are:
Debug.Print cmdSubmitToDB.CommandText
Click to expand...

"INSERT INTO members
(organization,address1,city,state,zip,county,cell,email,password,membertype)
VALUES('Nate''s House','1234 My
House','MyCity','CA','12345','NONE','123-456-7890','(e-mail address removed)','mypassword','artistagent')"


What is wrong with the syntax in this statement? Is there any way I can have
be told where in the statement the error is? Thanks.
 
G

Guest

Have you tried executing it without the apostrophe implied in the string
'Nate''s House'?

I successfully executed the SQL in the Access 2000 QBE sql pane so the
problem does not seem to be with your syntax as far as Access is concerned.
It could be though, that in parsing the string in ASP.net, something is going
awry; something that an ASP.net newsgroup could advise you on.

Nathan Sokalski said:
When trying to submit data to an Access database using ASP.NET I recieve the
following error:

[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
PAPresenters.register.btnSubmit_Click(Object sender, EventArgs e) in
c:\inetpub\wwwroot\PAPresenters\register.aspx.vb:220
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

The SQL in the CommandText property immediately before the the
ExecuteNonQuery method, which I got using Visual Studio's debugger, are:
Debug.Print cmdSubmitToDB.CommandText
Click to expand...

"INSERT INTO members
(organization,address1,city,state,zip,county,cell,email,password,membertype)
VALUES('Nate''s House','1234 My
House','MyCity','CA','12345','NONE','123-456-7890','(e-mail address removed)','mypassword','artistagent')"


What is wrong with the syntax in this statement? Is there any way I can have
be told where in the statement the error is? Thanks.
Click to expand...
 
N

Nathan Sokalski

I did try executing it without the implied apostrophe, it does not make any
difference.
--
Nathan Sokalski
(e-mail address removed)
http://www.nathansokalski.com/

GPO said:
Have you tried executing it without the apostrophe implied in the string
'Nate''s House'?

I successfully executed the SQL in the Access 2000 QBE sql pane so the
problem does not seem to be with your syntax as far as Access is
concerned.
It could be though, that in parsing the string in ASP.net, something is
going
awry; something that an ASP.net newsgroup could advise you on.

Nathan Sokalski said:
When trying to submit data to an Access database using ASP.NET I recieve
the
following error:

[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
PAPresenters.register.btnSubmit_Click(Object sender, EventArgs e) in
c:\inetpub\wwwroot\PAPresenters\register.aspx.vb:220
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1292

The SQL in the CommandText property immediately before the the
ExecuteNonQuery method, which I got using Visual Studio's debugger, are:
Debug.Print cmdSubmitToDB.CommandText
Click to expand...

"INSERT INTO members
(organization,address1,city,state,zip,county,cell,email,password,membertype)
VALUES('Nate''s House','1234 My
House','MyCity','CA','12345','NONE','123-456-7890','(e-mail address removed)','mypassword','artistagent')"


What is wrong with the syntax in this statement? Is there any way I can
have
be told where in the statement the error is? Thanks.
Click to expand...
Click to expand...
 
G

Guest

hi,
use [password] instead of password in the insert statement since password is
a reserved keyword.

--
The best
srini
http://www.expertszone.com


Nathan Sokalski said:
When trying to submit data to an Access database using ASP.NET I recieve the
following error:

[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
PAPresenters.register.btnSubmit_Click(Object sender, EventArgs e) in
c:\inetpub\wwwroot\PAPresenters\register.aspx.vb:220
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

The SQL in the CommandText property immediately before the the
ExecuteNonQuery method, which I got using Visual Studio's debugger, are:
Debug.Print cmdSubmitToDB.CommandText
Click to expand...

"INSERT INTO members
(organization,address1,city,state,zip,county,cell,email,password,membertype)
VALUES('Nate''s House','1234 My
House','MyCity','CA','12345','NONE','123-456-7890','(e-mail address removed)','mypassword','artistagent')"


What is wrong with the syntax in this statement? Is there any way I can have
be told where in the statement the error is? Thanks.
Click to expand...
 
P

Paul Clement

¤ hi,
¤ use [password] instead of password in the insert statement since password is
¤ a reserved keyword.

Sure is. You can always check the links below should you have any doubts:

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

ODBC Reserved Keywords
http://msdn.microsoft.com/library/d...us/odbc/htm/odbclist_of_reserved_keywords.asp


Paul
~~~~
Microsoft MVP (Visual Basic)
 
W

William \(Bill\) Vaughn

Right and better yet setup a Parameter-driven Command instead of using ad
hoc queries--unless you don't care about SQL injection and a number of other
issues.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

srini said:
hi,
use [password] instead of password in the insert statement since password
is
a reserved keyword.

--
The best
srini
http://www.expertszone.com


Nathan Sokalski said:
When trying to submit data to an Access database using ASP.NET I recieve
the
following error:

[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
PAPresenters.register.btnSubmit_Click(Object sender, EventArgs e) in
c:\inetpub\wwwroot\PAPresenters\register.aspx.vb:220
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1292

The SQL in the CommandText property immediately before the the
ExecuteNonQuery method, which I got using Visual Studio's debugger, are:
Debug.Print cmdSubmitToDB.CommandText
Click to expand...

"INSERT INTO members
(organization,address1,city,state,zip,county,cell,email,password,membertype)
VALUES('Nate''s House','1234 My
House','MyCity','CA','12345','NONE','123-456-7890','(e-mail address removed)','mypassword','artistagent')"


What is wrong with the syntax in this statement? Is there any way I can
have
be told where in the statement the error is? Thanks.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OleDbException: Syntax error in UPDATE statement 2
Help: SQL syntax error??? 2
Help: OleDbException: Operation must use an updateable query 1
Access Insert Error 2
not able to fill dataset with OleDbDataAdapter 1
System.Data.Common.UnsafeNativeMethods.SetErrorInfo 1
ADO .NET and Excel ... 8
Can't insert in my table 2

Top