Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Panama City, FL
    Posts
    2

    Unanswered: Retrieving temp table data from MS SQL using ASP

    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!?!?!?

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Whats the exact error

  3. #3
    Join Date
    Jun 2003
    Location
    Panama City, FL
    Posts
    2

    exact error

    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.

  4. #4
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    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

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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

  7. #7
    Join Date
    Sep 2003
    Location
    Belfast
    Posts
    1

    Re: Retrieving temp table data from MS SQL using ASP

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •