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 4 of 4
  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    215
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Need multiple SQL statements to run...

    Hello all. I am not too familiar with using multiple recordsets to insert multiple records at one time, so my code may be a little 'laughable', but here it goes.

    What I am wanting to do is to create a record in one table, and then get the ID (Autonumber) of that record and use it to populate a field in another table called LinkId. Here is what I have so far:

    Code:
    <%
    Dim oConn, oRS, sQry
    Dim sConn
    
    set oConn = Server.CreateObject("ADODB.Connection")
    'Relpace Xxxxx with the name of your dsn
    sConn = MM_timesheet_STRING
    oConn.Open sConn
    Set oRS = Server.CreateObject("ADODB.Recordset")
    Set oRS.ActiveConnection = oConn
    
    'SQL RecordSet 
    sQry = "INSERT INTO tblEquipment (EqNum, EqDesc, Active) VALUES ('" & EqNum & "', '" & EqDesc & "', -1)"
    oRS.Open sQry
    
    'oRS.Close()
    'Set oRS = Nothing
    %>
    
    <%
    Dim oConn2, oRS2, sQry2
    Dim sConn2
    
    set oConn2 = Server.CreateObject("ADODB.Connection")
    'Relpace Xxxxx with the name of your dsn
    sConn2 = MM_timesheet_STRING
    oConn2.Open sConn2
    Set oRS2 = Server.CreateObject("ADODB.Recordset")
    Set oRS2.ActiveConnection = oConn2
    
    'SQL RecordSet 
    sQry2 = "SELECT ID  FROM tblEquipment  WHERE EqNum = '" & EqNum & "'"
    oRS2.Open sQry2
    ' ------------------------------------------------
    
    LinkId = oRS2("ID")
    Response.Write("LinkId = " & LinkId & "<BR>")  ' For testing purposes
    %>
    Well, what is happening is that I am getting the following error:

    Code:
    ADODB.Field error '800a0bcd' 
    
    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. 
    
    /Equipment/Eq_AddEdit2.asp, line 74
    ... but if I refresh the page, it works, although now I have 2 identical records in the first table (except for the Autonumber of course). It seems like the second SQL statement is trying to read the table before the first one actually finished writing to the DB.

    Any suggestions would be greatly appreciated.

    Thanks,

    Parallon

  • #2
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Try this one:

    Code:
    <%
    Dim oConn, oRS, sQry
    Dim sConn
    
    set oConn = Server.CreateObject("ADODB.Connection")
    'Relpace Xxxxx with the name of your dsn
    sConn = MM_timesheet_STRING
    oConn.Open sConn
    
    'SQL RecordSet 
    sQry = "INSERT INTO tblEquipment (EqNum, EqDesc, Active) VALUES ('" & EqNum & "', '" & EqDesc & "', -1)"
    oConn.Execute(sQry)
    %>
    
    <%
    Dim oRS2, sQry2
    
    'Relpace Xxxxx with the name of your dsn
    Set oRS2 = Server.CreateObject("ADODB.Recordset")
    
    'SQL RecordSet 
    sQry2 = "SELECT ID  FROM tblEquipment  WHERE EqNum = '" & EqNum & "'"
    oRS2.Open sQry2,oConn,3
    LinkId = oRS2("ID")
    Response.Write("LinkId = " & LinkId & "<BR>")  ' For testing purposes
    %>

  • #3
    Regular Coder
    Join Date
    May 2005
    Posts
    215
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thank you so much. That worked perfectly. Although, I noticed that you added the following:

    Code:
    oRS2.Open sQry2,oConn,3
    ... what does the 3 do?

    Also, how come you don't have to run the oConn.Execute() for sQry2 like you did with sQry?

    Sorry if some of these questions are a little elementary, I am just now trying to get away from DreamWeaver writing my recordsets and other code for me.

    Thanks in advance,

    Parallon

  • #4
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts
    the 3 SSJ used is an ADO constant for the type of database cursor used. In this case it stands for adOpenStatic

    The 1st query was done using the Execute method of the Connection object.

    The second query uses the Recordset object's open method


  •  

    Posting Permissions

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