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 11 of 11
  1. #1
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post

    Error: Operation is not allowed when the object is closed.

    Hello

    I am getting the following error:
    ADODB.Recordset error '800a0e78'

    Operation is not allowed when the object is closed.

    /display_records3.asp, line 83
    Line 83 is this:
    Code:
    If rs.EOF Then
    However, as far as I can see, what I am trying to do (add records) takes place while the connection is open. Here is the relevant code:

    Code:
    <% 
    
     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
     
    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 conn = Server.CreateObject("ADODB.Connection")
     Set rs = Server.CreateObject("ADODB.Recordset")
    
    
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
     conn.Open sConnString
    
     rs.Open sSQL,conn
    
    'conn.Execute sSQL
    
    %>
    
    <%
    'Determine whether there are any records
    
     If rs.EOF Then
     Response.Write "<tr><td>No records returned.</td></tr>"
     Else
    'Loop through the fields and format
    
    Do While Not rs.EOF
    
     rs.MoveNext
     Loop
    'Close HTML table
     response.write "</table>"
     End If
    
    'close connection
     rs.Close
     conn.Close
     Set rs = Nothing
     Set conn = Nothing
     %>
    Does the error occur because I ask for
    Code:
    ="INSERT INTO tblWolf "
    BEFORE I open the database?

    Thank you.

    Steve

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    WHAT are you talking about???

    The line
    Code:
    sSQL="INSERT INTO tblWolf " _ (and continuing)
    does *NOTHING* but assign a value TO A STRING.

    It does *NOT* do ANYTHING to or with the database.

    That does not happen until you do
    Code:
     rs.Open sSQL,conn
    at which time you EXECUTE the sql command you put into the string sSQL.

    But that command is an INSERT.

    And an INSERT *will not open a recordset*.

    How can it? An INSERT does *NOT* request any records from the db, so there is NOTHING to put *IN* the recordset.

    And so the error message is correct: Your recordset never got any records, so it is considered to still be closed.

    In truth, you should not have used rs.Open to execute an INSERT query. Just use the line you commented out:
    Code:
    conn.Execute sSQL
    and get rid of the rs.Open
    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
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello Old Pedant

    Thank you again for your attention to detail.

    I had thought that my original code, which dimmed 'Connection' (a reserved word) and 'Recordset' (also a reserved word) as you will know

    http://support.microsoft.com/kb/248738

    and

    http://support.microsoft.com/kb/286335

    so I renamed them, but it has not made any difference. And even when I do this:

    Code:
    'Open the recordset object, execute the SQL statement
     'rs.Open sSQL,conn
    
    conn.Execute sSQL
    I still get exactly the same error on the same line, so the problem must be deeper, mustn't it?

  • #4
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    I should add, Old Pedant, that the column headers are visible on the Web page:

    http://www.proofreading4students.com...y_records3.asp

    Steve

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    *SIGH*

    NO PLACE in that code do you actually *OPEN* a recordset!

    If you want to get some records to display, you *MUST* do SELECT query.

    One more time: An INSERT query does *NOT* request any records at all for display. INSERT and SELECT (and DELETE and UPDATE) are ENTIRELY separate operations, and *ONLY* a SELECT (out of those 4 commands) will actually return any records.
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    In other words, *AFTER* you do
    Code:
     conn.Open sConnString
    conn.Execute sSQL
    *THEN* you *MAY* want do do
    Code:
    SQL = "SELECT ...you decide what to select..."
    Set rs = conn.Execute( SQL )
    *THEN* you will have a recordset WITH RECORDS in it that you can display.

    But if you really don't care about displaying records, then just stop after doing the INSERT. You are done.
    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.

  • #7
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello OP

    Please take a look at this:

    http://www.proofreading4students.com...y_records1.asp

    To display these records, I have:

    Code:
    <% 
    'declare your variables
     Dim Connection, Recordset
     Dim sSQL, sConnString
    
    'declare SQL statement that will query the database
    
    sSQL="SELECT TOP 10 ID, firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched FROM tblWolf"
    
    'Use "select * from ..." if you want to display all the records
     
    'define the connection string, specify db driver and database location
     sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
    
    'create an ADO connection and recordset
     Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    
    'Open the connection to the database
     connection.Open sConnString
    
    'Open the recordset object, execute the SQL statement
     recordset.Open sSQL,connection
    
    'HTML table to house our results
    
     response.write "<table width='100%' border='1'>"
    %>
    This is what you mean, isn't it?

    I have a feeling that we are speaking at cross-purposes which is probably my fault.

    When we establish that, I will elaborate a little more in my next post if that is OK?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    And what in the world does the code in your post #7 have ANYTHING to do with the code in your post #1???

    #7 is doing a SELECT query and getting a bunch of records to be displayed.

    #1 is adding one record via an INSERT query.

    I wonder if I can come up with an analogy...hmmm...

    Okay!

    #7 is like driving to the post office to open your mailbox there and finding a bunch of letters that you can then list the contents or senders of.

    #1 is like driving to the post office and then SENDING *one* letter yourself.

    TWO DIFFERENT PURPOSES.

    But you *COULD*, say, go to the post office to send a letter and then, after sending the letter, open your mail box to see what letters you have received.

    And you *CAN* do the equivalent in ASP code.

    You can add one record to your database table using an INSERT query. And then, on the same ASP page but *AS A SEPARATE OPERATION* you *can* do a SELECT query and get some records to display.

    But the two operations *ARE SEPARATE*, even if they are on the same ASP page.

    The *ONLY* thing they share are the already-open connection to the database. Kind of like you driving to the post office only once to both send a letter and open your mail box there. You save gas by only driving to the post office once, instead of making two trips. But the only thing the sending and receiving have in common is that they both needed a trip to the post office. Ditto the connection being used for both INSERT and SELECT.

    But, and this is very important, you DO NOT HAVE TO do a SELECT after doing an INSERT. You only do that if it makes sense in the logic of your application/web page.
    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.

  • #9
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    OP, YOU were instrumental in the code that displays the records in the db (link above) and for that I am grateful.

    It works! I was delighted when I saw it mirror my db.

    What I was then hoping to do was put a couple of links underneath that table that you can see on the link I sent, links such as 'Add Record', 'Delete Record', 'Modify Record' etc - if you think of an office worker having to do this type of work. On clicking these links a modal/pop-up would appear as a form in which data would be added (or deleted).

    The 'Add Record' modal would require its own code in order to add First Name, Last Name, Tel No, etc. Click on 'Add data' - done - job complete.

    Having displayed the records on the Web page indicated, this is where I am up to now. That is why I have asked you about 'insert' etc. I feel as if this INSERT page is almost complete, bar one or two errors.

    Likewise, the 'Delete Record' modal that would appear when the user clicked on the 'Delete Record' link would require its own code in order to delete First Name, Last Name, Tel No, etc.

    The modal part of things (size, colour, etc) I can look after and I am not asking you about that, but I am aware that each modal (add, modify, delete, etc) needs to be correctly coded in ASP in order to work.

    Can you not see what I was hoping to achieve? The records are now on display in the page indicated. Now I need to add to those records; later, maybe, delete one or two of them; or modify them.

    I probably prefer to have each modal/pop-up ('Add', 'Delete', 'Modify', etc, to have its own code and not for all the code to be on the one page - it would be far too confusing (at least for me).

    I should have made this clear to you from the beginning and because I didn't, I apologise. I didn't really have a concrete plan.

    But can you see now what I was intending?

    Sorry for trying your patience.

    Steve

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Okay...*NOW* it makes more sense to me. Thank you very much for explaining what you are after; it helps tons.

    Yes, you *CAN* do all you are asking for, but read again my post #8. The DELETE and INSERT operations:
    (a) can be on the same page as the SELECT but need to be *BEFORE* the select, and
    (b) they truly are completely independent of the SELECT.

    Here is a demo of the ENTIRE concept. It only shows 4 columns of your table, but it shows the important 4 for understanding thins. At the top of the table is one line with form fields to be filled in after which the user hits the "Add" button and that new record gets inserted into the table. On each line showing an existing record, there is a "Delete" button. Click on that and that record gets deleted. After either and "Add" or "Delete"--or just the first time you get to this page--the table contents are dumped to the screen.

    Obviously I couldn't actually test this code, since I don't have your database, but I know the concepts are sound, because I've used them many times. If there are bugs, they should be minor typos.
    Code:
    <!DOCTYPE html>
    <html>
    <body>
    <%
    ' first make the connection to be used by whatever else happens:
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("myDatabase.mdb") 
    
    ' now check:  Did user request to delete or insert a record?
    
    ' delete check:
    delrec = Trim(Request("deleteID"))
    If delrec <> 0 AND IsNumeric(delrec) Then
        sql = "DELETE FROM tblWolf WHERE wolfID = " & CLNG(delrec)
        howmany = 0
        conn.Execute sql, howmany
        If howmany = 0 Then
            Response.WRite "?? No wolfID of " & delrec & " found to delete??<hr>"
        End If
    End If
    
    ' insert check:
    If Trim(Request("insertRec")) = "YES" Then
        ' validate all the insert values before accepting the insert!
        ' I will show just 4 validations...you should validate EVERY field
        oops = ""
        Set RE = New RegExp
        RE.pattern = "[^a-zA-Z]"
        RE.global = True
    
        wolfID = Trim(Request("wolfid"))
        If Not IsNumeric(wolfID) Then
            oops = oops & "<li>wolfID is not a number</li>"
        Elseif CDBL(wolfID) <> CLNG(wolfID) OR CLNG(wolfID) <= 0 Then
            oops = oops & "<li>wolfID must be an integer greater than zero</li>"
        End If
    
        fname = Trim(Request("firstname"))
        If Len( RE.replace( fname, "" ) ) < 2 Then
            oops = oops & "<li>first name should be at least 2 letters long</li>"
        End If
        lname = Trim(Request("lastname"))
        If Len( RE.replace( lname, "" ) ) < 3 Then
            oops = oops & "<li>last name should be at least 3 letters long</li>"
        End If
    
        RE.pattern = "[^\d]"
        phone = RE.replace( Trim(Request(phone)), "" ) ' zap all but digits of phone
        If Len(phone) = 11 AND Left(phone,1) = "1" Then phone = Mid(phone,2) ' zap leading 1 if 11 digits
        If Len(phone) <> 10 Then
            oops = oops & "<li>phone number must be 10 digits in length</li>"
        End If
        
        If oops <> "" Then
            Response.Write "Unable to insert that data because:<ul>" & oops & "</ul><hr/>"
        Else
            ' okay to do the insert
            ' protect against SQL injection and allow apostrophes in names, thus:
            fname = Replace( fname, "'", "''")
            lname = Replace( lname, "'", "''")
            
            ' create the sql:
            sql = "INSERT INTO tblWolf( wolfid, firstname, lastname, phone ) " _
                & " VALUES( " & wolfID & ",'" & fname & "','" & lname & "','" & phone & "')"
            On Error Resume Next
                conn.Execute sql ' do the insert
                IF Err.Number > 0 Then
                    Response.Write "Insert failed: " & Err.Description & "<br/>" & sql 
                    Response.End ' abort?
                End If
            On Error GoTo 0
        End If
    End If
    
    ' *NOW* you are ready to display the current contents of tblWolf to the user.
    ' But let's make sure there is a line for adding a record:
    %>
    <table border="1" cellpadding="5">
    <tr>
        <th>Action</th>
        <th>WolfID</th>
        <th>Last name</th>
        <th>First name</th>
        <th>Phone</th>
    </tr>
    <tr>
        <form method="post">
        <input type="hidden" name="insertRec" value="YES" />
        <td><input type="submit" value="Add" /></td>
        <td><input name="wolfID"/></td>
        <td><input name="lastname"/></td>
        <td><input name="firstname"/></td>
        <td><input name="phone"/></td>
        </form>
    </tr>
    <tr>
        <td colspan="5"><hr/></td>
    </tr>
    <%
    ' and now dump out existing records, each with a delete button:
    sql = "SELECT * FROM tblWolf ORDER BY lastname, firstname "
    Set RS = conn.Execute( sql )
    Do Until RS.EOF
        wolfid = RS("wolfid")
        phone = RS("phone")
        ' pretty up the phone number:
        If Len(phone) = 10 Then phone = "(" & Left(phone,3) & ") " & Mid(phone,4,3) & "-" & Mid(phone,7)
    %>
    <tr>
        <td>
            <form method="post">
            <input type="hidden" name="deleteID" value="<%=wolfid%>" />
            <input type="submit" value="Delete" />
            </form>
        </td>
        <td><%=wolfid%></td>
        <td><%=RS("lastname")%></td>
        <td><%=RS("firstname")%></td>
        <td><%=phone%></td>
    </tr>
    <%
         RS.MoveNext
    Loop
    RS.Close
    conn.Close
    %>
    </table>
    </body>
    </html>
    Last edited by Old Pedant; 07-28-2013 at 03:48 AM.
    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 (07-28-2013)

  • #11
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello OP

    Many thanks for your code.

    I will try to make sense of it over the next few days.

    Thank you again for everything you have done.

    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
    •