|
 |
access thread: INSERT INTO just won't work
Message #1 by "Joshua Serwatien" <webmaster@e...> on Mon, 17 Dec 2001 19:33:24
|
|
I hae a form that sending an email to the system admin with the form's
info and logging the info into an Access table. If I run the following
query in Access, it works, but not in the ASP page:
INSERT INTO IDRequests (IType, FName, LName, Password, Department, Key,
Email, Login, Computer, Requestor, Comments)VALUES
('New', 'Joshua', 'Serwatien', '8521', 'Finance', 'Yes Please', 'Yes
Please','Yes Please', 'Yes Please', '2474', 'Comment')
The original query looks like this:
strSQL="INSERT INTO IDRequests (IType, FName, LName,
Password, Department, Key, Email, Login, Computer, Requestor, DateNeeded,
Comments)"
strSQL=strSQL & "VALUES ("
strSQL=strSQL & "'" & IType & "', "
strSQL=strSQL & "'" & FName & "', "
strSQL=strSQL & "'" & Lname & "', "
strSQL=strSQL & "'" & Password & "', "
strSQL=strSQL & "'" & Department & "', "
strSQL=strSQL & "'" & Key & "', "
strSQL=strSQL & "'" & Email & "',"
strSQL=strSQL & "'" & Login & "', "
strSQL=strSQL & "'" & Computer & "', "
strSQL=strSQL & "'" & Requestor & "', "
strSQL=strSQL & "'" & Ndate & "', "
strSQL=strSQL & "'" & Comments & "')"
If I just add the Fname, Lname and Department fields (the primary keys in
the table) it works, but once I try to add any other fields it gives me
the following error:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/send/term/idhandle.asp, line 70
Browser Type:
Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
Page:
POST 219 bytes to /send/term/idhandle.asp
POST Data:
IType=New&Fname=Joshua&Lname=Serwatien&Password=8521&CPassword=8521&Departm
ent=Finance&Key=Yes+Please&Email=Yes+Please&Login=Yes+Please&Computer=Yes+P
lease&Requestor=2474&MPassword=2474&NDate=1%2F1%2F . . .
Help me please, for the love of all that's Holy!
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 17 Dec 2001 12:48:16 -0800
|
|
I don't know any ASP, but is it possible to Response.Write the contents of
your strSQL variable to a page so you can see what it looks like to the
server? If yes, try copy/pasting the resulting SQL text into the SQL view
of a new Access query.
HTH,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Joshua Serwatien [mailto:webmaster@e...]
Sent: Monday, December 17, 2001 11:33 AM
To: Access
Subject: [access] INSERT INTO just won't work
I hae a form that sending an email to the system admin with the form's
info and logging the info into an Access table. If I run the following
query in Access, it works, but not in the ASP page:
INSERT INTO IDRequests (IType, FName, LName, Password, Department, Key,
Email, Login, Computer, Requestor, Comments)VALUES
('New', 'Joshua', 'Serwatien', '8521', 'Finance', 'Yes Please', 'Yes
Please','Yes Please', 'Yes Please', '2474', 'Comment')
The original query looks like this:
strSQL="INSERT INTO IDRequests (IType, FName, LName,
Password, Department, Key, Email, Login, Computer, Requestor, DateNeeded,
Comments)"
strSQL=strSQL & "VALUES ("
strSQL=strSQL & "'" & IType & "', "
strSQL=strSQL & "'" & FName & "', "
strSQL=strSQL & "'" & Lname & "', "
strSQL=strSQL & "'" & Password & "', "
strSQL=strSQL & "'" & Department & "', "
strSQL=strSQL & "'" & Key & "', "
strSQL=strSQL & "'" & Email & "',"
strSQL=strSQL & "'" & Login & "', "
strSQL=strSQL & "'" & Computer & "', "
strSQL=strSQL & "'" & Requestor & "', "
strSQL=strSQL & "'" & Ndate & "', "
strSQL=strSQL & "'" & Comments & "')"
If I just add the Fname, Lname and Department fields (the primary keys in
the table) it works, but once I try to add any other fields it gives me
the following error:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/send/term/idhandle.asp, line 70
Browser Type:
Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
Page:
POST 219 bytes to /send/term/idhandle.asp
POST Data:
IType=New&Fname=Joshua&Lname=Serwatien&Password=8521&CPassword=8521&Departm
ent=Finance&Key=Yes+Please&Email=Yes+Please&Login=Yes+Please&Computer=Yes+P
lease&Requestor=2474&MPassword=2474&NDate=1%2F1%2F . . .
Help me please, for the love of all that's Holy!
Message #3 by "Joshua Serwatien" <webmaster@e...> on Mon, 17 Dec 2001 20:58:23
|
|
Roy, I did just that and the query worked fine (when I pasted the results
into the SQL view). The weird part is that if I have just the fname,
lname and department fields in the query, it works fine...Any ideas?
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 17 Dec 2001 13:07:13 -0800
|
|
No good ones, unfortunately. Have you searched the KB? Are you all patched
up to the current version of Jet (on your ASP server)?
I wonder if you wouldn't get better advice on a more ASP-centric list--you
might give that a try.
Good luck!
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Joshua Serwatien [mailto:webmaster@e...]
Sent: Monday, December 17, 2001 12:58 PM
To: Access
Subject: [access] RE: INSERT INTO just won't work
Roy, I did just that and the query worked fine (when I pasted the results
into the SQL view). The weird part is that if I have just the fname,
lname and department fields in the query, it works fine...Any ideas?
Message #5 by "Joshua Serwatien" <webmaster@e...> on Mon, 17 Dec 2001 21:50:38
|
|
Yeah, the same exact script is working for another form of mine on the
same server. Good figure. What's KB?
Message #6 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 17 Dec 2001 14:17:14 -0800
|
|
The microsoft knowledge base--http://www.msdn.microsoft.com/support.
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Joshua Serwatien [mailto:webmaster@e...]
Sent: Monday, December 17, 2001 1:51 PM
To: Access
Subject: [access] RE: INSERT INTO just won't work
Yeah, the same exact script is working for another form of mine on the
same server. Good figure. What's KB?
Message #7 by "Doug W" <nnnhack@y...> on Tue, 18 Dec 2001 12:33:19
|
|
Just for giggles, try:
> strSQL=strSQL & " VALUES ("
(Add the space before Values)...
> I hae a form that sending an email to the system admin with the form's
> info and logging the info into an Access table. If I run the following
> query in Access, it works, but not in the ASP page:
>
> INSERT INTO IDRequests (IType, FName, LName, Password, Department, Key,
> Email, Login, Computer, Requestor, Comments)VALUES
> ('New', 'Joshua', 'Serwatien', '8521', 'Finance', 'Yes Please', 'Yes
> Please','Yes Please', 'Yes Please', '2474', 'Comment')
>
> The original query looks like this:
>
> strSQL="INSERT INTO IDRequests (IType, FName, LName,
> Password, Department, Key, Email, Login, Computer, Requestor,
DateNeeded,
> Comments)"
> strSQL=strSQL & "VALUES ("
> strSQL=strSQL & "'" & IType & "', "
> strSQL=strSQL & "'" & FName & "', "
> strSQL=strSQL & "'" & Lname & "', "
> strSQL=strSQL & "'" & Password & "', "
> strSQL=strSQL & "'" & Department & "', "
> strSQL=strSQL & "'" & Key & "', "
> strSQL=strSQL & "'" & Email & "',"
> strSQL=strSQL & "'" & Login & "', "
> strSQL=strSQL & "'" & Computer & "', "
> strSQL=strSQL & "'" & Requestor & "', "
> strSQL=strSQL & "'" & Ndate & "', "
> strSQL=strSQL & "'" & Comments & "')"
>
> If I just add the Fname, Lname and Department fields (the primary keys
in
> the table) it works, but once I try to add any other fields it gives me
> the following error:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /send/term/idhandle.asp, line 70
>
>
> Browser Type:
> Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
>
> Page:
> POST 219 bytes to /send/term/idhandle.asp
>
> POST Data:
>
IType=New&Fname=Joshua&Lname=Serwatien&Password=8521&CPassword=8521&Departm
>
ent=Finance&Key=Yes+Please&Email=Yes+Please&Login=Yes+Please&Computer=Yes+P
> lease&Requestor=2474&MPassword=2474&NDate=1%2F1%2F . . .
>
> Help me please, for the love of all that's Holy!
Message #8 by "Joshua Serwatien" <webmaster@e...> on Tue, 18 Dec 2001 14:59:06
|
|
Good thought, Doug, but it didn't work. If you have any other ideas...
Message #9 by "Ian Haynes" <i.haynes@l...> on Wed, 19 Dec 2001 19:34:03 -0000
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_001C_01C188C4.1DDF5E30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I have a similar problem which may be related.
I have a number of webpages that read, write and update an Access db. They
were developed a couple of years ago when I was using ODBC connections
rather than OLEDB. I need to move host for the site and decided I would
convert them all to OLEDB by adding an include file containing the
connection details.
In doing this the pages that read or delete from the db all work perfectly
but the ones that INSERT or UPDATE give syntax errors in the sql statements
when used with OLEDB. All work with the ODBC connection.
So out of interest what type of connection are you using; does changing it
make any difference and does anyone have any clues as to why the connections
behave differently?
(Other similar code on another web works perfectly with OLEDB).
Ian Haynes
LimeTree Design UK
Message #10 by "Joshua Serwatien" <webmaster@e...> on Thu, 20 Dec 2001 13:08:54
|
|
I actually have this exact script running in the same directory of the
same server for another page with more fields and it works fine. The
connection I'm using is:provider=microsoft.jet.oledb.4.0. If you want, I
can send you the entire script. I use it to send an email to the lan
admin, a response email to the requestor and I log the entry into a
database. What version of Access are you using? Let me know...
|
|
|
View Cart
