|
Subject:
|
failure in inserting data into database
|
|
Posted By:
|
ckmun81
|
Post Date:
|
10/29/2003 8:08:34 AM
|
i try to design a web page whereby the data entered by the user will b inserted to the database table call info.mdb. But the error saying that
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/BegASP/acknowledgement.asp, line 21
the asp code is as follow
<html>
<title> Thank you </title>
<H1> Thank you for registering with us</H1>
<body>
<%
Dim objConn, objRS, strDBPath
Set objConn = Server.CreateObject("ADODB.Connection")
strDBPath = Server.MapPath("userinfo.mdb")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
Set objRS = server.CreateObject("ADODB.Recordset")
objRs.Open "info", objConn
sql = "insert into Info (UserName, Email, PhoneNo,"
sql = sql & "Password) values"
sql = sql & "('"&Request.Form("uname")&"',"
sql = sql & " '"&Request.Form("email")&"',"
sql = sql & " '"&Request.Form("phoneno")&"',"
sql = sql & " '"&Request.Form("password")&"',)"
objRS.close
Set objRS = Nothing
objConn.close
Set objConn = Nothing
%>
</body>
</html>
help me please. thanx
|
|
Reply By:
|
sal
|
Reply Date:
|
10/29/2003 9:47:17 AM
|
Make sure you are leaving a space between your values
sql = "insert into Info (UserName, Email, PhoneNo,"
should be
sql = "insert into Info (UserName, Email, PhoneNo, "
And if you are using Access (Jet), make sure to use the semicolon ; at the end of the statement.
Try not to break up your statements into so many lines. This will confuse you from the get go.
Sal
|
|
Reply By:
|
pgtips
|
Reply Date:
|
10/30/2003 6:30:06 AM
|
Surely that can't be all of the code? All I can see is the sql is created, then the recordset and connection are closed. Where is the code that actually executes the SQL statement?
A few other points:
1. You don't need a recordset for an insert. No records will be returned.
2. Its v bad practice to just insert whatever the client types in the HTML without checking it first. Have you heard of SQL Injection Attacks? Also, your code will fail if any of the entered data contains a ' (e.g. if UserName is O'Brien). You need to replace ' with '' (that's 2 single quotes).
3. Get rid of the comma on this line: sql = sql & " '"&Request.Form("password")&"',)". There are no other fields so the comma after password is wrong.
hth
Phil
|
|
Reply By:
|
urbanPuppy
|
Reply Date:
|
12/21/2003 7:09:16 PM
|
I agree with the above comments.
I would add the following to make you code more readable.
I have seen many people use
sql = "insert into Info (UserName, Email, PhoneNo,"
sql = sql & "Password) values"
sql = sql & "('"&Request.Form("uname")&"',"
sql = sql & " '"&Request.Form("email")&"',"
sql = sql & " '"&Request.Form("phoneno")&"',"
sql = sql & " '"&Request.Form("password")&"',)"
unfortunately there is a bug in VB and string concat is leaking so this will if on a page that is not unloaded bloat your system. Also string concat takes time as VB creates new strings each time.
I would write it the following way
sql = "insert into Info "&
"(UserName, Email, PhoneNo, Password) values " &_
"('"&Request.Form("uname")&"'," &_
" '"&Request.Form("email")&"'," &_
" '"&Request.Form("phoneno")&"'," &_
" '"&Request.Form("password")&"')"
The syntax error is the extra , on the last line after password
|
