Web Wiz - Green Windows Web Hosting

  New Posts New Posts RSS Feed - Insert into Access Database
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Insert into Access Database

 Post Reply Post Reply
Author
oska View Drop Down
Newbie
Newbie
Avatar

Joined: 30 June 2003
Location: United Kingdom
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote oska Quote  Post ReplyReply Direct Link To This Post Topic: Insert into Access Database
    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!

Back to Top
ljamal View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 16 April 2003
Status: Offline
Points: 888
Post Options Post Options   Thanks (0) Thanks(0)   Quote ljamal Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
oska View Drop Down
Newbie
Newbie
Avatar

Joined: 30 June 2003
Location: United Kingdom
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote oska Quote  Post ReplyReply Direct Link To This Post 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...

Back to Top
ljamal View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 16 April 2003
Status: Offline
Points: 888
Post Options Post Options   Thanks (0) Thanks(0)   Quote ljamal Quote  Post ReplyReply Direct Link To This Post Posted: 30 June 2003 at 9:09am
post the SQL statement generated.
Back to Top
oska View Drop Down
Newbie
Newbie
Avatar

Joined: 30 June 2003
Location: United Kingdom
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote oska Quote  Post ReplyReply Direct Link To This Post 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

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.