failure in inserting data into database

ckmun81 · October 29th, 2003, 09:08 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

sal · October 29th, 2003, 10:47 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

pgtips · October 30th, 2003, 07:30 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

urbanPuppy · December 21st, 2003, 08:09 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