Is it possible to submit data into a SQL database, wait for that to finish, and then return the ID generated from SQL, using Classic ASP?

Question

I have an ASP form that needs to submit data to two different systems. First the data needs to go into an MS SQL database, which will get an ID. I then need to submit all that form data to an external system, along with that ID.

Pretty much everything in the code works just fine, the data goes into the database, and the data will go to the external system. The problem is I am not getting my ID back from SQL when I execute that query. I am under the impression this is happening because of how fast everything occurs in the code. The database is adding it's row at the same time my post page runs it's query to get the ID back, I think.

I need to know of a way to wait until SQL finished the insert or wait for a specific amount of time maybe. I already tried using the hacks to "sleep" with ASP, that did not help.

I am sure I could accomplish this in .Net, my background is more .Net than ASP, but this is what I have to work with on my current project.

Any ideas?

EDIT: Code from the the function writing to the DB.

driis - That was my understanding of how this should be working, but my follow up query for the ID returns nothing, so my though is that the row hasn't finished being inserted or updated yet. Maybe I am wrong on that, if so, that complicates this more. :(

Either way here is the code from the function to update the DB. Mind you this code is inherited, the rest of my project is being written by me, but I am stuck using these functions from a previous developer.

Sub DBWriteResult
    Dim connLeads
    Dim sSQL
    Dim rsUser
    Dim sErrorMsg
    Dim sLeads_Connection


  ' Connect to the Leads database
  ' -------------------------------------------------------------------
    sLeads_Connection = strDatabaseConnection


    Set connLeads = CreateObject("ADODB.Connection")
    connLeads.Provider = "SQLOLEDB.1"
    On Error Resume Next
    connLeads.Open sLeads_Connection

    If Err.number <> 0 Then

    ' Bad connection display error
        ' -----------------------------------------------------------------    
       Response.Write "Database Write Error: 001 Contact Programmer"
       Set connLeads = Nothing
       Exit Sub
    Else

    ' Verify the transaction does not already exist. 
    ' -----------------------------------------------------------------------
      Set rsUser = Server.CreateObject("ADODB.Recordset")
      rsUser.LockType = 3
      rsUser.CursorLocation = 3
      sSQL = "SELECT * "             
      sSQL = sSQL & " FROM Leads;" 
      rsUser.Open sSQL, connLeads, adOpenDynamic    

      Response.Write Err.Description
      If Err.number = 0 Then
        ' Add the record
        ' -----------------------------------------------------------
            rsUser.AddNew
            rsUser.Fields("LeadDate") = Date()&" "&Time()
            rsUser.Fields("StageNum") = ESM_StageNum
            rsUser.Fields("MarketingVendor") = ESMSourceData
            rsUser.Fields("FirstName") = ESM_FirstName
            rsUser.Fields("Prev_LName") = Request.Form ("Prev_LName")
            rsUser.Fields("LastName") = ESM_LastName
            rsUser.Fields("ProgramType") = ESM_ProgramType
            rsUser.Fields("ProgramofInterest") = ESM_ProgramofInterest
            rsUser.Fields("Phone1") = Phonenumber
                rsUser.Fields("Phone2") = ESM_Phonenumber2
            rsUser.Fields("Address1") = ESM_Address
            rsUser.Fields("Address2") = ESM_Address2
            rsUser.Fields("City") = ESM_City
            rsUser.Fields("State") = ESM_State
            rsUser.Fields("Region") = ESM_Region
            rsUser.Fields("Zip") =  ESM_Zip
            rsUser.Fields("Country") = ESM_Country
            rsUser.Fields("Email") = ESM_Email
            rsUser.Fields("MilitaryBranch") = ESM_MilitaryBranch
            rsUser.Fields("MilitaryStatus") = ESM_MilitaryStatus
            rsUser.Fields("BestTimeToCall") = ESM_BestTimeToCall
            rsUser.Fields("DateofBirth") = ESM_DateofBirth

            rsUser.Update
    Else
 ' There was an error
 Response.Write "There was an error. Error code is: "&Err.number&" "&Err.Desc

     End if

    End If

      ' Close the recordset
      ' ---------------------------------------------------------------
        Call rsUser.Close
        Set rsUser.ActiveConnection = Nothing
        Set rsUser = Nothing


  ' Destroy the connection to the database
  ' -------------------------------------------------------------------
    Set connLeads = Nothing

  End Sub

Answer 1

It sounds like you're trying to do this:

1. Insert some data in DB 1
2. Retrieve an ID from the inserted data
3. Send data + the ID to DB 2

It's been a good five years but I believe it looked something like this:

dim connStr1
connStr1 = "[connection string 1]"

dim conn1
set conn1 = server.createobject("adodb.connection")
conn1.open connStr1

dim sql
sql = " SET NOCOUNT ON " & vbCrLf & _
      " INSERT FOO (a, b, c) VALUES (1, 2, 3) " & vbCrLf & _
      " SET NOCOUNT OFF " & vbCrLf & _
      " SELECT SCOPE_IDENTITY() "

dim rs
set rs = conn1.execute(sql)
rs.close

dim id
set id = CInt(rs(0))

conn1.close

dim connStr2
connStr2 = "[connection string 2]"

dim conn2
set conn2 = server.createobject("adodb.connection")
conn2.open connStr2
conn2.execute("INSERT FOO (id, a, b, c) VALUES (" & id & ", 1, 2, 3)")
conn2.close

Good luck, and get off my lawn!

Answer 2

Ok, so I figured this one out. The problem was insane, a typo. I am spoiled with .Net and the fact that if I use a variable that doesn't really exist, I get errors. I guess ASP doesn't care so much.

On the up side, driis was correct. The code does not continue until the database transaction is completed. That was my major concern, that had incorrectly assumed that was the case. I am glad I was right.

Thanks for the help, and hopefully the next time I post it'll be something better than a tyop.

;)