Please help,



1.



I'm trying to use a SQL2000 Stored Proc with ASP. The proc creates a temp

table with some data /business logic and returns a recordset with a SELECT

statement with data from the temp table. It works fine in the SQL Query

Analyzer. It works fine in ASP as long as I don't try to use the resulting

recrodset in the ASP. If I try to use the recordset in ASP I always get the

message "'Operation is not allowed when the object is closed." I've used

every type of ASP means I've come across to populate the ASP recordset.

Same result every time. They all work as long as I don't try to access the

recordset. If in the proc I create a global temp table (##) instead of a

local temp table (#) and then use a second proc for the SELECT statement I

can work the recordset in ASP as long as I use a second ASP command to

access the recordset. If I try to use an EXEC proc2 in the first proc, and

only one ASP command to get the recordset in ASP it doesn't work. Is this

clear ? What am I doing wrong ? 



2.



Also when I do get it working using two procs and two ASP commands, I can't

use rs.MoveLast. I get the message "'Object doesn't support this property

or method: 'EOF'". Even when I use adOpenDynamic and adLockOptimistic. How

do I get around this ?



3.



Also, I've read somewhere that using Table variable or something is better

than using temp tables in SQL procs. But the books online doesn't explain

it well to me, no examples. And I can't find any info about this in the

dozen books I have. Can any one help me with this ? Explain how it works

and an example or two how to use it to populate an ASP recordset ?



I have a dozen books on ASP and SQL and none of them seem to explain the

above problems. Maybe I've missed the explanations ?



Thanking you in advance for your kind help.

Peter

Problem 1



You need to do SET NO COUNT ON in your sproc, otherwise the first recordset

returned contains the text: "XXX rows affected". Alternatively, you could

use Set objRS = objRS.NextRecordset, but I don't know why you'd want to do

this.



Problem 2



You say you are "using two procs and two commands", and you get an error

when you do rs.MoveLast... A command object does not have a method

.movelast - only a recordset does. Perhaps you could post some code?!?



Problem 3

Table variables are explained in my copy of the SQL Server 2000 Books

Online...are you using SQL Server 2000, or SQL Server 7, or SQL Server

6.5?!?



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "p" <plhere@y...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Cc: <asp_components@p...>; <asp_ecommerce@p...>;

<asp_web_howto@p...>; <beginning_asp>; <rbchandar@y...>;

<proasp_howto@p...>

Sent: Wednesday, September 19, 2001 8:54 AM

Subject: [asp_web_howto] SQL stored proc into ASP recordset problems





: Please help,

:

: 1.

:

: I'm trying to use a SQL2000 Stored Proc with ASP. The proc creates a temp

: table with some data /business logic and returns a recordset with a SELECT

: statement with data from the temp table. It works fine in the SQL Query

: Analyzer. It works fine in ASP as long as I don't try to use the resulting

: recrodset in the ASP. If I try to use the recordset in ASP I always get

the

: message "'Operation is not allowed when the object is closed." I've used

: every type of ASP means I've come across to populate the ASP recordset.

: Same result every time. They all work as long as I don't try to access the

: recordset. If in the proc I create a global temp table (##) instead of a

: local temp table (#) and then use a second proc for the SELECT statement I

: can work the recordset in ASP as long as I use a second ASP command to

: access the recordset. If I try to use an EXEC proc2 in the first proc, and

: only one ASP command to get the recordset in ASP it doesn't work. Is this

: clear ? What am I doing wrong ?

:

: 2.

:

: Also when I do get it working using two procs and two ASP commands, I

can't

: use rs.MoveLast. I get the message "'Object doesn't support this property

: or method: 'EOF'". Even when I use adOpenDynamic and adLockOptimistic. How

: do I get around this ?

:

: 3.

:

: Also, I've read somewhere that using Table variable or something is better

: than using temp tables in SQL procs. But the books online doesn't explain

: it well to me, no examples. And I can't find any info about this in the

: dozen books I have. Can any one help me with this ? Explain how it works

: and an example or two how to use it to populate an ASP recordset ?

:

: I have a dozen books on ASP and SQL and none of them seem to explain the

: above problems. Maybe I've missed the explanations ?

:

: Thanking you in advance for your kind help.

: Peter

DOH!



That would be SET NOCOUNT ON for problem 1



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Sent: Wednesday, September 19, 2001 1:23 PM

Subject: [asp_web_howto] Re: SQL stored proc into ASP recordset problems





: Problem 1

:

: You need to do SET NO COUNT ON in your sproc, otherwise the first

recordset

: returned contains the text: "XXX rows affected". Alternatively, you could

: use Set objRS = objRS.NextRecordset, but I don't know why you'd want to do

: this.