Solved

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

Posted on 2003-10-29
13
510 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

16 Experts available now in Live!

Get 1:1 Help Now