Solved

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

Posted on 2003-10-29
13
511 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:rschaeferhig
  • 7
  • 6
13 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9644242
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9644247
Ditto for this:

rsTimeSheet.Open cmdTimeSheet, ,3, 3
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 250 total points
ID: 9644269
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
 

Author Comment

by:rschaeferhig
ID: 9644298
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
 

Author Comment

by:rschaeferhig
ID: 9644343
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9644360
Did the code above work?

Fritz the Blank
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:rschaeferhig
ID: 9644394
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9644707
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
 

Author Comment

by:rschaeferhig
ID: 9644985
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9645032
0
 

Author Comment

by:rschaeferhig
ID: 9645304
I read it... There is no "StillExecuting" property of the Recordset object that I can find.... The example is in VB4!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9645331
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
 

Author Comment

by:rschaeferhig
ID: 9645359
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now