"Connection is busy with results from another hstmt" using Command Object

I've been trying to convert an ASP application that opens multiple recordset objects using complex SQL SELECT strings to use command objects that call stored procedures with parameters. I keep getting the error message listed in the title. I've tried using parameter collections and adCmdStoredProc, but the resulting recordset defaults to adForwardOnly (firehose mode). I thought the example below, setting the recordset cursor-type explicitly, would fix it but it didn't. Any ideas? The error occurs in the "rsRC.Open" statement.

Sample code:

set cmdTimeSheet = server.CreateObject("ADODB.Command")
cmdTimeSheet.ActiveConnection = dbconn
set rsTimeSheet = server.CreateObject("ADODB.Recordset")
cmdTimeSheet.CommandText = "Exec sp_timesheet_rsTimeSheet " & "'" & strreqdate & "' , '" & struser & "' ,'" & strSortBy & "'"
rsTimeSheet.Open cmdTimeSheet, ,3, 3
set cmdRC = server.CreateObject("ADODB.Command")
cmdRC.ActiveConnection = dbconn
set rsRC = server.CreateObject("ADODB.Recordset")
cmdRC.CommandText = "Exec sp_timesheet_rowcount "& "'" & strreqdate & "' , '" & struser & "'"
rsRC.Open cmdRC, , 3, 3
do until rsRC.EOF
  for each x in rsRC.Fields
    Response.Write(x.name)
    Response.Write("=")
    Response.Write(x.value & "<br />")
  next
  Response.Write("<br /")
  rsRC.MoveNext
loop
rsRC.MoveFirst
rschaeferhigAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fritz_the_blankCommented:
I could be wrong, but I think that the problem is here:

rsRC.Open cmdRC, , 3, 3

because cmdRC is a command object and the recordset object is looking for some kind of text parameter...

Fritz the blank
0
fritz_the_blankCommented:
Ditto for this:

rsTimeSheet.Open cmdTimeSheet, ,3, 3
0
fritz_the_blankCommented:
I am not sure if this will work, but give it a try:

set cmdTimeSheet = server.CreateObject("ADODB.Command")
set rsTimeSheet = server.CreateObject("ADODB.Recordset")
strTimeSheet = "Exec sp_timesheet_rsTimeSheet " & "'" & strreqdate & "' , '" & struser & "' ,'" & strSortBy & "'"
rsTimeSheet.Open strTimeSheet,dbconn,3,3
set rsRC = server.CreateObject("ADODB.Recordset")
strRC= "Exec sp_timesheet_rowcount "& "'" & strreqdate & "' , '" & struser & "'"
rsRC.Open strRC,dbconn,3,3
do until rsRC.EOF
  for each x in rsRC.Fields
    Response.Write(x.name)
    Response.Write("=")
    Response.Write(x.value & "<br />")
  next
  Response.Write("<br /")
  rsRC.MoveNext
loop
rsRC.MoveFirst
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

rschaeferhigAuthor Commented:
The doc for the Recordset object Open method says the first parameter can be "A Command object name, SQL statement, table name, or stored procedure".

There are previous Opens using Command objects and I can even access the data. It's just when I get to a certain number of them (it seems....) that I get the error.
0
rschaeferhigAuthor Commented:
That's another option, Fritz. I was using Command objects to initially get rid of the SQL string and use Parameter arrays. Unfortunately the recordsets returned by that method are intrinsically forward-only and can't be changed (or so it seems). I need to be able to Move, and I need Recordcount. So I've been backing out one change at a time to find a compromize method. I may have to go back to just using the "Exec SP" SQL strings in the rs.Open method.

Thanks.
0
fritz_the_blankCommented:
Did the code above work?

Fritz the Blank
0
rschaeferhigAuthor Commented:
Your code is essentially where I started. It works, but I lose the Recordcount property of the recordset. This requires that I count the records myself (time-consuming) and then use that counter in about 20 places in the code. I was trying to avoid that, hoping that using Command objects would give me ResultSets that supported the Recordcount property. That's when I started running into the cursor/firehose issue indicated by the error message.

