Retrieving recordsets from SQL Server 2000 using VB ADODB 1

[ppynrde]

I am accessing SQL server 2000 from VB using ADODB but I am having issues returning a recordset from a stored procedure that firstly updates a temp table and then runs a select on it.

I can retrieve recordsets if I only run select statements, but not if I run Updates or inserts prior to the select. The error from VB states that the object, i.e. the recordset is closed and therefore cannot be accessed.

Any help with this would be much appreciated as it is beginning to drive me nuts
Thanks

Rich

 

[mukund]

Hi Rich,
Do some changes to your stored procedure.
You have to put a statement
SET NOCOUNT ON
before any insert/updates into the Temperory table.
Just before the final SELECT Statement add a
SET NOCOUNT OFF
statement and see whether the Recordset gets returned to the VB application.

Hope this helps
Mukund.

 

[ppynrde]

Thanks loads for that works like a dream!!

Now I come to think about it, it is pretty straight forward should have worked that one out ages ago. But it is always the little things that cause the biggest irritations

Thanks again

Rich

 

[ppynrde]

Hi,

An extension to this problem that I have just come across. Within the stored procedure I am calling other stored procs which return an output parameter to the calling stored proc. These parameters are then inserted into a temp table, and then a select from the temp table is performed. This is the dataset I require. Again, VB just shows an empty recordset.
I have tried enclosing all of the code apart from the select in the 'NOCOUNT' syntax suggested before, but this time it seems to have no effect.

Any thoughts would again be greatly appreciated.

Thanks