...

View Full Version : 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


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.


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


<%
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
:thumbsup: :D :thumbsup:

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


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.



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.



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
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

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
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
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

freakwar
03-19-2010, 09:19 AM
<HTML>
<body>
<%@ Page aspcompat = "true" %>

<% Dim conn, x, rs, sql, recAffected


conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.MapPath("rental.mdb"))


sql = "INSERT INTO Profile (Idnum, Name, Age, Add, Contactnum, Vidtype, Vidcat, Due) VALUES ('" & Request.Form("custid") & "' , '" & Request.Form("name") & "' , '" & Request.Form("age") & "' , '" & Request.Form("add") & "' , '" & Request.Form("cnum") & "' , '" & Request.Form("type") & "' , '" & Request.Form("cat") & "' , '" & Request.Form("due") & "')"
MsgBox("sql has " & sql)
On Error Resume Next

conn.Execute(sql)

If Err.Number <> 0 Then
Response.Write("No update permissions!")
Else
Response.Write("<h3>" & recAffected & " record added</h3>")
End If
conn.close()
conn = Nothing
%>
</body>
</HTML>

please help may i know my error??? thanks

Old Pedant
03-19-2010, 07:23 PM
(1) DO NOT add to a very very old thread like this. Create your own *NEW* thread.

(2) *REMOVE* the ON ERROR RESUME NEXT line so that you get the *REAL* error message.

(3) After you do that, post a message in a *NEW* thread that says what the real error is.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum