SQL SP that returns no records error.

[jjjax]

We get the following error when we excute an sql sp that returns no records on the rstemp.close statement in the code below. The sp runs fine and if we comment out that statement, it works without error but is that leaving the connection open? Or, is there a way to tell it that no records will be returned. Thanks, Joe


Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

---------------------------------------------------
code:

strconn="PROVIDER=MSDASQL;DRIVER={SQL Server};"
strconn=strconn & "SERVER=xxxxxx1;DATABASE=northwind;"
strconn=strconn & "UID=xxxxx;PWD=xxxxxxx;"

mySQL="testjobfromweb"

dim conntemp, rstemp
set conntemp=server.createobject("adodb.connection&quot
conntemp.open strconn
set rstemp=conntemp.execute(MySQL)
conntemp.close
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing

 

[palbano]

>> conntemp.close
>> rstemp.close

you should not have to close the Recordset after you already closed the connection. Try reversing the order of those statements.

rstemp.close
conntemp.close

>> but is that leaving the connection open

if you close the connection it is leaving it open? Are you sure? How do you know?

-pete

 

[cathyw]

Here are a few things you can try:

1. You have conntemp.close twice in your code - once before you close the rs and once after. I think you need to remove the one before (it seems to me if you close the connection, you no longer have access to the rs, so this may be generating the error).

2. Before you attempt closing the rs, you can check the state property. If you are have included the ado constants file, you can check for adStateOpen, or if you aren't, the constant value is 1, i.e.
If rstemp.State = 1 Then
rstemp.Close
End If
In order to do this, you may need to use CreateObject to create rstemp as an ADODB.Recordset before you attempt to populate it, so if nothing comes back from the stored proc, you still have a recordset.

Hope this helps,
Cathy