Link to home
Start Free TrialLog in
Avatar of rschaeferhig
rschaeferhigFlag for United States of America

asked on

"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
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image
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
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image
Ditto for this:

rsTimeSheet.Open cmdTimeSheet, ,3, 3
ASKER CERTIFIED SOLUTION
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rschaeferhig
rschaeferhig
Flag of United States of America image

ASKER

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.
Avatar of rschaeferhig
rschaeferhig
Flag of United States of America image

ASKER

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.
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image
Did the code above work?

Fritz the Blank
Avatar of rschaeferhig
rschaeferhig
Flag of United States of America image

ASKER

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.
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image
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
Avatar of rschaeferhig
rschaeferhig
Flag of United States of America image

ASKER

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>
Avatar of rschaeferhig
rschaeferhig
Flag of United States of America image

ASKER

I read it... There is no "StillExecuting" property of the Recordset object that I can find.... The example is in VB4!!
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image
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
Avatar of rschaeferhig
rschaeferhig
Flag of United States of America image

ASKER

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.