HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value 
Author Message
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

SET NOCOUNT ON is scoped within the SP. It has no effect after the SP ends
so there's no need to reset it afterwards.

This problem is "by design". We've discussed this before so check the
archives, but remember that queries return resultsets. A resultset with no
rowset is exposed as Recordset.State = adStateClosed. Any action query
generates a resultset returning the rows affected value. If you don't want
this information, use the SET NOCOUNT ON in the sp. If you do want this
information, simply step to the next resultset

    set rs = rs.NextRecordset

until rs is nothing--which means there are no more resultsets.


William (Bill) Vaughn
Author, Trainer, Mentor
Microsoft Pacwest Regional Director
Beta V Corporation
"ADO and ADO.NET Examples and Best Practices for VB Programmers--2nd
Edition" (ISBN: 1-893115-68-2)
"ADO.NET Examples and Best Practices for C# Programmers" (ISBN:

Please reply only to the newsgroup so that others can benefit. When posting,
This posting is provided "AS IS" with no warranties, and confers no rights.


Add SET NOCOUNT ON at the beginning of SP and SET NOCOUNT
OFF at the end. See if it helps. If not, then after you
open recordset with ADO, try to use NextRecordset to see
if you get multiple one. Probably OUTPUT value comes with
last recordset.


>-----Original Message-----

>thanks both of you for your reply

>I do get an result back in my ado thing from the stored

>but still I get the following errror

>"3704 Operation is not allowed when the object is closed."

>any ideas

>closing the recordset did not change anything



>> Hi, all out there...

>> I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5

>> I created an Stored procedure that inserts 1 new record
and should return
>> the value of the identifier of the created record
>> with profiler I could see that the stored procedure
returns the value

>> but in my vb applic  i can not get any value...

>> I used a paper from Microsoft as example it has no

number just a title:
>> "Using return code and output parameters for stored

>> 1. the sp does not get the output value...
>> 2. due to this the recordset set is empty and state is


>> THANKS ......

>> Wim Mintiens

>> the stored procedure :


- Show quoted text -

>> CREATE    procedure dbo.sp_addMsg

>> AS

>>   (indic_nr,
>>    in_out,
>>           subject,
>>           mfrom,
>>           mto,
>>           cc,
>>           bcc,
>>           priority,
>>           importance,
>>           status_id,
>>           date_received,
>>           date_transmission,
>>    doc_class)

indic_nr =

>> END


>> my code:

>> Public Function SaveMsgInDB(ByVal msg As CDO.Message,
ByVal iDocType As
>> Integer, ByVal way As String) As Integer

>>     Dim cn As ADODB.Connection
>>     Dim rs As ADODB.Recordset
>>     Dim cmd As ADODB.Command
>>     Dim fldloop As ADODB.Field

>>     Dim pmReturn As ADODB.Parameter
>>     Dim pmMsgId As ADODB.Parameter
>>     Dim pmDocClass As ADODB.Parameter
>>     Dim pmInOut As ADODB.Parameter
>>     Dim pmSubject As ADODB.Parameter
>>     Dim pmMFrom As ADODB.Parameter
>>     Dim pmMto As ADODB.Parameter
>>     Dim pmCC As ADODB.Parameter
>>     Dim pmBCC As ADODB.Parameter
>>     Dim pmPriority As ADODB.Parameter
>>     Dim pmImportance As ADODB.Parameter
>>     Dim pmStatusId  As ADODB.Parameter
>>     Dim pmDateRcv{ w o 4tj"?r2Dk *  As
>>     Dim pmDateTrans As ADODB.Parameter

>>     Dim DBUsrName As String
>>     Dim DBPWD As String
>>     Dim DBSrvName As String
>>     Dim DBName As String
>>     Dim iLastIndicNr As Integer

>>     Set cn = New Connection
>>     Set cmd = New Command

Sun, 30 Jan 2005 03:01:27 GMT  
 [ 1 post ] 

 Relevant Pages 

1. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

2. SQL Server 2000 stored procedure not returning rows

3. SQL Server 2000 stored procedure not returning rows

4. doing updating from grid to sql server with stored procedure sql 2000

5. MS ADO / Sybase SQL Anywhere ASA 7 Stored Procedure Return Value Problem

6. SQL Stored Procedure Return Value

7. Getting a return value from a SQL Server stored procedure through VB

8. Return value from stored procedure in SQL 6.5 to VB4 to a 16 bits client

9. Return a value from stored procedure in SQL 6.5 to VB4 to a 16 bits client

10. Getting a return value from a stored procedure in SQL Server

11. Return Value - Calling SQL Stored Procedure

12. Returning value from SQL Server Store Procedure


Powered by phpBB® Forum Software