Retrieving temp table data from MS SQL using ASP

[mikeyd]

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&quot
cnn.Open(application("DBconnNewBudgetRO&quot)

'Instantiate a Recordset object.
Set rs = Server.CreateObject("ADODB.Recordset&quot
strSQL = "aTest1 '" & Session("DataYear&quot & "'"
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(&quot;FundDocument&quot & 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!?!?!?

 

[Shaggs]

Use the ADO Command and Parameter objects to pass over required parameters to the proc and put the results into a recordset.

 

[partymong]

Hi All,
I have the exactly the same problem as the initial post.
Can anyone expand on Shaggs post?

Any help would be gratefully appreciated.

Thankyou!

Regards,

P

 

[partymong]

Hi All,
I have solved my problem.

I am posting the solution, in case anyone else encounters the same problem


In my stored procedure I put my code in a transaction and I now get the result from the temporary table in a resultset.

Heres the example:-


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

I did not need to change any of my vbscript.


Regards,

P