syntax error connecting database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike 1983 - 30 May 2008 01:17 GMT

I'm a novice in visual studio and access database.I need to connect my
access database, but i keep getting the following error:
Script error detected at line 44.
Source line: con.Execute sql_insert
Description: Syntax error in INSERT INTO statement.

My code is:

<%

' Declaring variables

Dim firstname, lastname, email, password, data_source, con, sql_insert

' A Function to check if some field entered by user is empty

Function ChkString(string)

If string = "" Then string = " "

ChkString = Replace(string, "'", "''")

End Function

' Receiving values from Form

firstname = ChkString(Request.Form("firstname"))

lastname = ChkString(Request.Form("lastname"))

email = ChkString(Request.Form("email"))

password = ChkString(Request.Form("password"))

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _

Server.MapPath("mydatabase.mdb")

sql_insert = "insert into Customers (Firstname, Lastname, Email_adres,
Password) values ('" & _

firstname & "', '" & lastname & "', '" & email & "', '" & password & "')"

' Creating Connection Object and opening the database

Set con = Server.CreateObject("ADODB.Connection")

con.Open data_source

con.Execute sql_insert

response.write ""

' Done. Close the connection

con.Close

Set con = Nothing

%>

I've looked into my access but i couldn't find anything . Can someone help
me? I appreciate it

Reply to this Message

Bob Barrows [MVP] - 30 May 2008 01:48 GMT

> I'm a novice in visual studio and access database.I need to connect my
> access database, but i keep getting the following error:
> Script error detected at line 44.
> Source line: con.Execute sql_insert
> Description: Syntax error in INSERT INTO statement.

Your subject line is misleading: you have no problem connecting to the
database. You are having a problem with getting the database to execute a
sql string you have built via concatenation in vbscript (dynamic sql). So
...
You cannot troubleshoot a sql statement without knowing what it is. You need
to write it to response to see what it is:

sql_insert = "insert into Customers (Firstname, Lastname, Email_adres,
Password) values ('" & _
firstname & "', '" & lastname & "', '" & email & "', '" & password & "')"

Response.Write sql_insert
Response.End

Run the page and look at the sql statement in the browser window. Does it
look correct? If you have built it correctly, you should be able to copy it
to the clipboard, open your database in Access, create a new query in Design
mode, switch to SQL View, paste in the sql statement, and run it without
modification. Can you do this? Do you get a better error message to help you
figure out the problem?

If you cannot figure out the problem (it should really stick out like a sore
thumb at this point), show US the sql statement.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36
562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%4
0TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.402
0%40tk2msftngp13.phx.gbl

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Mike Brind [MVP] - 30 May 2008 23:40 GMT

> I'm a novice in visual studio and access database.I need to connect my
> access database, but i keep getting the following error:
[quoted text clipped - 36 lines]
> sql_insert = "insert into Customers (Firstname, Lastname, Email_adres,
> Password)

Password is a reserved word in Jet SQL. Change the field name or enclose it
in brackets in your SQL:

Insert Into Customers (Firstname, Lastname, Email_adres, [Password]).....

--
Mike Brind
Microsoft MVP - ASP/ASP.NET

Reply to this Message

Webmaster Forum / ASP / Database Access / May 2008

syntax error connecting database