Returning RecordSet to ASP is empty

[Eli Sidwell]

Trying to return a Recordset to an ASP and the Recordset is empty.
The StorredProc works in the query analyzer and it even works from a
quick VB app that I wrote to test it.

The storedproc that I am using is fairly complex (creates some
temporary tables and populates them with 'Insert Into Select ...', but
the during testing the only Select statements that return visible rows
is the final one that returns the finished table with an 'Order By
....'.

I am trying to figure out if I am receiving multiple recordsets or if
I am out of environment space. Do the 'Insert Into Select ..'
statements return empty or null recordsets ?

Thanks

Sid ..

[Bob Barrows [MVP]]

Eli Sidwell wrote:

Trying to return a Recordset to an ASP and the Recordset is empty.
The StorredProc works in the query analyzer and it even works from a
quick VB app that I wrote to test it.

The storedproc that I am using is fairly complex (creates some
temporary tables and populates them with 'Insert Into Select ...', but
the during testing the only Select statements that return visible rows
is the final one that returns the finished table with an 'Order By
...'.

I am trying to figure out if I am receiving multiple recordsets or if
I am out of environment space. Do the 'Insert Into Select ..'
statements return empty or null recordsets ?

Yes. The informational messages (x rows were affected ...) you see in QA are
returned as extra resultsets. You should make a practice of including the
line "SET NOCOUNT ON" at the beginning of all your procedures to suppress
these messages.

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

[Al Reid]

If you are using SQL Server, put a SET NOCOUNT ON at the beginning of the SP to stop SQL Server from returning empty recordsets for
each non row-returning DML statement. If you cannot do that, user NextRecordset to move to the next one until you get to the one
that is not empty.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

"Eli Sidwell" <si******@alexian.net> wrote in message news:ef**************************@posting.google.c om...

Trying to return a Recordset to an ASP and the Recordset is empty.
The StorredProc works in the query analyzer and it even works from a
quick VB app that I wrote to test it.

The storedproc that I am using is fairly complex (creates some
temporary tables and populates them with 'Insert Into Select ...', but
the during testing the only Select statements that return visible rows
is the final one that returns the finished table with an 'Order By
...'.

I am trying to figure out if I am receiving multiple recordsets or if
I am out of environment space. Do the 'Insert Into Select ..'
statements return empty or null recordsets ?

Thanks

Sid ..

[Eli Sidwell]

It worked, Thanks for the follow up.

Sid ..

"Al Reid" <ar*****@reidDASHhome.com> wrote in message news:<em*************@TK2MSFTNGP12.phx.gbl>...

If you are using SQL Server, put a SET NOCOUNT ON at the beginning of the SP to stop SQL Server from returning empty recordsets for
each non row-returning DML statement. If you cannot do that, user NextRecordset to move to the next one until you get to the one
that is not empty.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

"Eli Sidwell" <si******@alexian.net> wrote in message news:ef**************************@posting.google.c om...

Trying to return a Recordset to an ASP and the Recordset is empty.
The StorredProc works in the query analyzer and it even works from a
quick VB app that I wrote to test it.

The storedproc that I am using is fairly complex (creates some
temporary tables and populates them with 'Insert Into Select ...', but
the during testing the only Select statements that return visible rows
is the final one that returns the finished table with an 'Order By
...'.

I am trying to figure out if I am receiving multiple recordsets or if
I am out of environment space. Do the 'Insert Into Select ..'
statements return empty or null recordsets ?

Thanks

Sid ..

[Eli Sidwell]

In addition, the Stored Proc returns some values like recordCounts and
other stuff and this is done by a second select statement, but there
appears to be no other recordsets. i.e.

* sp_GetStuff****
Select * from tblSuff Where ...
Select @Count, @OtherSuff

*********

+ ASP +++
RcdSet.Open "Stored_Proc ..."
Do Until RcdSet.EOF
response.write RcdSet(1)
Loop

Set RcdSet = RcdSet.NextRecordSet()
iCount = RcdSet(0)
Response.write iCount

+++++++++

I keep Getting a 'Syntax Error' or 'object required'. How do I get
values returned by the second Select if the are not in a table format
?

Thanks

Sid ..


si******@alexian.net (Eli Sidwell) wrote in message news:<ef*************************@posting.google.c om>...

It worked, Thanks for the follow up.

Sid ..

"Al Reid" <ar*****@reidDASHhome.com> wrote in message news:<em*************@TK2MSFTNGP12.phx.gbl>...

If you are using SQL Server, put a SET NOCOUNT ON at the beginning of the SP to stop SQL Server from returning empty recordsets for
each non row-returning DML statement. If you cannot do that, user NextRecordset to move to the next one until you get to the one
that is not empty.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

"Eli Sidwell" <si******@alexian.net> wrote in message news:ef**************************@posting.google.c om...

Trying to return a Recordset to an ASP and the Recordset is empty.
The StorredProc works in the query analyzer and it even works from a
quick VB app that I wrote to test it.

The storedproc that I am using is fairly complex (creates some
temporary tables and populates them with 'Insert Into Select ...', but
the during testing the only Select statements that return visible rows
is the final one that returns the finished table with an 'Order By
...'.

I am trying to figure out if I am receiving multiple recordsets or if
I am out of environment space. Do the 'Insert Into Select ..'
statements return empty or null recordsets ?

Thanks

Sid ..