Thanks.
0
fritz_the_blankCommented:
Fair enough--I was just wondering about the grade of B given that it solved the problem as asked.

Would it be possible to include a count(*) in your stored procs? If so, then you could just grab that field out of the results. Also, if you do rsRC.Open strRC,dbconn,3,3, rsRC.RecordCount() should return a value as it is not a firehose cursor.

FtB
0
rschaeferhigAuthor Commented:
Actually, it didn't solve the problem... I did get the code to get all the way through the VBScript and build all the recordsets, but the first line in the HTML that references a field in one of the recordset rows raises the same error again.

As much as I hate to do it, here's a sanitized copy of the VBscript.

<%@ Language=VBScript %>
<%response.buffer = true%>
<HTML>
<HEAD>
</HEAD>
<BODY>
<%
set rsProjJob = server.CreateObject("ADODB.Recordset")
sql = "Select * from ts_rsProjJob"
rsProjJob.Open sql, dbconn, adOpenStatic, adLockOptimistic
if Request.QueryString("ContID") = "" then
   sql = "Select * from Contact where cnt_ContactID = " & session("UserKey")
else
   sql = "Select * from Contact where cnt_ContactID = " & Request.QueryString("ContID")
end if

set rsUser = server.CreateObject("ADODB.Recordset")
rsUser.Open sql, dbconn,  adOpenStatic, adLockOptimistic

'Figure out date of Last Day of Current week (saturday)
if Request.QueryString("weekend") = "" then
   a bunch of if/thens to calculate a date
else
   strDate = formatdatetime(cdate(Request.QueryString("weekend")),2)
end if
strListDate = formatdatetime(cdate(strDate) - 182,2)
strSortBy = ""
if Request.QueryString("SortBy") <> "" then
   strSortBy = Request.QueryString("SortBy")
else
   if Request.Form("SortBy") <> "" then
      strSortBy = Request.Form("SortBy")
   else
      strSortBy = "job_JobID"
   end if
end if

if Request.QueryString("ContID") = "" then
   strreqdate = strdate
   struser = session("USERKEY")
else
   strreqdate = Request.QueryString("WeekEnd")
   struser = Request.QueryString("ContID")
end if
sql = "Exec sp_timesheet_rsTimeSheet " & "'" & strreqdate & "' , '" & struser & "' ,'" & strSortBy & "'"
set rsTimeSheet = server.CreateObject("ADODB.Recordset")
rsTimeSheet.Open sql,dbconn , adOpenStatic, adLockOptimistic
introws=0
if not(rsTimeSheet.EOF)then    ' new timesheets have no entries
   while not(rsTimeSheet.EOF)  ' old timesheets - count the entries
     introws = introws + 1
     rsTimeSheet.MoveNext
   wend
rsTimeSheet.MoveFirst            ' reset the cursor
end if
%>

And here's what breaks. It's the first reference to any of the recordsets.

<TD NOWRAP WIDTH = "50%">
   <B><font face=verdana,geneva,arial size=-2 color="#800000">
          Employee Name: </FONT></B>
         <FONT color=#ff0066 face="Times New Roman" size=3>
<B><I><%=rsUser.Fields("cnt_ContactLastName").value & ", " & rsUser.Fields("cnt_ContactFirstName").value %></I><B></font>
</TD>
0
rschaeferhigAuthor Commented:
I read it... There is no "StillExecuting" property of the Recordset object that I can find.... The example is in VB4!!
0
fritz_the_blankCommented:
hmmn--

I am at a little of a loss here. If I can think of anything, I'll be sure to let you know.

If you like, you might be able to get a point refund by posting a message in Community Support...

FtB
0
rschaeferhigAuthor Commented:
Not necessary. You put in a lot to try and help and I appreciate it. If you can think of anything let me know.

It just doesn't make sense that the exact same code using complex SELECT statements for the SQL works and changing the SQL to execute Stored Procedures (with parms) or access a View (without parms) results in this error. Something unique about the recordsets returned from Views and SP's vs. those created "on-the-fly" with native SQL statements?

Hmm.... I don't know.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.