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.
Results 1 to 5 of 5

Thread: Adding records

  1. #1
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post

    Adding records

    Hello

    This code:

    Code:
    <% 
    'declare your variables
    
    Dim conn, rs, sSQL, sConnString, my_firstName, my_lastName, my_wolfID, my_telNo, my_stAddress, my_city, my_postCode, my_items, my_received, my_dispatched
    
    my_firstName = Replace( Request.Form("firstName"), "'", "''" )
    my_lastName = Replace( Request.Form("lastName"), "'", "''" )
    my_wolfID = CDBL( Request.Form("wolfID") )
    my_telNo = CDBL( Request.Form("telNo") )
    my_stAddress = Replace( Request.Form("stAddress"), "'", "''" )
    my_city = Replace( Request.Form("city"), "'", "''" )
    my_postCode = Replace( Request.Form("postCode"), "'", "''" )
    my_items = Replace( Request.Form("items"), "'", "''" )
    my_received = CDATE( Request.Form("received") )
    my_dispatched = CDATE( Request.Form("dispatched") )
    
    
    sSQL="INSERT INTO tblWolf " _
         & "(firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched)" _
         & "  VALUES ('" & my_firstName & "', '" & my_lastName & "'," & my_wolfID & ", " _
         & my_telNo & ", '" & my_stAddress & "', '" & my_city & "', '" & my_postCode & "'," _
         & " '" & my_items & "', #" & my_received & "#, #" & my_dispatched & "#)"
    
    
    Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    
    
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
    'Open the connection to the database
     connection.Open sConnString
    
    'Open the recordset object, execute the SQL statement
     recordset.Open sSQL,connection
    
    Response.write "<div align='center'><br>The record has been added.</div>"
    
    
    'close the connection and recordset objects and free up resources
     rs.Close
     conn.Close
     Set rs = Nothing
     Set conn = Nothing
    %>
    generates the following:

    Microsoft VBScript runtime error '800a01a8'

    Object required: ''

    /addRecords.asp, line 44

    Is it likely that the source of this problem is this:

    Code:
    Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    which should be:

    Code:
    Set conn = Server.CreateObject("ADODB.Connection")
     Set rs = Server.CreateObject("ADODB.Recordset")
    because of reserved names?

    Thanks.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Nope. Completely wrong diagnosis.

    Just you making a really silly typo!

    Code:
    Set recordset = Server.CreateObject("ADODB.Recordset")
    recordset.Open sSQL,connection
    rs.Close
    WHY would you think you can close something name rs when you never OPENED anything by that name???

    USE THE SAME NAMES!
    Code:
    recordset.close
    connection.close
    Set recordset = Nothing
    Set connection = Nothing
    What you were doing is the same thing as trying to do
    Code:
    myname = "Steve H"
    Response.Write yourname
    and expecting ASP/VBScript (or *ANY* computer language, for that matter!) to figure out what you thought that myname and yourname are one and the same thing.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    And while we are at it...

    When you execute an INSERT, DELETE, or UPDATE query, *NO RECORD IS RETURNED*!!!

    How can it be? What sense would it make? You asked to change the contents of the database, but you did *NOT* ask to get some value *BACK*.

    So when you do this:
    Code:
     recordset.Open sSQL,connection
    indeed ASP will execute your sSQL (which is an INSERT), *BUT* it will *NOT* actually open any recordset, because there is NOTHING there to open!

    So it is *POINTLESS* to use a recordset object for this purpose!

    Instead, just do:
    Code:
    ... omitted ...
    
    sSQL="INSERT INTO tblWolf " _
         & "(firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched)" _
         & "  VALUES ('" & my_firstName & "', '" & my_lastName & "'," & my_wolfID & ", " _
         & my_telNo & ", '" & my_stAddress & "', '" & my_city & "', '" & my_postCode & "'," _
         & " '" & my_items & "', #" & my_received & "#, #" & my_dispatched & "#)"
    
    Set connection = Server.CreateObject("ADODB.Connection")
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
    connection.Open sConnString
    
    connnection.Execute sSQL
    Response.write "<div align='center'><br>The record has been added.</div>"
    
    connection.Close
    Set connection = Nothing ' you can do this, but pointless if this is end of page anyway
    %>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    If you would like to be just a bit more cautious, you could do:
    Code:
    ...
    connection.Open sConnString
    
    howMany = 0
    connnection.Execute sSQL, howMany
    
    If howMany = 1 Then
        Response.write "<div align='center'><br>The record has been added.</div>"
    Else
        Response.write "OOPS!  Something went wrong."
    End if
    ...
    And that howMany feature is available for INSERT, UPDATE, and DELETE queries. For UPDATE and DELETE (and for INSERT when used with MySQL, in some cases) the value of howMany tells you how many records were affected, which might be zero to any number, depending on the query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    SteveH (08-27-2013)

  • #5
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Many thanks for that OP

    I'll take my time to go through your code and post back.

    Appreciate your help.

    Steve


  •  

    Posting Permissions

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