Home > Archive > MS Access and Internet > July 2005 > SQL syntax error in INSERT INTO statement









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author SQL syntax error in INSERT INTO statement
Nathan Sokalski

2005-07-01, 3:25 am

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. ExecuteCommandTextEr
rorHandling(Int32 hr)
+41
System.Data.OleDb.OleDbCommand. ExecuteCommandTextFo
rSingleResult(tagDBP
ARAMS
dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand. ExecuteCommandText(O
bject& executeResult)
+92
System.Data.OleDb.OleDbCommand. ExecuteCommand(Comma
ndBehavior behavior,
Object& executeResult) +65
System.Data.OleDb.OleDbCommand. ExecuteReaderInterna
l(CommandBehavior
behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
PAPresenters.register. btnSubmit_Click(Obje
ct sender, EventArgs e) in
c:\inetpub\wwwroot\P
APresenters\register
.aspx.vb:220
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI. IPostBackEventHandle
r. RaisePostBackEvent(S
tring
eventArgument) +57
System.Web.UI.Page. RaisePostBackEvent(I
PostBackEventHandler

sourceControl, String eventArgument) +18
System.Web.UI.Page. RaisePostBackEvent(N
ameValueCollection 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,addres
s1,city,state,zip,co
unty,cell,email,pass
word,membertype)
VALUES('Nate''s House','1234 My
House','MyCity','CA'
,'12345','NONE','123
-456- 7890','njsokalski@ho
tmail. com','mypassword','a
rtistagent')"


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.
--
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/


GPO

2005-07-01, 3:25 am

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" wrote:

> 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. ExecuteCommandTextEr
rorHandling(Int32 hr)
> +41
> System.Data.OleDb.OleDbCommand. ExecuteCommandTextFo
rSingleResult(tagDBP
ARAMS
> dbParams, Object& executeResult) +174
> System.Data.OleDb.OleDbCommand. ExecuteCommandText(O
bject& executeResult)
> +92
> System.Data.OleDb.OleDbCommand. ExecuteCommand(Comma
ndBehavior behavior,
> Object& executeResult) +65
> System.Data.OleDb.OleDbCommand. ExecuteReaderInterna
l(CommandBehavior
> behavior, String method) +112
> System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
> PAPresenters.register. btnSubmit_Click(Obje
ct sender, EventArgs e) in
> c:\inetpub\wwwroot\P
APresenters\register
.aspx.vb:220
> System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
> System.Web.UI.WebControls.Button.System.Web.UI. IPostBackEventHandle
r. RaisePostBackEvent(S
tring
> eventArgument) +57
> System.Web.UI.Page. RaisePostBackEvent(I
PostBackEventHandler

> sourceControl, String eventArgument) +18
> System.Web.UI.Page. RaisePostBackEvent(N
ameValueCollection 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:
>
>
> "INSERT INTO members
> (organization,addres
s1,city,state,zip,co
unty,cell,email,pass
word,membertype)
> VALUES('Nate''s House','1234 My
> House','MyCity','CA'
,'12345','NONE','123
-456- 7890','njsokalski@ho
tmail. com','mypassword','a
rtistagent')"
>
>
> 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.
> --
> Nathan Sokalski
> njsokalski@hotmail.com
> http://www.nathansokalski.com/
>
>
>
Nathan Sokalski

2005-07-01, 3:25 am

I did try executing it without the implied apostrophe, it does not make any
difference.
--
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/

"GPO" <GPO@discussions.microsoft.com> wrote in message
news:CA1C4022-FC2C-4FB8-A137- 677CE95A5F99@microso
ft.com...[color=darkred]
> 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" wrote:
>


srini

2005-07-01, 3:25 am

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" wrote:

> 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. ExecuteCommandTextEr
rorHandling(Int32 hr)
> +41
> System.Data.OleDb.OleDbCommand. ExecuteCommandTextFo
rSingleResult(tagDBP
ARAMS
> dbParams, Object& executeResult) +174
> System.Data.OleDb.OleDbCommand. ExecuteCommandText(O
bject& executeResult)
> +92
> System.Data.OleDb.OleDbCommand. ExecuteCommand(Comma
ndBehavior behavior,
> Object& executeResult) +65
> System.Data.OleDb.OleDbCommand. ExecuteReaderInterna
l(CommandBehavior
> behavior, String method) +112
> System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
> PAPresenters.register. btnSubmit_Click(Obje
ct sender, EventArgs e) in
> c:\inetpub\wwwroot\P
APresenters\register
.aspx.vb:220
> System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
> System.Web.UI.WebControls.Button.System.Web.UI. IPostBackEventHandle
r. RaisePostBackEvent(S
tring
> eventArgument) +57
> System.Web.UI.Page. RaisePostBackEvent(I
PostBackEventHandler

> sourceControl, String eventArgument) +18
> System.Web.UI.Page. RaisePostBackEvent(N
ameValueCollection 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:
>
>
> "INSERT INTO members
> (organization,addres
s1,city,state,zip,co
unty,cell,email,pass
word,membertype)
> VALUES('Nate''s House','1234 My
> House','MyCity','CA'
,'12345','NONE','123
-456- 7890','njsokalski@ho
tmail. com','mypassword','a
rtistagent')"
>
>
> 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.
> --
> Nathan Sokalski
> njsokalski@hotmail.com
> http://www.nathansokalski.com/
>
>
>
Paul Clement

2005-07-01, 9:24 am

On Thu, 30 Jun 2005 23:48:02 -0700, "srini" <srini@discussions.microsoft.com> wrote:

¤ 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/defaul...kb;en-us;321266

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/defaul...kb;en-us;286335

ODBC Reserved Keywords
http://msdn.microsoft.com/library/d...> _keywords.asp


Paul
~~~~
Microsoft MVP (Visual Basic)
William \(Bill\) Vaughn

2005-07-01, 11:24 am

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" <srini@discussions.microsoft.com> wrote in message
news:E11916BF-8AE4-4CE2-BEEE- 0D22B43FDE30@microso
ft.com...[color=darkred]
> 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" wrote:
>


Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2008 droptable.com