Recordset problem

Discussion in 'ASP General' started by Dima Protchenko, Aug 4, 2004.

  1. Hi, guys.
    Please help if you know something about this.

    Error:
    ADODB.Recordset error '800a0e78'
    Operation is not allowed when the object is closed.
    line: if not rs.EOF then (from the code below)

    I have an SP on SQL2k that returns a recordset after a few data
    manipulations. The code for the SP is too long to attach here, but to
    describe it - it extracs some data from the db, stores it in the temp table
    (#), then does some data manipulation in that temp table and at the end of
    it I have:

    select * from #Courses
    drop table #Courses

    which returns my recordset. On yeah - and I DO have SET NOCOUNT ON at the
    top of the SP

    Now on the asp page I have this:

    dim conn, rs
    set conn = Server.CreateObject("ADODB.Connection")
    set rs = Server.CreateObject("ADODB.Recordset")
    conn.ConnectionString = "Provider=SQLOLEDB; server=**; Initial Catalog=SAGE;
    uid=**;pwd=**;"
    conn.Open
    rs.Open "exec dbo.usp_FindOneClass '15','2'", conn, adOpenDynamic, 1, 1

    if not rs.EOF then
    rs.MoveFirst
    do while not rs.eof
    Response.Write(rs("CourseID"))
    rs.MoveNext
    loop
    end if

    if rs.state = adStateOpen then rs.close
    set rs = nothing
    conn.Close
    set conn = nothing

    This is all pretty straight forward and I know it runs with the other SP
    that I have. SP runs fine by itself in QA too. But I still get the recordset
    error. Please help if you know the answer.

    Thanks
     
    Dima Protchenko, Aug 4, 2004
    #1
    1. Advertising

  2. Instead of using ADODB.Recordset, do this:


    set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB; server=**; Initial Catalog=SAGE;
    uid=**;pwd=**;"
    set rs = conn.execute("exec dbo.usp_FindOneClass '15','2'")

    if not rs.EOF then
    do while not rs.eof
    Response.Write(rs("CourseID") & "<br>")
    rs.MoveNext
    loop
    end if

    rs.close
    set rs = nothing
    conn.Close
    set conn = nothing


    --
    http://www.aspfaq.com/
    (Reverse address to reply.)

    "Dima Protchenko" <> wrote in message
    news:...
    > Hi, guys.
    > Please help if you know something about this.
    >
    > Error:
    > ADODB.Recordset error '800a0e78'
    > Operation is not allowed when the object is closed.
    > line: if not rs.EOF then (from the code below)
    >
    > I have an SP on SQL2k that returns a recordset after a few data
    > manipulations. The code for the SP is too long to attach here, but to
    > describe it - it extracs some data from the db, stores it in the temp
    > table
    > (#), then does some data manipulation in that temp table and at the end of
    > it I have:
    >
    > select * from #Courses
    > drop table #Courses
    >
    > which returns my recordset. On yeah - and I DO have SET NOCOUNT ON at the
    > top of the SP
    >
    > Now on the asp page I have this:
    >
    > dim conn, rs
    > set conn = Server.CreateObject("ADODB.Connection")
    > set rs = Server.CreateObject("ADODB.Recordset")
    > conn.ConnectionString = "Provider=SQLOLEDB; server=**; Initial
    > Catalog=SAGE;
    > uid=**;pwd=**;"
    > conn.Open
    > rs.Open "exec dbo.usp_FindOneClass '15','2'", conn, adOpenDynamic, 1, 1
    >
    > if not rs.EOF then
    > rs.MoveFirst
    > do while not rs.eof
    > Response.Write(rs("CourseID"))
    > rs.MoveNext
    > loop
    > end if
    >
    > if rs.state = adStateOpen then rs.close
    > set rs = nothing
    > conn.Close
    > set conn = nothing
    >
    > This is all pretty straight forward and I know it runs with the other SP
    > that I have. SP runs fine by itself in QA too. But I still get the
    > recordset
    > error. Please help if you know the answer.
    >
    > Thanks
    >
    >
     
    Aaron [SQL Server MVP], Aug 4, 2004
    #2
    1. Advertising

  3. Dima Protchenko wrote:
    > Hi, guys.
    > Please help if you know something about this.
    >
    > Error:
    > ADODB.Recordset error '800a0e78'
    > Operation is not allowed when the object is closed.
    > line: if not rs.EOF then (from the code below)
    >
    > I have an SP on SQL2k that returns a recordset after a few data
    > manipulations. The code for the SP is too long to attach here, but to
    > describe it - it extracs some data from the db, stores it in the temp
    > table (#), then does some data manipulation in that temp table and at
    > the end of it I have:
    >
    > select * from #Courses


    Nothing to do with your problem, but, you should avoid selstar (select *) in
    production code. Don't force the query engine to resolve the * into a list
    of columns every time the code runs.

    > drop table #Courses
    >
    > which returns my recordset. On yeah - and I DO have SET NOCOUNT ON at
    > the top of the SP


    Darn! That was my first culprit!

    >
    > Now on the asp page I have this:
    >
    > dim conn, rs
    > set conn = Server.CreateObject("ADODB.Connection")
    > set rs = Server.CreateObject("ADODB.Recordset")
    > conn.ConnectionString = "Provider=SQLOLEDB; server=**; Initial
    > Catalog=SAGE; uid=**;pwd=**;"
    > conn.Open
    > rs.Open "exec dbo.usp_FindOneClass '15','2'", conn, adOpenDynamic, 1,
    > 1


    Why are you attempting to open an expensive dynamic cursor? An inexpensive
    forward-only cursor would seem to suit your needs nicely.

    While some folks here prefer to use this dynamic sql approach for executing
    their stored procedures, I have some objections to it which you can read
    about here: http://tinyurl.com/jyy0

    Do this instead:

    'if you really think you need the dynamic cursor, add this line:
    rs.CursorType = adOpenDynamic
    'then:
    rs.LockType = 1
    conn.usp_FindOneClass '15','2', rs

    I am a little curious as to why you are passing this numeric data as
    strings. Are your parameters declared as numeric or char?

    >
    > if not rs.EOF then
    > rs.MoveFirst


    This MoveFirst line is completely unnecessary. The cursor will already be
    pointing at the first record immediately after opening the recordset. Not
    only is it unnecessary, in some circumstances it will cause your recordset
    to be requeried, which is a complete waste of time.

    > do while not rs.eof
    > Response.Write(rs("CourseID"))
    > rs.MoveNext
    > loop
    > end if


    See here for alternatives to slow, inefficient recordset loops
    http://www.aspfaq.com/show.asp?id=2467

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Aug 4, 2004
    #3
  4. Thanks for your posts, guys, but the problem is still there. I really think
    that it is caused by the stored procedure because when I go and change asp
    code to run a different sp, everything is working just fine.

    Part of the stored procedure creates a SQL statement using the parameters
    passed in. To answer your question, Bob, the reason why those parameters are
    strings is because I can have this:

    usp_FindOneClass '13,14,17', '2,0,2'
    and I parse them out as separate values

    SP steps:
    1. Parse and loop through passed parameters and create a SQL query string.
    2. Run that string using sp_executesql and store results in a temp table
    3. Loop through the temp table (cursor) and based on the information in each
    record perform a set of operations to create a value for the last column in
    every record
    4. select * from #temptable

    What I did at this point (so that I can move on with the project) is
    basically sacrifised step 3, changed step 2 to return the result of
    sp_executesql instead of storing it in a temp table, which also eliminated
    step 4.

    boy, I wish I could do this project in .net.....sigh
     
    Dima Protchenko, Aug 4, 2004
    #4
  5. Dima Protchenko wrote:
    > Thanks for your posts, guys, but the problem is still there. I really
    > think that it is caused by the stored procedure because when I go and
    > change asp code to run a different sp, everything is working just
    > fine.
    >
    > SP steps:
    > 1. Parse and loop through passed parameters and create a SQL query
    > string.
    > 2. Run that string using sp_executesql and store results in a temp
    > table
    > 3. Loop through the temp table (cursor) and based on the information
    > in each record perform a set of operations to create a value for the
    > last column in every record


    Why would you need a cursor to do what an UPDATE query could do?

    > 4. select * from #temptable
    >
    > What I did at this point (so that I can move on with the project) is
    > basically sacrifised step 3, changed step 2 to return the result of
    > sp_executesql instead of storing it in a temp table, which also
    > eliminated step 4.
    >
    > boy, I wish I could do this project in .net.....sigh

    I doubt that would solve this particular issue, whatever it is. When I have
    time later, I will try and reproduce it. You could save me some time by
    providing a repro script ...

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Aug 4, 2004
    #5
  6. That's a good question. I guess I am that big of a SQL expert and I didn't
    see a way to do that operation with an UPDATE statement.
    What I do in that loop is for each record, collect all records (one column)
    from another table related to that record and concatinate all those values
    into one string. I hope this explanation makes sense.

    for example if you have "Products" and "Product_Category" tables I need to
    do a query that returns this:

    "Running Shoes" | "Shoes, Sports Apparel, Jordan Gear"
    "Casio Piano" | "Electronics, Music"

    Thanks


    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Dima Protchenko wrote:
    > > Thanks for your posts, guys, but the problem is still there. I really
    > > think that it is caused by the stored procedure because when I go and
    > > change asp code to run a different sp, everything is working just
    > > fine.
    > >
    > > SP steps:
    > > 1. Parse and loop through passed parameters and create a SQL query
    > > string.
    > > 2. Run that string using sp_executesql and store results in a temp
    > > table
    > > 3. Loop through the temp table (cursor) and based on the information
    > > in each record perform a set of operations to create a value for the
    > > last column in every record

    >
    > Why would you need a cursor to do what an UPDATE query could do?
    >
    > > 4. select * from #temptable
    > >
    > > What I did at this point (so that I can move on with the project) is
    > > basically sacrifised step 3, changed step 2 to return the result of
    > > sp_executesql instead of storing it in a temp table, which also
    > > eliminated step 4.
    > >
    > > boy, I wish I could do this project in .net.....sigh

    > I doubt that would solve this particular issue, whatever it is. When I

    have
    > time later, I will try and reproduce it. You could save me some time by
    > providing a repro script ...
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    Dima Protchenko, Aug 4, 2004
    #6
  7. What I meant to say is "I guess I am NOT a big SQL expert...."

    "Dima Protchenko" <> wrote in message
    news:eZ%...
    > That's a good question. I guess I am that big of a SQL expert and I didn't
    > see a way to do that operation with an UPDATE statement.
    > What I do in that loop is for each record, collect all records (one

    column)
    > from another table related to that record and concatinate all those values
    > into one string. I hope this explanation makes sense.
    >
    > for example if you have "Products" and "Product_Category" tables I need to
    > do a query that returns this:
    >
    > "Running Shoes" | "Shoes, Sports Apparel, Jordan Gear"
    > "Casio Piano" | "Electronics, Music"
    >
    > Thanks
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:%...
    > > Dima Protchenko wrote:
    > > > Thanks for your posts, guys, but the problem is still there. I really
    > > > think that it is caused by the stored procedure because when I go and
    > > > change asp code to run a different sp, everything is working just
    > > > fine.
    > > >
    > > > SP steps:
    > > > 1. Parse and loop through passed parameters and create a SQL query
    > > > string.
    > > > 2. Run that string using sp_executesql and store results in a temp
    > > > table
    > > > 3. Loop through the temp table (cursor) and based on the information
    > > > in each record perform a set of operations to create a value for the
    > > > last column in every record

    > >
    > > Why would you need a cursor to do what an UPDATE query could do?
    > >
    > > > 4. select * from #temptable
    > > >
    > > > What I did at this point (so that I can move on with the project) is
    > > > basically sacrifised step 3, changed step 2 to return the result of
    > > > sp_executesql instead of storing it in a temp table, which also
    > > > eliminated step 4.
    > > >
    > > > boy, I wish I could do this project in .net.....sigh

    > > I doubt that would solve this particular issue, whatever it is. When I

    > have
    > > time later, I will try and reproduce it. You could save me some time by
    > > providing a repro script ...
    > >
    > > Bob Barrows
    > > --
    > > Microsoft MVP -- ASP/ASP.NET
    > > Please reply to the newsgroup. The email account listed in my From
    > > header is my spam trap, so I don't check it very often. You will get a
    > > quicker response by posting to the newsgroup.
    > >
    > >

    >
    >
     
    Dima Protchenko, Aug 4, 2004
    #7
  8. > for example if you have "Products" and "Product_Category" tables I need to
    > do a query that returns this:
    >
    > "Running Shoes" | "Shoes, Sports Apparel, Jordan Gear"
    > "Casio Piano" | "Electronics, Music"


    Or, you could return this:

    Running Shoes Shoes
    Running Shoes Sports Apparel
    Running Shoes Jordan Gear
    Casio Piano Electronics
    Casio Piano Music

    And do the formatting (inserting commas or whatever you like) at the client,
    where presentation formatting should be handled. I just answered a question
    that was similar to this, thread "so many queries within queries I'm
    confused". SQL Server is not designed to turn columns into single,
    comma-separated entities. You can kludge it up, of course, but imho that's
    really not the database's job, since you are only doing that for
    presentation.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
     
    Aaron [SQL Server MVP], Aug 4, 2004
    #8
  9. I would probably do this in the client as you have opted to do. But, if you
    are feeling adventurous, you might want to play around with creating a UDF
    that uses an unsupported behavior which has been unofficially coined
    "aggregate concatenation". As I said, it is unsupported, but I have used it
    a few times with no ill effects.

    Basically, it goes like this:

    DECLARE @str varchar(2000)
    Set @str = ''
    Select @str = Product + ', ' + @str
    FROM Products
    WHERE ProductCategory = 'Running Shoes'

    Just do not try to control the order in which the items are concatenated.


    Create a UDF, replacing 'Running Shoes' with the parameter which is passed
    to the UDF, and you can use it like this:

    Select ProductCategory, dbo.fConcatProducts([ProductCategory])
    FROM Product_Category

    HTH,
    Bob Barrows

    Dima Protchenko wrote:
    > That's a good question. I guess I am that big of a SQL expert and I
    > didn't see a way to do that operation with an UPDATE statement.
    > What I do in that loop is for each record, collect all records (one
    > column) from another table related to that record and concatinate all
    > those values into one string. I hope this explanation makes sense.
    >
    > for example if you have "Products" and "Product_Category" tables I
    > need to do a query that returns this:
    >
    > "Running Shoes" | "Shoes, Sports Apparel, Jordan Gear"
    > "Casio Piano" | "Electronics, Music"
    >
    > Thanks
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:%...
    >> Dima Protchenko wrote:
    >>> Thanks for your posts, guys, but the problem is still there. I
    >>> really think that it is caused by the stored procedure because when
    >>> I go and change asp code to run a different sp, everything is
    >>> working just fine.
    >>>
    >>> SP steps:
    >>> 1. Parse and loop through passed parameters and create a SQL query
    >>> string.
    >>> 2. Run that string using sp_executesql and store results in a temp
    >>> table
    >>> 3. Loop through the temp table (cursor) and based on the information
    >>> in each record perform a set of operations to create a value for the
    >>> last column in every record

    >>
    >> Why would you need a cursor to do what an UPDATE query could do?
    >>
    >>> 4. select * from #temptable
    >>>
    >>> What I did at this point (so that I can move on with the project) is
    >>> basically sacrifised step 3, changed step 2 to return the result of
    >>> sp_executesql instead of storing it in a temp table, which also
    >>> eliminated step 4.
    >>>
    >>> boy, I wish I could do this project in .net.....sigh

    >> I doubt that would solve this particular issue, whatever it is. When
    >> I have time later, I will try and reproduce it. You could save me
    >> some time by providing a repro script ...
    >>
    >> Bob Barrows
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Aug 4, 2004
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Khurram Hanif

    ADO recordset problem

    Khurram Hanif, May 5, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    471
    Robbe Morris [C# MVP]
    May 5, 2005
  2. Hung Huynh
    Replies:
    8
    Views:
    312
    Bob Barrows
    Sep 24, 2003
  3. Darren Smith
    Replies:
    2
    Views:
    159
    Darren Smith
    Jan 28, 2004
  4. Ola Tuvesson
    Replies:
    4
    Views:
    194
    Ray Costanzo [MVP]
    Sep 29, 2004
  5. Tim Pollard
    Replies:
    8
    Views:
    157
    Ray Costanzo [MVP]
    Dec 3, 2004
Loading...

Share This Page