SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    011521 dbalsdon's Avatar
    Join Date
    Feb 2003
    Location
    North Of Scotland
    Posts
    444
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ADODB.Recordset error '800a0cc1'

    hi,

    i keep geting the following error

    ADODB.Recordset error '800a0cc1'

    Item cannot be found in the collection corresponding to the requested name or ordinal.

    /guestbook/edit.asp, line 51

    which refers to this line:
    <td>Name: </td><td><input type="text" name="username" value="<%= RS("username") %>" size="20" /></td>


    could somebody please tell me what i'm doing wrong??

    the code for accessing the database ect... is below

    Code:
    Set MyConn = Server.CreateObject ("ADODB.Connection")   
    Set RS = Server.CreateObject("ADODB.RecordSet")  
    MyConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _             
    "Data Source=\\premfs7\sites\premium2\dbalzdon\database\gbook.mdb;" & _             
    "Persist Security Info=False")    
    If Len (susername) > 0 AND Len (scomment) > 0 Then   
    SQL = "UPDATE messages SET username = '" & susername & "', email = '" & semail & "', website = '" & swebsite & "', comment = '" & scomment & "' WHERE message_id = umessage "  
    MyConn.Execute (SQL)   
    Response.Redirect("gbookview.asp")  
    Else  
    sSQL = "SELECT * FROM messages WHERE message_id = " & umessage & ""  
    MyConn.Execute (sSQL)  
    End if
    Daniel Balsdon
    My Site

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "username" is a reserved name in Access, as is "password". Using reserved names as fieldnames is not a good idea, try accessing RS("[username]") or try using

    select username as TheUsername

    and requesting rs("TheUsername").

    - Richard

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    You've got a few problems with your code. Your update SQL statement will fail because of misplaced quotes. It should be:

    Code:
     
    SQL = "UPDATE messages SET username = '" & susername & "', email = '" & semail & "', website = '" & swebsite & "', comment = '" & scomment & "' WHERE message_id = " & umessage

    Then, you're trying to use a recordset you never opened, so this line:
    MyConn.Execute (sSQL)
    should be something like:
    rs.Open sSQL, MyConn

    Do you have code to check for EOF and BOF? If not, you should add it...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    011521 dbalsdon's Avatar
    Join Date
    Feb 2003
    Location
    North Of Scotland
    Posts
    444
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as you can see in my coding above, I have two SQL statements to carry out....

    should i be using a seperate database connection for each one??

    ie.. open the connection, carry out the select SQL, then close...
    and then, when i need to do the update, open the connection again, carry out the Update, then close it??
    Daniel Balsdon
    My Site

  5. #5
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That error is commonly used as well when you mispell tables or fields that don't exist

    ie

    tableName.Customer is in CODE while in the database, "Customer" is not a field, but "Customers" is. Requested Name/Ordinal errors sometimes refer to that. Double check your spelling and the tables/fields in the DB

  6. #6
    011521 dbalsdon's Avatar
    Join Date
    Feb 2003
    Location
    North Of Scotland
    Posts
    444
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    right, now i'm getting the following error:
    Microsoft JET Database Engine error '80040e07'

    Data type mismatch in criteria expression.

    /guestbook/edit.asp, line 26


    the updated code is:
    Code:
    Set MyConn = Server.CreateObject ("ADODB.Connection") 
    Set RS = Server.CreateObject("ADODB.RecordSet")
    MyConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=\\premfs7\sites\premium2\dbalzdon\database\gbook.mdb;" & _
               "Persist Security Info=False")
    
    If Len (susername) > 0 AND Len (scomment) > 0 Then 
    SQL = "UPDATE messages SET userid = '" & susername & "', email = '" & semail & "', website = '" & swebsite & "', comments = '" & scomment & "' WHERE message_id = '" & umessage & "'"
    MyConn.Execute (SQL)
    Response.Redirect("gbookview.asp")
    Else
    sSQL = "SELECT * FROM messages WHERE message_id = " & umessage 
    rs.Open sSQL, MyConn
    MyConn.Execute (sSQL)
    End if
    Quote Originally Posted by ASPFAQ.com
    This error usually happens when you do one of the following things:
    attempt to insert a date in Access with ' delimiters;
    attempt to insert a date in SQL Server with # delimiters;
    attempt to insert a date in Access or SQL Server with no delimiters; or,
    attempt to insert a malformed date.
    i cant see where i'm going wrong, could somebody please help...

    PS. Thanx for the help above...
    Daniel Balsdon
    My Site

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Does umessage have a value?

    Add this line right before the line erroring out

    Response.Write sSQL : Response.End

    or (if the error is on the update line)
    Response.Write SQL : Response.End

    Also, remove this line:
    MyConn.Execute (sSQL)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    011521 dbalsdon's Avatar
    Join Date
    Feb 2003
    Location
    North Of Scotland
    Posts
    444
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, i get the following output when i use Response.Write SQL : Response.End

    UPDATE messages SET userid = 'test', email = 'test', website = 'test', comments = 'test <b>test</b>', date_time = '8/31/2003 9:22:50 AM' WHERE message_id = '52'

    another error i keep getting is:
    Microsoft VBScript runtime error '800a01a8'

    Object required: ''

    /guestbook/edit.asp, line 27

    ne ideas??
    Daniel Balsdon
    My Site


Bookmarks

Posting Permissions

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