SQL syntax error in INSERT INTO statement

[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

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

 

[Guest]

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

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

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

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

 

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

 

[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.
__________________________________

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

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

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

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

 

[diverge]

It worked for me

I had the same problem where i have used :
Insert into table (password,...)
you must use :
Insert into table ([password],...) to have an valid sql expression

tnx

 

[ahmed_samir]

I have a syntax error in (insert into) statement

my statment is like that

d = "insert into cv1(name,birth,country,city,address,tel,school,spch,quali,email,pers,user)values('" + nme.Text + "','" + birth.Text + "','" + country.Text + "','" + city.Text + "','" + address.Text + "','" + tel.Text + "','" + school.Text + "','" + spech.Text + "','" + quali.Text + "','" + email.Text + "','" + pers.Text + "','" + user.Text + "')"

and my proplem is :
when i start debuging an error happen which is syntax error INSERT INTO statement and the program highlight the cmd.excutenonquery() statment
so if any one can help me in this problem



Ahmed Samir