Recordset problem

[Dima Protchenko]

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

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

 

[Bob Barrows [MVP]]

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

 

[Dima Protchenko]

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

 

[Bob Barrows [MVP]]

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

 

[Dima Protchenko]

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

 

[Dima Protchenko]

What I meant to say is "I guess I am NOT a big SQL expert...."

 

[Aaron [SQL Server MVP]]

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.

 

[Bob Barrows [MVP]]

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