asked on

Connection is busy with results for another command

Experts,

Please help ASAP. Here is my code. How can I get rid of Connection is busy with results for another command error?

Please edit the code:
Set oRs4 = Server.CreateObject("ADODB.Recordset")
sqlStmt = "SELECT * from CLF_Sample_Hazards where SampleID = 0"
      oRs4.Open sqlStmt, oConn, 1, 3
      Set oRs5 = Server.CreateObject("ADODB.Recordset")

      buff = ""
      j=0
      if Request.form("hazard"&i) <>"" then
            buff = Request.form("hazard"&i)
            options = Split(buff, " ")

            For j=0 to UBound(options) - 1
       if trim(options(j)) <>"" then
            sqlStmt = "SELECT * from CLF_Hazards where HazardDescription like '" &trim(options(j))&"%'"
            Set oRs5 = oConn.Execute(sqlStmt)
            if oRs5.BOF=false then oRs5.MoveFirst

            if not oRs5.EOF then

            oRs4.AddNew
            oRs4("SampleID")= CreateSample
            if oRs5("hazardID") <>"" then oRs4("HazardID")= oRs5("hazardID")
            oRs4("requestID") = create
            oRs4.Update '<----------------ERROR Connection is busy with results for another command

            end if

      end if

      next

end if

ASKER CERTIFIED SOLUTION

jrram

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

suran78

ASKER

I changed to your code, but now I am getting a diffrent error:

Microsoft OLE DB Provider for SQL Server error '80004005'

Cannot create new connection because in manual or distributed transaction mode.

insertSQL = "INSERT INTO CLF_Sample_Hazards(sampleID, hazardID, requestID) VALUES (" & CreateSample & "," & nhazard & "," & create & ")"
oConn.Execute insertSQL <------------ERROR

suran78

ASKER

I forgot Createsample are not functions:

Set oRs3 = Server.CreateObject("ADODB.Recordset")
      sqlStmt = "SELECT * from CLF_Samples where SampleID = 0"
      oRs3.Open sqlStmt, oConn, 1, 3 ' adKeySet, adLockOptimistic
            for i=1 to nPropertyData
            CreateSample = 0
            oRs3.AddNew
if Request.form("SampleName"&i) <>"" then oRs3("SampleName")= Request.form("SampleName"&i)'
if Request.form("chkMat"&i) <>"" then oRs3("UnknownMaterial")= Request.form("chkMat"&i)
if Request.form("SampleID"&i) <>"" then oRs3("SampleShippingID")= Request.form("SampleID"&i)
if Request.form("Otherhazard"&i) <>"" then oRs3("SampleSpecificHazard")= Request.form("Otherhazard"&i)
if Request.form("Otherhandle"&i) <>"" then oRs3("SampleSpecificHandling")= Request.form("Otherhandle"&i)
if Request.form("Samplename"&i) <>"" then oRs3("RequestID")= Create

            oRs3.Update
            CreateSample = oRs3("sampleID")