Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Error Type: Microsoft VBScript compilation (0x800A0401)

    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

  • #2
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    by the way,

    im using MS Access 2003 for my Database...

    thanks

  • #3
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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 
    %>

  • Users who have thanked miranda for this post:

    xavatar (08-29-2007)

  • #4
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Thumbs up



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

  • #5
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    thank you very much!!!

  • #6
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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

  • #7
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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
    %>

  • #8
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    data type for code is autonumber... the rest are set to text temporarily... thanks

  • #9
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #10
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  • #11
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    i removed the code field and it worked...

    thanks!!!

  • #12
    New to the CF scene
    Join Date
    Dec 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Error

    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.

  • #13
    Regular Coder
    Join Date
    Mar 2006
    Location
    Sumter, SC
    Posts
    178
    Thanks
    10
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by Aurora View Post
    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",

  • #14
    Regular Coder
    Join Date
    Mar 2006
    Location
    Sumter, SC
    Posts
    178
    Thanks
    10
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by xavatar View Post
    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.
    Last edited by DakotaChick; 12-09-2007 at 12:57 PM. Reason: tyops :(

  • #15
    New to the CF scene
    Join Date
    Mar 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by xavatar View Post
    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


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •