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.
hth
--
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:
1-590590-12-0)
www.betav.com
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.
________________________________
Hi,
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.
Val
Quote:
>-----Original Message-----
>OJ, VAL,
>thanks both of you for your reply
>I do get an result back in my ado thing from the stored
procedure
>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
>regards,
>Wim
message
>> 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
>a
>> 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:
Quote:
>> "Using return code and output parameters for stored
procedures"
>> 1. the sp does not get the output value...
>> 2. due to this the recordset set is empty and state is
closed
>> CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
>> THANKS ......
>> Wim Mintiens
>> the stored procedure :
===========================================================
=
Quote:
>> CREATE procedure dbo.sp_addMsg
>> AS
>> BEGIN
>> INSERT MESSAGE
>> (indic_nr,
>> in_out,
>> subject,
>> mfrom,
>> mto,
>> cc,
>> bcc,
>> priority,
>> importance,
>> status_id,
>> date_received,
>> date_transmission,
>> doc_class)
>> VALUES
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
ADODB.Parameter
>> 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
