Is it possible to send data to an SQL database, wait for it to finish, and then return the identifier generated from SQL using classic ASP?

Question

I have an ASP form that needs to send data to two different systems. First, the data must enter the MS SQL database, which will receive the identifier. Then I need to send all this form data to an external system along with this identifier.

Almost everything in the code works fine, the data goes to the database, and the data goes to the external system. The problem is that I am not getting my ID from SQL when executing this query. I get the impression that this is due to how quickly everything happens in the code. I think the database adds it at the same time that my mail page starts a query to get the identifier.

I need to know how to wait until SQL completes the insert or waits for a while. I have already tried using hacks to "sleep" with ASP, which did not help.

I am sure that I could do this in .NET, my experience is more .Net than ASP, but I have to work with this in my current project.

Any ideas?

EDIT: Code from the write function to the database.

driis - This was my understanding of how this should work, but my subsequent request for an identifier does not return anything, so mine, however, is that the line has not yet finished inserting or updating. Maybe I'm wrong if so, which complicates this more. :(

In any case, this is the code from the function for updating the database. Keep in mind that this code is inherited, the rest of my project is written by me, but I was 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

Ok, so I figured it out. The problem was crazy, a typo. I am spoiled by .Net and the fact that if I use a variable that does not actually exist, I get errors. I think ASP doesn't care so much.

At the top, driis was right. The code does not continue until the database transaction is completed. This was my main problem, which incorrectly suggested that this is so. I'm glad I was right.

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

;)

Answer 2

It looks like you are trying to do this:

• Paste some data into DB 1
• Get id from inserted data
• Send data + ID to DB 2

It has been a good five years, but I think 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 the lawn!