«

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

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

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"

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 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.

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.
>
>

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.
> >
> >
>
>

> 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.)

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.