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 3 of 3
  1. #1
    New to the CF scene
    Join Date
    May 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert info from one table into another table ASP & Access

    Ok, I have found various posts about inserting data from one table to another but still cannot figure out my coding issues. I have two tables in one database. tblinfo & tblresources. I have an ASP search that goes through the resources table and displays any matches when queried. When the matches are displayed I have a loop that displays [update Delete insert into HTML] buttons. Update and Delete work great, my issue is when clicking [insert into HTML] I want to take the data from whichever match I select using its ID_no and insert it into tblresources. I keep getting errors that read, "Operation is not allowed when the object is open. " I am not sure if I need 2 recordsets or only one. The code below is with 2 recordsets, I dont know if I can even do that

    Any help or wisdom is greatly appreciated.

    <%

    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsAddComments 'Holds the recordset for the new record to be added to the database
    Dim rsNewComments
    Dim strSQL 'Holds the SQL query for the database
    Dim lngRecordNo 'Holds the record number to be updated

    'Read in the record number to be updated
    lngRecordNo = CLng(Request.QueryString("ID"))

    'Create an ADO connection odject
    Set adoCon = Server.CreateObject("ADODB.Connection")

    'Set an active connection to the Connection object using a DSN-less connection
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("html_resources.mdb")

    'Set an active connection to the Connection object using DSN connection
    adoCon.Open "DSN=Addcomments"

    'Create an ADO recordset object
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")

    strSQL = "INSERT INTO tblresources (Organization, Description, url) SELECT Institution, description, url FROM info WHERE id_no=" &

    'Set the cursor type we are using so we can navigate through the recordset
    rsAddComments.CursorType = 2

    'Set the lock type so that the record is locked by ADO when it is updated
    rsAddComments.LockType = 3

    'Open the tblComments table using the SQL query held in the strSQL varaiable
    rsAddComments.Open strSQL, adoCon

    'Tell the recordset we are adding a new record to it
    rsAddComments.AddNew

    'Add a new record to the recordset
    rsAddComments.Fields("Organization") = rsNewComments.Fields("Institution")
    rsAddComments.Fields("Description") = rsNewComments.Fields("description")
    rsAddComments.Fields("url") = rsNewComments.Fields("url")

    'Write the updated recordset to the database
    rsAddComments.Update

    'Reset server objects
    'adocon.Close
    Set adoCon = Nothing
    rsAddComments.Close
    Set rsAddComments = Nothing



    'Redirect to the Terms.asp page
    Response.Redirect "Thankyou2.html"
    %>

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Well, you're telling your connection object to open one connection string, a DSN-less one, then you're telling it to open a DSN.

    Connection objects can only open one connection at a time. They can be DSN's or DSN-less, but you can't tell it to open two of them. That's where the error you quoted is probably coming from.

    The "two recordset approach" - I see only one?

  • #3
    New to the CF scene
    Join Date
    May 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When I click my link to initiate the ASP page I get this error:

    ADODB.Recordset error '800a0cc1'

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

    /ATP_TEST/resources/add_project.asp, line 38

    Oddly enough I went to check the database and found that even though I am receving this error the information is getting inserted into the database the way I need it to be. I know this error means that the database is missing the item I am trying to refer to, but I have checked and triple checked my spelling and syntax with regards to the table and data. Any ideas?

    This is the code that does work but gives me the afore mentioned error:

    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsAddComments 'Holds the recordset for the new record to be added to the database
    Dim strSQL 'Holds the SQL query for the database
    Dim lngRecordNo 'Holds the record number to be updated

    'Read in the record number to be updated
    lngRecordNo = CLng(Request.QueryString("ID"))

    'Create an ADO connection odject
    Set adoCon = Server.CreateObject("ADODB.Connection")

    'Set an active connection to the Connection object using a DSN-less connection
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("html_resources.mdb")

    'Create an ADO recordset object
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")

    'strSQL = INSERT INTO tblresources SELECT * FROM info WHERE id_no= & lngRecordNo

    strSQL = "INSERT INTO tblresources (institution, description, url) SELECT institution, description, url FROM info WHERE id_no=" &

    lngRecordNo

    'Set the cursor type we are using so we can navigate through the recordset
    rsAddComments.CursorType = 2

    'Set the lock type so that the record is locked by ADO when it is updated
    rsAddComments.LockType = 3

    'Open the tblComments table using the SQL query held in the strSQL varaiable
    rsAddComments.Open strSQL, adoCon

    'Tell the recordset we are adding a new record to it
    'rsAddComments.Update

    'Add a new record to the recordset
    rsAddComments.Fields("institution") = rsAddComments.Fields("institution")
    rsAddComments.Fields("description") = rsAddComments.Fields("description")
    rsAddComments.Fields("url") = rsAddComments.Fields("url")

    'Write the updated recordset to the database
    rsAddComments.Update

    'Reset server objects
    'adocon.Close
    Set adoCon = Nothing
    rsAddComments.Close
    Set rsAddComments = Nothing

    'Redirect to the Terms.asp page
    Response.Redirect "Thankyou2.html"
    %>


  •  

    Posting Permissions

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