Retrieving temp table data from MS SQL using ASP

rodrich · #1 (**permalink**) 06-19-03, 12:53

I am trying to retrieve data from a stored procedure in MS SQL 2000 that has had to use temporary tables in coming up with the required dataset. However, when I call the procedure from a web page in which I am using VB script to call and retrieve the data, the recordset is always closed when I try to use it.

ex.

'Establish a connection with data source.
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open(application("DBconnNewBudgetRO"))

'Instantiate a Recordset object.
Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "aTest1 '" & Session("DataYear") & "'"
rs.Open strSQL, cnn

' ANY statement that trys to use the dataset rs gets the following error
' Operation is not allowed when object is closed
DO UNTIL rs.EOF <--- crash here
Response.Write rs.Fields("FundDocument") & vbCrLf
rs.MoveNext
LOOP

rs.Close
Set rs = nothing

There MUST be a way to return data from MS SQL that used temp tables to gather and format the records... but I have had no luck at all. HELP!?!?!?

rhs98 · #2 (**permalink**) 06-24-03, 09:46

Whats the exact error

rodrich · #3 (**permalink**) 06-25-03, 21:09

like I said in the example code... it complains that the recordset is closed when I try to do anything with it after a call to any stored procedure that used temp tables. The exact error message says 'Operation is not allowed when object is closed'. I am perplexed.

Memnoch1207 · #4 (**permalink**) 06-26-03, 14:21

This checks to see if the rs.state is closed (0), and if it is then it opens it.

Code:

If(rs.State = 0) then
   rs.State = 1
End if

rhs98 · #5 (**permalink**) 07-02-03, 12:52

try this (no idea if it will work);

Code:

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open(application("DBconnNewBudgetRO"))

Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "aTest1 '" & Session("DataYear") & "'"
rs.Open strSQL, cnn

while not(rs.EOF)
 Response.Write rs.Fields("FundDocument") & vbCrLf
 rs.MoveNext
wend

rs.Close
Set rs = nothing

Seppuku · #6 (**permalink**) 07-09-03, 18:57

Can you attach the code for the stored procedure?

You have to make sure you're doing a SELECT out of that temp table at the end of your stored procedure if you want it returned to your record set.

It should look similar to this:

Code:

CREATE PROCEDURE dbo.spMyStoredProc
    --Define your inbound parameters
AS
    SET NOCOUNT ON

    --Declared a bunch of variables

    CREATE TABLE #tempTable (col1 numeric, col2 varchar(50))

    --Do some work to find rows, and insert them into the temp table

    SELECT * FROM #tempTable
    DROP TABLE #tempTable

    SET NOCOUNT OFF
GO

d96bfe · #7 (**permalink**) 09-29-03, 12:06

I had been experiencing the same problem myself. I used the Set NoCount On and Set No Count Off but this still did not work for me. But when using this and also putting the code into a transaction this worked. I hope it also solves your problem.

My code now looks something like the following:

CREATE PROCEDURE sp_StoredProcName
@Param1 As varchar(10),
@Param2 As int
AS
BEGIN TRAN

SET NOCOUNT ON

CREATE TABLE #tempTable(Field1 varchar(10), Field2 int)

INSERT INTO #tempTable(Field1 varchar(10), Field2 int) Values(@Param1, @Param2)

SELECT * FROM #tempTable
DROP TABLE #tempTable

SET NOCOUNT OFF
COMMIT TRAN
GO

Internet Services

Web Development

Digital Marketing

Consumer Tech