Error Type: Microsoft VBScript compilation (0x800A0401)

xavatar · 08-29-2007, 06:30 PM

Hi... im trying to add a new record onto my database. However, i keep on getting this error message

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/CTs2/test1.asp, line 14, column 45
sql="INSERT INTO testTable (myname) VALUES ("Request.Form("myname")")"

im running WinXP Pro and IIS 5.1

this is my html file

<html>
<body>

<form method="POST" action="test1.asp">

<table>
<tr>
<td>CustomerID:</td>
<td><input name="myname"></td>

<input type="submit" value="Add New">
<input type="reset" value="Cancel">
</TR>
</form>

</body>
</html>

and this is my asp file

<html>
<body>

<%

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Documents and Settings\CTSLab\Desktop\lab webby\webby 2\test1.mdb"

sql="INSERT INTO testTable (myname) VALUES ("Request.Form("myname")")"

on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>

</body>
</html>

.... already checked folder and file permissions

thanks

xavatar · 08-29-2007, 06:33 PM

by the way,

im using MS Access 2003 for my Database...

thanks

miranda · 08-29-2007, 07:39 PM

There are 3 things wrong.

I see two things wrong with the Sql insert statement itself. 1st, You are stoping the Insert with the double Quote " but then do not use the concatenation operator to add the form value and then have the double quote again immediately after the form value. Also if this is not a numeric datatype for the datafield you need to enclose the info in single quotes. like so

Code:

sql="INSERT INTO testTable (myname) VALUES ('" & Request.Form("myname") & "')"

Also recaffected is not used.

When you use the Execute method of the connection object there is only one parameter passed and that is the sql query.

Also using this kind of insert opens you up to SQL interjection attacks. (that is where someone tries to take control of your database) The following will help prevent it. Since this is an Access database the part of the function that replaces the double dashes isn't needed but it doesn't hurt to have it in there.

Code:

Private Function preventInjection(ByRef theString)
    theString = Replace(theString, ";", ";")  'removes semicolon
    theString = Replace(theString, "'", "'") 'removes lone apostrophe's '
    theString = Replace(theString, "--", "--") 'removes double dash sql comment
    preventInjection = theString
End Function

So your final code to insert will look like this

Code:

<%
Private Function preventInjection(ByRef theString)
    theString = Replace(theString, ";", ";")  'removes semicolon
    theString = Replace(theString, "'", "'") 'removes lone apostrophe's '
    theString = Replace(theString, "--", "--") 'removes double dash sql comment
    preventInjection = theString
End Function

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Documents and Settings\CTSLab\Desktop\lab webby\webby 2\test1.mdb"

sql="INSERT INTO testTable (myname) VALUES ('" & preventInjection(Request.Form("myname")) & "')"

on error resume next
conn.Execute(sql)
if err<>0 then
     Response.Write("No update permissions!")
else 
     Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close  'closes object
Set conn =Nothing  'clears object from memory 
%>

xavatar · 08-29-2007, 07:51 PM

you were great.. its working now... thanks!!!

xavatar · 08-29-2007, 07:52 PM

thank you very much!!!

miranda · 08-29-2007, 07:56 PM

BY the way i forgot that the the actual characters will display and not the ascii values. Normally the function looks like this

Just remove the empty space after the ampersands and the pound signs

Code:

Private Function preventInjection(ByRef theString)
    theString = Replace(theString, ";", "& # 59;")  'removes semicolon
    theString = Replace(theString, "'", "& # 39;") 'removes lone apostrophe's '
    theString = Replace(theString, "--", "& # 45;& # 45;") 'removes double dash sql comment
    preventInjection = theString
End Function

xavatar · 08-29-2007, 08:59 PM

Hi, im back with another problem...

i did what you asked me to do earlier and it worked perfectly. When i tried to apply what you said to my main code... i keep on getting "No Update Permission!"

Im pretty sure im doing something wrong.... hope you can help me with this...

Thanks!!!

<%
Private Function preventInjection(ByRef theString)
theString = Replace(theString, ";", ";") 'removes semicolon
theString = Replace(theString, "'", "'") 'removes lone apostrophe's '
theString = Replace(theString, "--", "--") 'removes double dash sql comment
preventInjection = theString
End Function

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Documents and Settings\CTSLab\Desktop\lab webby\webby 2\LabApp.mdb"

sql="INSERT INTO borrowTable (code, userName, userPosition, userPassword, borrowerName, borrowerBadge, servicetag, systemtype, timeBorrowed) VALUES ('" & preventInjection(Request.Form("userName")) & preventInjection(Request.Form("position")) & preventInjection(Request.Form("userPassword")) & preventInjection(Request.Form("borrowerName")) & preventInjection(Request.Form("borrowerBadge")) & preventInjection(Request.Form("servicetag")) & preventInjection(Request.Form("systemtype")) & preventInjection(Request.Form("timeBorrowed")) & "')"

on error resume next
conn.Execute(sql)
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close 'closes object
Set conn =Nothing 'clears object from memory
%>

xavatar · 08-29-2007, 09:00 PM

data type for code is autonumber... the rest are set to text temporarily... thanks

Daemonspyre · 08-29-2007, 09:20 PM

You are getting an error because your SQL statement is still a little off...

