469,290 Members | 1,880 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

Recordset problem

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
Jul 19 '05 #1
8 4500
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" <di******@hotmail.com> wrote in message
news:eR*************@TK2MSFTNGP09.phx.gbl...
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

Jul 19 '05 #2
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"
Jul 19 '05 #3
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

Jul 19 '05 #4
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.
Jul 19 '05 #5
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
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.

Jul 19 '05 #6
What I meant to say is "I guess I am NOT a big SQL expert...."

"Dima Protchenko" <di******@hotmail.com> wrote in message
news:eZ****************@TK2MSFTNGP12.phx.gbl...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
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.


Jul 19 '05 #7
> 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.)
Jul 19 '05 #8
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
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.
Jul 19 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by gary artim | last post: by
4 posts views Thread by Gerry Abbott | last post: by
5 posts views Thread by zMisc | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.