|
 |
access_asp thread: Weird SQL syntax error
Message #1 by r_mastrobiso@a... on Sat, 19 Oct 2002 19:51:26
|
|
Hi,
Can anybody please help me? I´m getting a "syntax error" message with the
following code; I´m flashing
the SQL statement before executing the insertion, but I can´t see anything
wrong with it.
The code I´m using is this:
2: <!-- #include file="adovbs.inc" -->
3:
4: <%
5: Dim cn
6:
7: Set cn = Server.CreateObject("ADODB.Connection")
8: cn.Mode = adModeReadWrite
9: cn.Open ("Provider = Microsoft.Jet.OLEDB.4.0; DATA SOURCE = " &
Server.MapPath("ds.mdb"))
10:
11: Dim dbinsert
12: Set nick = Request.Form("user_nick")
13: Set sex = Request.Form("user_sex")
14: Set os = Request.Form("user_os")
15: Set age = Request.Form("user_age")
16:
17: Set dbinsert = Server.CreateObject("ADODB.Recordset")
18: sql = "insert into user (user_nick, user_sex, user_os, user_age)
values ('" & nick & "', '" & sex & "', " & "'" & os & "', '" & age & "') "
19: response.write (sql)
20: dbinsert.Open "insert into user (user_nick, user_sex, user_os,
user_age) values ('" & nick & "', '" & sex & "', " & "'" & os & "', '" &
age & "') ", cn , adOpenStatic, adLockPessimistic
When I run the code(on a Windows 2k Server with ADO version 2.5), I get
the following (notice the SQL string printed before the error message):
insert into user (user_nick, user_sex, user_os, user_age) values
('Testing', 'male', 'winnt', 'age_grp1')
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/klazz/bdinsert.asp, line 20
Would anyone shed a light on this, please?
Thanks in advance,
M.
Message #2 by "Larry Woods" <larry@l...> on Sat, 19 Oct 2002 17:52:15 -0700
|
|
I guess the only thing that really hits me as "strange" is that
you are opening a recordset. The INSERT doesn't return a
recordset. I would use an ADO Command, or the Connnection
itself, as in:
cn.Execute sql
Larry Woods
> -----Original Message-----
> From: r_mastrobiso@a...
> [mailto:r_mastrobiso@a...]
> Sent: Saturday, October 19, 2002 7:51 PM
> To: Access ASP
> Subject: [access_asp] Weird SQL syntax error
>
>
> Hi,
>
> Can anybody please help me? I´m getting a "syntax
> error" message with the
> following code; I´m flashing
> the SQL statement before executing the insertion, but
> I can´t see anything
> wrong with it.
>
> The code I´m using is this:
>
> 2: <!-- #include file="adovbs.inc" -->
> 3:
> 4: <%
> 5: Dim cn
> 6:
> 7: Set cn = Server.CreateObject("ADODB.Connection")
> 8: cn.Mode = adModeReadWrite
> 9: cn.Open ("Provider = Microsoft.Jet.OLEDB.4.0; DATA
> SOURCE = " &
> Server.MapPath("ds.mdb"))
> 10:
> 11: Dim dbinsert
> 12: Set nick = Request.Form("user_nick")
> 13: Set sex = Request.Form("user_sex")
> 14: Set os = Request.Form("user_os")
> 15: Set age = Request.Form("user_age")
> 16:
> 17: Set dbinsert = Server.CreateObject("ADODB.Recordset")
> 18: sql = "insert into user (user_nick, user_sex,
> user_os, user_age)
> values ('" & nick & "', '" & sex & "', " & "'" & os &
> "', '" & age & "') "
> 19: response.write (sql)
> 20: dbinsert.Open "insert into user (user_nick,
> user_sex, user_os,
> user_age) values ('" & nick & "', '" & sex & "', " &
> "'" & os & "', '" &
> age & "') ", cn , adOpenStatic, adLockPessimistic
>
> When I run the code(on a Windows 2k Server with ADO
> version 2.5), I get
> the following (notice the SQL string printed before
> the error message):
>
> insert into user (user_nick, user_sex, user_os,
> user_age) values
> ('Testing', 'male', 'winnt', 'age_grp1')
>
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error in INSERT INTO statement.
>
> /klazz/bdinsert.asp, line 20
>
> Would anyone shed a light on this, please?
> Thanks in advance,
>
> M.
Message #3 by "R. Mastrobiso" <r_mastrobiso@a...> on Sun, 20 Oct 2002 21:29:00
|
|
Larry,
Thanks for replying. I commented the recordset lines and used the
line "cn.Execute sql", but it returns exactly the same error message,
believe it or not. Could it be something wrong with the database modeling
instead?
M.
> I guess the only thing that really hits me as "strange" is that
you are opening a recordset. The INSERT doesn't return a
recordset. I would use an ADO Command, or the Connnection
itself, as in:
cn.Execute sql
Larry Woods
> -----Original Message-----
> From: r_mastrobiso@a...
> [mailto:r_mastrobiso@a...]
> Sent: Saturday, October 19, 2002 7:51 PM
> To: Access ASP
> Subject: [access_asp] Weird SQL syntax error
>
>
> Hi,
>
> Can anybody please help me? I´m getting a "syntax
> error" message with the
> following code; I´m flashing
> the SQL statement before executing the insertion, but
> I can´t see anything
> wrong with it.
>
> The code I´m using is this:
>
> 2: <!-- #include file="adovbs.inc" -->
> 3:
> 4: <%
> 5: Dim cn
> 6:
> 7: Set cn = Server.CreateObject("ADODB.Connection")
> 8: cn.Mode = adModeReadWrite
> 9: cn.Open ("Provider = Microsoft.Jet.OLEDB.4.0; DATA
> SOURCE = " &
> Server.MapPath("ds.mdb"))
> 10:
> 11: Dim dbinsert
> 12: Set nick = Request.Form("user_nick")
> 13: Set sex = Request.Form("user_sex")
> 14: Set os = Request.Form("user_os")
> 15: Set age = Request.Form("user_age")
> 16:
> 17: Set dbinsert = Server.CreateObject("ADODB.Recordset")
> 18: sql = "insert into user (user_nick, user_sex,
> user_os, user_age)
> values ('" & nick & "', '" & sex & "', " & "'" & os &
> "', '" & age & "') "
> 19: response.write (sql)
> 20: dbinsert.Open "insert into user (user_nick,
> user_sex, user_os,
> user_age) values ('" & nick & "', '" & sex & "', " &
> "'" & os & "', '" &
> age & "') ", cn , adOpenStatic, adLockPessimistic
>
> When I run the code(on a Windows 2k Server with ADO
> version 2.5), I get
> the following (notice the SQL string printed before
> the error message):
>
> insert into user (user_nick, user_sex, user_os,
> user_age) values
> ('Testing', 'male', 'winnt', 'age_grp1')
>
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error in INSERT INTO statement.
>
> /klazz/bdinsert.asp, line 20
>
> Would anyone shed a light on this, please?
> Thanks in advance,
>
> M.
Message #4 by "Larry Woods" <larry@l...> on Sun, 20 Oct 2002 16:03:58 -0700
|
|
The next thing that I would do is verify that each of the fields
are text. Also check to see if there is each verificaiton rule
put on any of the fields.
Larry
> -----Original Message-----
> From: R. Mastrobiso [mailto:r_mastrobiso@a...]
> Sent: Sunday, October 20, 2002 9:29 PM
> To: Access ASP
> Subject: [access_asp] RE: Weird SQL syntax error
>
>
>
> Larry,
>
> Thanks for replying. I commented the recordset lines
> and used the
> line "cn.Execute sql", but it returns exactly the same
> error message,
> believe it or not. Could it be something wrong with
> the database modeling
> instead?
>
> M.
>
> > I guess the only thing that really hits me as
> "strange" is that
> you are opening a recordset. The INSERT doesn't return a
> recordset. I would use an ADO Command, or the Connnection
> itself, as in:
>
> cn.Execute sql
>
> Larry Woods
>
>
> > -----Original Message-----
> > From: r_mastrobiso@a...
> > [mailto:r_mastrobiso@a...]
> > Sent: Saturday, October 19, 2002 7:51 PM
> > To: Access ASP
> > Subject: [access_asp] Weird SQL syntax error
> >
> >
> > Hi,
> >
> > Can anybody please help me? I´m getting a "syntax
> > error" message with the
> > following code; I´m flashing
> > the SQL statement before executing the insertion, but
> > I can´t see anything
> > wrong with it.
> >
> > The code I´m using is this:
> >
> > 2: <!-- #include file="adovbs.inc" -->
> > 3:
> > 4: <%
> > 5: Dim cn
> > 6:
> > 7: Set cn = Server.CreateObject("ADODB.Connection")
> > 8: cn.Mode = adModeReadWrite
> > 9: cn.Open ("Provider = Microsoft.Jet.OLEDB.4.0; DATA
> > SOURCE = " &
> > Server.MapPath("ds.mdb"))
> > 10:
> > 11: Dim dbinsert
> > 12: Set nick = Request.Form("user_nick")
> > 13: Set sex = Request.Form("user_sex")
> > 14: Set os = Request.Form("user_os")
> > 15: Set age = Request.Form("user_age")
> > 16:
> > 17: Set dbinsert = Server.CreateObject("ADODB.Recordset")
> > 18: sql = "insert into user (user_nick, user_sex,
> > user_os, user_age)
> > values ('" & nick & "', '" & sex & "', " & "'" & os &
> > "', '" & age & "') "
> > 19: response.write (sql)
> > 20: dbinsert.Open "insert into user (user_nick,
> > user_sex, user_os,
> > user_age) values ('" & nick & "', '" & sex & "', " &
> > "'" & os & "', '" &
> > age & "') ", cn , adOpenStatic, adLockPessimistic
> >
> > When I run the code(on a Windows 2k Server with ADO
> > version 2.5), I get
> > the following (notice the SQL string printed before
> > the error message):
> >
> > insert into user (user_nick, user_sex, user_os,
> > user_age) values
> > ('Testing', 'male', 'winnt', 'age_grp1')
> >
> > Microsoft JET Database Engine error '80040e14'
> >
> > Syntax error in INSERT INTO statement.
> >
> > /klazz/bdinsert.asp, line 20
> >
> > Would anyone shed a light on this, please?
> > Thanks in advance,
> >
> > M.
Message #5 by "Ken Schaefer" <ken@a...> on Mon, 21 Oct 2002 11:32:27 +1000
|
|
I would suggest that "User" is a reserved word. Don't use reserved words for
field/table names.
Also, as Larry as noted, you don't need a recordset to insert data.
Recordsets are for holding data retrieved from the database. And even when
you do retrieve data, always use the lightest lock/cursor type you can, eg
adOpenForwardOnly/adLockReadOnly.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <r_mastrobiso@a...>
Subject: [access_asp] Weird SQL syntax error
: Can anybody please help me? I´m getting a "syntax error" message with the
: following code; I´m flashing
: the SQL statement before executing the insertion, but I can´t see anything
: wrong with it.
:
: The code I´m using is this:
:
: 2: <!-- #include file="adovbs.inc" -->
: 3:
: 4: <%
: 5: Dim cn
: 6:
: 7: Set cn = Server.CreateObject("ADODB.Connection")
: 8: cn.Mode = adModeReadWrite
: 9: cn.Open ("Provider = Microsoft.Jet.OLEDB.4.0; DATA SOURCE = " &
: Server.MapPath("ds.mdb"))
: 10:
: 11: Dim dbinsert
: 12: Set nick = Request.Form("user_nick")
: 13: Set sex = Request.Form("user_sex")
: 14: Set os = Request.Form("user_os")
: 15: Set age = Request.Form("user_age")
: 16:
: 17: Set dbinsert = Server.CreateObject("ADODB.Recordset")
: 18: sql = "insert into user (user_nick, user_sex, user_os, user_age)
: values ('" & nick & "', '" & sex & "', " & "'" & os & "', '" & age & "') "
: 19: response.write (sql)
: 20: dbinsert.Open "insert into user (user_nick, user_sex, user_os,
: user_age) values ('" & nick & "', '" & sex & "', " & "'" & os & "', '" &
: age & "') ", cn , adOpenStatic, adLockPessimistic
:
: When I run the code(on a Windows 2k Server with ADO version 2.5), I get
: the following (notice the SQL string printed before the error message):
:
: insert into user (user_nick, user_sex, user_os, user_age) values
: ('Testing', 'male', 'winnt', 'age_grp1')
:
: Microsoft JET Database Engine error '80040e14'
:
: Syntax error in INSERT INTO statement.
:
: /klazz/bdinsert.asp, line 20
:
: Would anyone shed a light on this, please?
: Thanks in advance,
:
: M.
Message #6 by "R. Mastrobiso" <r_mastrobiso@a...> on Tue, 22 Oct 2002 02:55:20
|
|
Larry, Ken, Alfred;
Thanks a lot for your advice. The code is running with perfection now. Ken
nailed it right: "user" is a reserved word for Access and the Jet engine.
I am also using the connection, instead of the recordset object too.
For future reference to people who might come to a similar problem and
consult the site´s archives, the link to a list of Access/Jet reserved
words is this:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q209187&LN=EN-US
Thanks again,
M.
> I would suggest that "User" is a reserved word. Don't use reserved words
for
field/table names.
Also, as Larry as noted, you don't need a recordset to insert data.
Recordsets are for holding data retrieved from the database. And even when
you do retrieve data, always use the lightest lock/cursor type you can, eg
adOpenForwardOnly/adLockReadOnly.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <r_mastrobiso@a...>
Subject: [access_asp] Weird SQL syntax error
: Can anybody please help me? I´m getting a "syntax error" message with the
: following code; I´m flashing
: the SQL statement before executing the insertion, but I can´t see
anything
: wrong with it.
:
: The code I´m using is this:
:
: 2: <!-- #include file="adovbs.inc" -->
: 3:
: 4: <%
: 5: Dim cn
: 6:
: 7: Set cn = Server.CreateObject("ADODB.Connection")
: 8: cn.Mode = adModeReadWrite
: 9: cn.Open ("Provider = Microsoft.Jet.OLEDB.4.0; DATA SOURCE = " &
: Server.MapPath("ds.mdb"))
: 10:
: 11: Dim dbinsert
: 12: Set nick = Request.Form("user_nick")
: 13: Set sex = Request.Form("user_sex")
: 14: Set os = Request.Form("user_os")
: 15: Set age = Request.Form("user_age")
: 16:
: 17: Set dbinsert = Server.CreateObject("ADODB.Recordset")
: 18: sql = "insert into user (user_nick, user_sex, user_os, user_age)
: values ('" & nick & "', '" & sex & "', " & "'" & os & "', '" & age
& "') "
: 19: response.write (sql)
: 20: dbinsert.Open "insert into user (user_nick, user_sex, user_os,
: user_age) values ('" & nick & "', '" & sex & "', " & "'" & os & "', '" &
: age & "') ", cn , adOpenStatic, adLockPessimistic
:
: When I run the code(on a Windows 2k Server with ADO version 2.5), I get
: the following (notice the SQL string printed before the error message):
:
: insert into user (user_nick, user_sex, user_os, user_age) values
: ('Testing', 'male', 'winnt', 'age_grp1')
:
: Microsoft JET Database Engine error '80040e14'
:
: Syntax error in INSERT INTO statement.
:
: /klazz/bdinsert.asp, line 20
:
: Would anyone shed a light on this, please?
: Thanks in advance,
:
: M.
|
|
|
View Cart
