Classic ASP Discussion - Insert into Access Database

Print Page | Close Window

Insert into Access Database

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=3953
Printed Date: 30 March 2026 at 5:00am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Insert into Access Database

Posted By: oska
Subject: Insert into Access Database
Date Posted: 30 June 2003 at 6:26am

Having major problems with Inserting form details into an access database, keeps bringing back:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.

but as far as I can see, the statement seems to be fine! somebody please help me out! before I resort to fisticuffs with my pc!

heres the code:
<%
`Create object. In this case Connection to a database
Set Conn = Server.CreateObject("ADODB.Connection")

`Select provider
Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
`Select data source.
`Server.MapPath function is equivalent to app.path function of VB
`It returns the directory in which the script is present
Conn.ConnectionString = "Data Source=" & Server.MapPath ("db/esurvedata.mdb")
`Open the connection
Conn.Open

Sub CheckValid(Str)
Str = Replace(Str, "`", "``")
If Str = "" THEN
Str = " "
End If
End Sub

strFname = server.HTMLencode(Request.Form("firstname"))
strSname = server.HTMLencode(Request.form("surname"))
strCname = server.HTMLencode(Request.form("companyname"))
strPosition = server.HTMLencode(Request.form("position"))
strEmail = server.HTMLencode(Request.form("email"))
strPhoneno = server.HTMLencode(Request.form("phoneno"))

Call CheckValid(strFname)
Call CheckValid(strSname)
Call CheckValid(strCname)
Call CheckValid(strPosition)
Call CheckValid(strEmail)
Call CheckValid(Phoneno)

strSQL="INSERT INTO esurvey(firstname, surname, companyname, position, email, phoneno) VALUES (` " & strFname & " ` , ` " & strSname & " ` , ` " & strCname & " ` , ` " & strPosition & " ` , ` " & strEmail & " ` , ` " & strPhoneno & " `)"

conn.execute strSQL
%>

...help!

-------------
+ osk +

Replies:

Posted By: ljamal
Date Posted: 30 June 2003 at 7:39am

change the Sub to a Function
Remove
Call CheckValid(strFname)
Call CheckValid(strSname)
Call CheckValid(strCname)
Call CheckValid(strPosition)
Call CheckValid(strEmail)
Call CheckValid(Phoneno)

Change
strFname = CheckValid(server.HTMLencode(Request.Form("firstname")))
strSname = CheckValid(server.HTMLencode(Request.form("surname")))
strCname = CheckValid(server.HTMLencode(Request.form("companyname")))
strPosition = CheckValid(server.HTMLencode(Request.form("position")))
strEmail = CheckValid(server.HTMLencode(Request.form("email")))
strPhoneno = CheckValid(server.HTMLencode(Request.form("phoneno")))

I suspect that will remedy your problem, but it not print out your SQL statement with
Response.Write strSQL
and post it

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: oska
Date Posted: 30 June 2003 at 9:08am

thanks ljamal for the advice on the sub but unfortunately after those changes the error is exactly the same -

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.

tested the sql statement in access and works fine, so I'm even more confused...

-------------
+ osk +

Posted By: ljamal
Date Posted: 30 June 2003 at 9:09am

post the SQL statement generated.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: oska
Date Posted: 30 June 2003 at 9:40am

its all good!! looks like the coloumn name 'position' maybe a reserved word in VB or something. All I had to do was change the coloumn name and it worked.

Thanks for your help though ljamal

-------------
+ osk +