You need to add single quotes and commas between each and every value, not just the at the beginning and the end of your statement.

Code:

sql="INSERT INTO borrowTable (code, userName, userPosition, userPassword, borrowerName, borrowerBadge, servicetag, systemtype, timeBorrowed) VALUES ('" & preventInjection(Request.Form("userName")) & "','" & preventInjection(Request.Form("position")) & "','" & preventInjection(Request.Form("userPassword")) & "','" & preventInjection(Request.Form("borrowerName")) & "','" & preventInjection(Request.Form("borrowerBadge")) & "','" & preventInjection(Request.Form("servicetag")) & "','" & preventInjection(Request.Form("systemtype")) & "','" & preventInjection(Request.Form("timeBorrowed")) & "')"

Also, before you try to execute your code, it's always a good idea to run a response.write so you can see if there are any mistakes.

Code:

sql="INSERT INTO borrowTable (code, userName, userPosition, userPassword, borrowerName, borrowerBadge, servicetag, systemtype, timeBorrowed) VALUES ('" & preventInjection(Request.Form("userName")) & "','" & preventInjection(Request.Form("position")) & "','" & preventInjection(Request.Form("userPassword")) & "','" & preventInjection(Request.Form("borrowerName")) & "','" & preventInjection(Request.Form("borrowerBadge")) & "','" & preventInjection(Request.Form("servicetag")) & "','" & preventInjection(Request.Form("systemtype")) & "','" & preventInjection(Request.Form("timeBorrowed")) & "')"

response.write sql
response.flush
schtopp   'this will force the page to stop. It's OK, as we want that to happen

'on error resume next

xavatar · 08-29-2007, 09:37 PM

thanks for you quick response....

i tried what you said... but now im getting this...

------------

INSERT INTO borrowTable (code, userName, userPosition, userPassword, borrowerName, borrowerBadge, servicetag, systemtype, timeBorrowed) VALUES ('12312','12312','12312','12312','12312','12312','12312','t12312est')
Microsoft VBScript runtime error '800a000d'

Type mismatch: 'schtopp'

/CTs2/borrowsystem.asp, line 207

------------

if i take out this line

response.write sql
response.flush
schtopp 'this will force the page to stop. It's OK, as we want that to happen

i still get "No update permissions!"

... thanks

xavatar · 08-29-2007, 10:02 PM

i removed the code field and it worked...

thanks!!!

Aurora · 12-08-2007, 07:43 PM

Hello to everyone who read my thread,

I'm having an error in SQL part. I'm using ASP 2003 and SQL Server 7.0. This is my error.

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/Crystal_1/productlist.asp, line 19, column 23
"ORDER BY product_name",
------------------------^

And This is my coding,

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" &_
"Server=Aurora;" &_
"Database=CRYSTAL;" &_
"Uid=sa;" &_
"Pwd=;"

Set conn= Server.CreateObject("ADODB.Recordset")

conn="SELECT product_id,product_picture,product_name,product_briefDesc " &_
"FROM Product WHERE product_category= '" &cat& "' " &_
"AND status=1" &_
"ORDER BY product_name",

Anyone please kindly reply the solution for this error. Thank a lot. God Bless You.

DakotaChick · 12-09-2007, 11:55 AM

Quote:

Originally Posted by Aurora

Hello to everyone who read my thread,

I'm having an error in SQL part. I'm using ASP 2003 and SQL Server 7.0. This is my error.

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/Crystal_1/productlist.asp, line 19, column 23
"ORDER BY product_name",
------------------------^

Remove the , at the end of the conn= statement

Code:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" &_ 
"Server=Aurora;" &_ 
"Database=CRYSTAL;" &_
"Uid=sa;" &_ 
"Pwd=;"

Set conn= Server.CreateObject("ADODB.Recordset")

conn="SELECT product_id,product_picture,product_name,product_briefDesc " &_ 
 "FROM Product WHERE product_category= '" &cat& "' " &_
 "AND status=1" &_
 "ORDER BY product_name",

DakotaChick · 12-09-2007, 11:57 AM

Quote:

Originally Posted by xavatar

i removed the code field and it worked...

thanks!!!

The joy of autonumber data types is that you dont have to insert it into the table, its done AUTOmatically when a new record is added.

kartal · 03-24-2008, 03:12 AM

Quote:

Originally Posted by xavatar

i removed the code field and it worked...

thanks!!!

hi

I got the same error message. you said I removed the code field
I didnt understand which code field did you remove
if you had these codes can u send me

my code is:
<%
Private Function preventInjection(ByRef theString)
theString = Replace(theString, ";", ";") 'removes semicolon
theString = Replace(theString, "'", "'") 'removes lone apostrophe's '
theString = Replace(theString, "--", "--") 'removes double dash sql comment
preventInjection = theString
End Function

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\wwwroot\seref\ozo.mdb"

sql="INSERT INTO test (ad,soyad) VALUES ('" & preventInjection(Request.Form("ad")) & "','" & preventInjection(Request.Form("soyad")) & "')"

on error resume next
conn.Execute(sql)
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close 'closes object
Set conn =Nothing 'clears object from memory
%>

the result is:

no update permissions

if anyone help me I will be happy

...thanks