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

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:
"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 :
============================================================
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














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 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

        Call EventLog("saveIndb", "init")
    DBUsrName = GetRegKey(DBUSRID)
    DBPWD = ""                             ' GetRegKey (DBPWD)
    DBSrvName = GetRegKey(DBSYS)
    DBName = GetRegKey(DBDBNAME)

        Call EventLog("saveindb", "connec", , DBUsrName & vbTab & DBPWD &
vbTab & DBSrvName & vbTab & DBName)
    ' Set connection properties.
    cn.ConnectionTimeout = 25                                       ' Set
the time out.
    cn.Provider = "sqloledb"                                        '
Specify the OLE DB provider.
    cn.Properties("Data Source").Value = DBSrvName                  ' Set
SQLOLEDB connection properties.
    cn.Properties("Initial Catalog").Value = DBName                 ' Set
SQLOLEDB connection properties.
    cn.Properties("User ID").Value = DBUsrName
    cn.Properties("Password").Value = DBPWD
    cn.CursorLocation = adUseServer
        Call EventLog("saveindb", "open conn")
    ' Open the database.
    cn.Open

    Set cmd.ActiveConnection = cn
    cmd.CommandText = "sp_AddMsg"
    cmd.CommandType = adCmdStoredProc

        Call EventLog("saveinddb", "1")

    Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger, adParamOutput)
    cmd.Parameters.Append pmMsgId
        Call EventLog("saveinddb", "3")

    Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput, 3)
    cmd.Parameters.Append pmInOut
        Call EventLog("saveinddb", "4")

    Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
adParamInput, 255)
    cmd.Parameters.Append pmSubject
        Call EventLog("saveinddb", "5")

    Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar, adParamInput,
255)
    cmd.Parameters.Append pmMFrom
        Call EventLog("saveinddb", "6")

    Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput, 255)
    cmd.Parameters.Append pmMto
        Call EventLog("saveinddb", "7")

    Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput, 255)
    cmd.Parameters.Append pmCC

    Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput, 255)
    cmd.Parameters.Append pmBCC

    Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
adParamInput)
    cmd.Parameters.Append pmPriority

    Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
adParamInput)
    cmd.Parameters.Append pmImportance

    Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
adParamInput)
    cmd.Parameters.Append pmDateRcv

    Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
adParamInput)
    cmd.Parameters.Append pmDateTrans

        Call EventLog("saveinddb", "a")
    Select Case way
        Case "IN"
            pmInOut.Value = INCOMMING
            pmDateRcv.Value = msg.ReceivedTime
            pmDateTrans = Null
        Case "OUT"
            pmInOut.Value = OUTGOING
            pmDateTrans.Value = msg.SentOn
            pmDateRcv.Value = Null
    End Select

        Call EventLog("saveinddb", "b")
    pmMsgId.Value = 0
    pmSubject.Value = msg.Subject
    pmMFrom.Value = msg.From
    pmMto.Value = msg.To
    pmCC.Value = msg.CC
    pmBCC.Value = msg.BCC
    pmPriority.Value = msg.Fields(cdoPriority)
    pmImportance.Value = msg.Fields(cdoImportance)
        Call EventLog("saveinddb", "c")

    Set rs = cmd.execute
        Call EventLog("saveindb", "c2", , CStr(rs.State))
        Call EventLog("saveindb", "cmd 0  " & CStr(cmd(0)) & vbTab & "cmd 1
" & CStr(cmd(1)))

    Dim i As Integer
    While Not rs.EOF
        For Each fldloop In rs.Fields
            Call EventLog("Saveindb", CStr(rs.Fields(i)))
            i = i + 1
        Next fldloop
        i = 0
        rs.MoveNext
    Wend

    SaveMsgInDB = 1

    Call EventLog("saveindb", CStr(cmd(0)) & vbTab & CStr(cmd(1)))

    rs.Close
        Call EventLog("saveinddb", "d")
    cn.Close

End Function
============================================================================



Fri, 30 Jul 2004 06:35:15 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
wim,

here is the correction for your stored procedure.

btw, please do not use [sp] as a prefix for your stored procedure. this will
hinder your performance.

--notice spname changed

AS
--added this
set nocount on

BEGIN







--changed this

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














END

--
-oj
http://rac4sql.home.attbi.com


Quote:
> 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:
> "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 :
> ============================================================
> 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














> END

===========================================================================

- Show quoted text -

Quote:

> 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 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

>         Call EventLog("saveIndb", "init")
>     DBUsrName = GetRegKey(DBUSRID)
>     DBPWD = ""                             ' GetRegKey (DBPWD)
>     DBSrvName = GetRegKey(DBSYS)
>     DBName = GetRegKey(DBDBNAME)

>         Call EventLog("saveindb", "connec", , DBUsrName & vbTab & DBPWD &
> vbTab & DBSrvName & vbTab & DBName)
>     ' Set connection properties.
>     cn.ConnectionTimeout = 25                                       ' Set
> the time out.
>     cn.Provider = "sqloledb"                                        '
> Specify the OLE DB provider.
>     cn.Properties("Data Source").Value = DBSrvName                  ' Set
> SQLOLEDB connection properties.
>     cn.Properties("Initial Catalog").Value = DBName                 ' Set
> SQLOLEDB connection properties.
>     cn.Properties("User ID").Value = DBUsrName
>     cn.Properties("Password").Value = DBPWD
>     cn.CursorLocation = adUseServer
>         Call EventLog("saveindb", "open conn")
>     ' Open the database.
>     cn.Open

>     Set cmd.ActiveConnection = cn
>     cmd.CommandText = "sp_AddMsg"
>     cmd.CommandType = adCmdStoredProc

>         Call EventLog("saveinddb", "1")

>     Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger, adParamOutput)
>     cmd.Parameters.Append pmMsgId
>         Call EventLog("saveinddb", "3")

>     Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput, 3)
>     cmd.Parameters.Append pmInOut
>         Call EventLog("saveinddb", "4")

>     Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> adParamInput, 255)
>     cmd.Parameters.Append pmSubject
>         Call EventLog("saveinddb", "5")

>     Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar, adParamInput,
> 255)
>     cmd.Parameters.Append pmMFrom
>         Call EventLog("saveinddb", "6")

>     Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput, 255)
>     cmd.Parameters.Append pmMto
>         Call EventLog("saveinddb", "7")

>     Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput, 255)
>     cmd.Parameters.Append pmCC

>     Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput,
255)
>     cmd.Parameters.Append pmBCC

>     Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> adParamInput)
>     cmd.Parameters.Append pmPriority

>     Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
> adParamInput)
>     cmd.Parameters.Append pmImportance

>     Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> adParamInput)
>     cmd.Parameters.Append pmDateRcv

>     Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
> adParamInput)
>     cmd.Parameters.Append pmDateTrans

>         Call EventLog("saveinddb", "a")
>     Select Case way
>         Case "IN"
>             pmInOut.Value = INCOMMING
>             pmDateRcv.Value = msg.ReceivedTime
>             pmDateTrans = Null
>         Case "OUT"
>             pmInOut.Value = OUTGOING
>             pmDateTrans.Value = msg.SentOn
>             pmDateRcv.Value = Null
>     End Select

>         Call EventLog("saveinddb", "b")
>     pmMsgId.Value = 0
>     pmSubject.Value = msg.Subject
>     pmMFrom.Value = msg.From
>     pmMto.Value = msg.To
>     pmCC.Value = msg.CC
>     pmBCC.Value = msg.BCC
>     pmPriority.Value = msg.Fields(cdoPriority)
>     pmImportance.Value = msg.Fields(cdoImportance)
>         Call EventLog("saveinddb", "c")

>     Set rs = cmd.execute
>         Call EventLog("saveindb", "c2", , CStr(rs.State))
>         Call EventLog("saveindb", "cmd 0  " & CStr(cmd(0)) & vbTab & "cmd
1
> " & CStr(cmd(1)))

>     Dim i As Integer
>     While Not rs.EOF
>         For Each fldloop In rs.Fields
>             Call EventLog("Saveindb", CStr(rs.Fields(i)))
>             i = i + 1
>         Next fldloop
>         i = 0
>         rs.MoveNext
>     Wend

>     SaveMsgInDB = 1

>     Call EventLog("saveindb", CStr(cmd(0)) & vbTab & CStr(cmd(1)))

>     rs.Close
>         Call EventLog("saveinddb", "d")
>     cn.Close

> End Function

============================================================================

- Show quoted text -



Fri, 30 Jul 2004 06:58:40 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
Hi,

Try to close recordset before reading output parameter.

Val

Quote:
>-----Original 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:
>"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 :
>==========================================================
==
>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
















- Show quoted text -

Quote:

>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 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

>        Call EventLog("saveIndb", "init")
>    DBUsrName = GetRegKey(DBUSRID)
>    DBPWD = ""                             ' GetRegKey
(DBPWD)
>    DBSrvName = GetRegKey(DBSYS)
>    DBName = GetRegKey(DBDBNAME)

>        Call EventLog("saveindb", "connec", , DBUsrName &
vbTab & DBPWD &
>vbTab & DBSrvName & vbTab & DBName)
>    ' Set connection properties.
>    cn.ConnectionTimeout = 25          

{ w   O""S"   ??                            ' Set
Quote:
>the time out.
>    cn.Provider

= "sqloledb"                                        '
Quote:
>Specify the OLE DB provider.
>    cn.Properties("Data Source").Value =

DBSrvName                  ' Set

- Show quoted text -

Quote:
>SQLOLEDB connection properties.
>    cn.Properties("Initia



Fri, 30 Jul 2004 09:50:00 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
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


Quote:
> 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:
> "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 :
> ============================================================
> 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














> END

===========================================================================

- Show quoted text -

Quote:

> 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 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

>         Call EventLog("saveIndb", "init")
>     DBUsrName = GetRegKey(DBUSRID)
>     DBPWD = ""                             ' GetRegKey (DBPWD)
>     DBSrvName = GetRegKey(DBSYS)
>     DBName = GetRegKey(DBDBNAME)

>         Call EventLog("saveindb", "connec", , DBUsrName & vbTab & DBPWD &
> vbTab & DBSrvName & vbTab & DBName)
>     ' Set connection properties.
>     cn.ConnectionTimeout = 25                                       ' Set
> the time out.
>     cn.Provider = "sqloledb"                                        '
> Specify the OLE DB provider.
>     cn.Properties("Data Source").Value = DBSrvName                  ' Set
> SQLOLEDB connection properties.
>     cn.Properties("Initial Catalog").Value = DBName                 ' Set
> SQLOLEDB connection properties.
>     cn.Properties("User ID").Value = DBUsrName
>     cn.Properties("Password").Value = DBPWD
>     cn.CursorLocation = adUseServer
>         Call EventLog("saveindb", "open conn")
>     ' Open the database.
>     cn.Open

>     Set cmd.ActiveConnection = cn
>     cmd.CommandText = "sp_AddMsg"
>     cmd.CommandType = adCmdStoredProc

>         Call EventLog("saveinddb", "1")

>     Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger, adParamOutput)
>     cmd.Parameters.Append pmMsgId
>         Call EventLog("saveinddb", "3")

>     Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput, 3)
>     cmd.Parameters.Append pmInOut
>         Call EventLog("saveinddb", "4")

>     Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> adParamInput, 255)
>     cmd.Parameters.Append pmSubject
>         Call EventLog("saveinddb", "5")

>     Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar, adParamInput,
> 255)
>     cmd.Parameters.Append pmMFrom
>         Call EventLog("saveinddb", "6")

>     Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput, 255)
>     cmd.Parameters.Append pmMto
>         Call EventLog("saveinddb", "7")

>     Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput, 255)
>     cmd.Parameters.Append pmCC

>     Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput,
255)
>     cmd.Parameters.Append pmBCC

>     Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> adParamInput)
>     cmd.Parameters.Append pmPriority

>     Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
> adParamInput)
>     cmd.Parameters.Append pmImportance

>     Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> adParamInput)
>     cmd.Parameters.Append pmDateRcv

>     Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
> adParamInput)
>     cmd.Parameters.Append pmDateTrans

>         Call EventLog("saveinddb", "a")
>     Select Case way
>         Case "IN"
>             pmInOut.Value = INCOMMING
>             pmDateRcv.Value = msg.ReceivedTime
>             pmDateTrans = Null
>         Case "OUT"
>             pmInOut.Value = OUTGOING
>             pmDateTrans.Value = msg.SentOn
>             pmDateRcv.Value = Null
>     End Select

>         Call EventLog("saveinddb", "b")
>     pmMsgId.Value = 0
>     pmSubject.Value = msg.Subject
>     pmMFrom.Value = msg.From
>     pmMto.Value = msg.To
>     pmCC.Value = msg.CC
>     pmBCC.Value = msg.BCC
>     pmPriority.Value = msg.Fields(cdoPriority)
>     pmImportance.Value = msg.Fields(cdoImportance)
>         Call EventLog("saveinddb", "c")

>     Set rs = cmd.execute
>         Call EventLog("saveindb", "c2", , CStr(rs.State))
>         Call EventLog("saveindb", "cmd 0  " & CStr(cmd(0)) & vbTab & "cmd
1
> " & CStr(cmd(1)))

>     Dim i As Integer
>     While Not rs.EOF
>         For Each fldloop In rs.Fields
>             Call EventLog("Saveindb", CStr(rs.Fields(i)))
>             i = i + 1
>         Next fldloop
>         i = 0
>         rs.MoveNext
>     Wend

>     SaveMsgInDB = 1

>     Call EventLog("saveindb", CStr(cmd(0)) & vbTab & CStr(cmd(1)))

>     rs.Close
>         Call EventLog("saveinddb", "d")
>     cn.Close

> End Function

============================================================================

- Show quoted text -



Fri, 30 Jul 2004 15:06:40 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
HI
I think stored procedure is returning multiple results .
There are two options where you can solve this
1)Use SET NOCOUNT ON before each statement.
2) Or use NextRecordset in your ADO code.
First option is preferred.
also, refer to the link:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q235340

Harinatha Reddy Gorla (MCSD)
        Software Engineer
Smart Software Technologies,
Hyderabad, India

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Fri, 30 Jul 2004 16:28:41 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
HI
A small correction in my previous post:
I think stored procedure is returning multiple resultsets .
There are two options where you can solve this
1)Use SET NOCOUNT ON at the beginning in SP.
2) Or use NextRecordset in your ADO code.
First option is preferred.
also, refer to the link:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q235340

Harinatha Reddy Gorla (MCSD)
        Software Engineer
Smart Software Technologies,
Hyderabad, India

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Fri, 30 Jul 2004 16:56:43 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
Harinatha,

sorry but that did not change it, i stll get the error
I even adapted the code to what was specified in Q235340

still the same error, the record set stays closed....

regards & thanks

Wim



Quote:
> HI
> I think stored procedure is returning multiple results .
> There are two options where you can solve this
> 1)Use SET NOCOUNT ON before each statement.
> 2) Or use NextRecordset in your ADO code.
> First option is preferred.
> also, refer to the link:
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q235340

> Harinatha Reddy Gorla (MCSD)
> Software Engineer
> Smart Software Technologies,
> Hyderabad, India

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Fri, 30 Jul 2004 19:01:21 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
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 :

===========================================================
=

- Show quoted text -

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



Fri, 30 Jul 2004 20:37:46 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
As far as receiving output parameters and return values (they are different)
from a stored procedure to an ADO command object, here's some additional
information.

SQL Server sends the values in the last data packet. This means a server
side recordset must be closed before receiving the parameter values.  A
clientside recordset does not have to be closed.

The return value parameter should be the first one defined in the ado
command.

As for the error  "3704 Operation is not allowed when the object is closed."

Your stored procedure does not return any records.  No where in your
procedure is there a Select statement.  Your rs variable does get
instantiated by the cmd.Execute statement but it's state is closed.  Since
you then try to access the EOF property you receive this error.   You should
add a check for possible scenarios, something like.

Set rs = cmd.Execute

If Not rs is Nothing then
    if rs.state = adStateOpen then
        Do
            ' ........ record processing.
            rs.movenext
        Loop Until rs.EOF
    else
         'Recordset is closed.
    end if
Endif

HTH
Bruce


Quote:
> 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



> > 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:
> > "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 :
> > ============================================================
> > 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















> > END

===========================================================================

- Show quoted text -

Quote:

> > 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 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

> >         Call EventLog("saveIndb", "init")
> >     DBUsrName = GetRegKey(DBUSRID)
> >     DBPWD = ""                             ' GetRegKey (DBPWD)
> >     DBSrvName = GetRegKey(DBSYS)
> >     DBName = GetRegKey(DBDBNAME)

> >         Call EventLog("saveindb", "connec", , DBUsrName & vbTab & DBPWD
&
> > vbTab & DBSrvName & vbTab & DBName)
> >     ' Set connection properties.
> >     cn.ConnectionTimeout = 25                                       '
Set
> > the time out.
> >     cn.Provider = "sqloledb"                                        '
> > Specify the OLE DB provider.
> >     cn.Properties("Data Source").Value = DBSrvName                  '
Set
> > SQLOLEDB connection properties.
> >     cn.Properties("Initial Catalog").Value = DBName                 '
Set
> > SQLOLEDB connection properties.
> >     cn.Properties("User ID").Value = DBUsrName
> >     cn.Properties("Password").Value = DBPWD
> >     cn.CursorLocation = adUseServer
> >         Call EventLog("saveindb", "open conn")
> >     ' Open the database.
> >     cn.Open

> >     Set cmd.ActiveConnection = cn
> >     cmd.CommandText = "sp_AddMsg"
> >     cmd.CommandType = adCmdStoredProc

> >         Call EventLog("saveinddb", "1")

> >     Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger,
adParamOutput)
> >     cmd.Parameters.Append pmMsgId
> >         Call EventLog("saveinddb", "3")

> >     Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput, 3)
> >     cmd.Parameters.Append pmInOut
> >         Call EventLog("saveinddb", "4")

> >     Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> > adParamInput, 255)
> >     cmd.Parameters.Append pmSubject
> >         Call EventLog("saveinddb", "5")

> >     Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar,
adParamInput,
> > 255)
> >     cmd.Parameters.Append pmMFrom
> >         Call EventLog("saveinddb", "6")

> >     Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput,
255)
> >     cmd.Parameters.Append pmMto
> >         Call EventLog("saveinddb", "7")

> >     Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput,
255)
> >     cmd.Parameters.Append pmCC

> >     Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput,
> 255)
> >     cmd.Parameters.Append pmBCC

> >     Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> > adParamInput)
> >     cmd.Parameters.Append pmPriority

> >     Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
> > adParamInput)
> >     cmd.Parameters.Append pmImportance

> >     Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> > adParamInput)
> >     cmd.Parameters.Append pmDateRcv

> >     Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
> > adParamInput)
> >     cmd.Parameters.Append pmDateTrans

> >         Call EventLog("saveinddb", "a")
> >     Select Case way
> >         Case "IN"
> >             pmInOut.Value = INCOMMING
> >             pmDateRcv.Value = msg.ReceivedTime
> >             pmDateTrans = Null
> >         Case "OUT"
> >             pmInOut.Value = OUTGOING
> >             pmDateTrans.Value = msg.SentOn
> >             pmDateRcv.Value = Null
> >     End Select

> >         Call EventLog("saveinddb", "b")
> >     pmMsgId.Value = 0
> >     pmSubject.Value = msg.Subject
> >     pmMFrom.Value = msg.From
> >     pmMto.Value = msg.To
> >     pmCC.Value = msg.CC
> >     pmBCC.Value = msg.BCC
> >     pmPriority.Value = msg.Fields(cdoPriority)
> >     pmImportance.Value = msg.Fields(cdoImportance)
> >         Call EventLog("saveinddb", "c")

> >     Set rs = cmd.execute
> >         Call EventLog("saveindb", "c2", , CStr(rs.State))
> >         Call EventLog("saveindb", "cmd 0  " & CStr(cmd(0)) & vbTab &
"cmd
> 1
> > " & CStr(cmd(1)))

> >     Dim i As Integer
> >     While Not rs.EOF
> >         For Each fldloop In rs.Fields
> >             Call EventLog("Saveindb", CStr(rs.Fields(i)))
> >             i = i + 1
> >         Next fldloop
> >         i = 0
> >         rs.MoveNext
> >     Wend

> >     SaveMsgInDB = 1

> >     Call EventLog("saveindb", CStr(cmd(0)) & vbTab & CStr(cmd(1)))

> >     rs.Close
> >         Call EventLog("saveinddb", "d")
> >     cn.Close

> > End Function

============================================================================

- Show quoted text -



Sat, 31 Jul 2004 09:27:27 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
Bruce,

thanks for the clarification of the "problem", it's clear now what i have to
do

Many thanks to your all for helping me ....

Regards,
Wim Mintiens
Belgium


Quote:
> As far as receiving output parameters and return values (they are
different)
> from a stored procedure to an ADO command object, here's some additional
> information.

> SQL Server sends the values in the last data packet. This means a server
> side recordset must be closed before receiving the parameter values.  A
> clientside recordset does not have to be closed.

> The return value parameter should be the first one defined in the ado
> command.

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

> Your stored procedure does not return any records.  No where in your
> procedure is there a Select statement.  Your rs variable does get
> instantiated by the cmd.Execute statement but it's state is closed.  Since
> you then try to access the EOF property you receive this error.   You
should
> add a check for possible scenarios, something like.

> Set rs = cmd.Execute

> If Not rs is Nothing then
>     if rs.state = adStateOpen then
>         Do
>             ' ........ record processing.
>             rs.movenext
>         Loop Until rs.EOF
>     else
>          'Recordset is closed.
>     end if
> Endif

> HTH
> Bruce



> > 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



> > > 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:
> > > "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 :
> > > ============================================================
> > > 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















> > > END

===========================================================================

- Show quoted text -

Quote:

> > > 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 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

> > >         Call EventLog("saveIndb", "init")
> > >     DBUsrName = GetRegKey(DBUSRID)
> > >     DBPWD = ""                             ' GetRegKey (DBPWD)
> > >     DBSrvName = GetRegKey(DBSYS)
> > >     DBName = GetRegKey(DBDBNAME)

> > >         Call EventLog("saveindb", "connec", , DBUsrName & vbTab &
DBPWD
> &
> > > vbTab & DBSrvName & vbTab & DBName)
> > >     ' Set connection properties.
> > >     cn.ConnectionTimeout = 25                                       '
> Set
> > > the time out.
> > >     cn.Provider = "sqloledb"                                        '
> > > Specify the OLE DB provider.
> > >     cn.Properties("Data Source").Value = DBSrvName                  '
> Set
> > > SQLOLEDB connection properties.
> > >     cn.Properties("Initial Catalog").Value = DBName                 '
> Set
> > > SQLOLEDB connection properties.
> > >     cn.Properties("User ID").Value = DBUsrName
> > >     cn.Properties("Password").Value = DBPWD
> > >     cn.CursorLocation = adUseServer
> > >         Call EventLog("saveindb", "open conn")
> > >     ' Open the database.
> > >     cn.Open

> > >     Set cmd.ActiveConnection = cn
> > >     cmd.CommandText = "sp_AddMsg"
> > >     cmd.CommandType = adCmdStoredProc

> > >         Call EventLog("saveinddb", "1")

> > >     Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger,
> adParamOutput)
> > >     cmd.Parameters.Append pmMsgId
> > >         Call EventLog("saveinddb", "3")

> > >     Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput,
3)
> > >     cmd.Parameters.Append pmInOut
> > >         Call EventLog("saveinddb", "4")

> > >     Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> > > adParamInput, 255)
> > >     cmd.Parameters.Append pmSubject
> > >         Call EventLog("saveinddb", "5")

> > >     Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar,
> adParamInput,
> > > 255)
> > >     cmd.Parameters.Append pmMFrom
> > >         Call EventLog("saveinddb", "6")

> > >     Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput,
> 255)
> > >     cmd.Parameters.Append pmMto
> > >         Call EventLog("saveinddb", "7")

> > >     Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput,
> 255)
> > >     cmd.Parameters.Append pmCC

> > >     Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput,
> > 255)
> > >     cmd.Parameters.Append pmBCC

> > >     Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> > > adParamInput)
> > >     cmd.Parameters.Append pmPriority

> > >     Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
> > > adParamInput)
> > >     cmd.Parameters.Append pmImportance

> > >     Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> > > adParamInput)
> > >     cmd.Parameters.Append pmDateRcv

> > >     Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
> > > adParamInput)
> > >     cmd.Parameters.Append pmDateTrans

> > >         Call EventLog("saveinddb", "a")
> > >     Select Case way
> > >         Case "IN"
> > >             pmInOut.Value = INCOMMING
> > >             pmDateRcv.Value = msg.ReceivedTime
> > >             pmDateTrans = Null
> > >         Case "OUT"
> > >             pmInOut.Value = OUTGOING
> > >             pmDateTrans.Value = msg.SentOn
> > >             pmDateRcv.Value = Null
> > >     End Select

> > >         Call EventLog("saveinddb", "b")
> > >     pmMsgId.Value = 0
> > >     pmSubject.Value = msg.Subject
> > >     pmMFrom.Value = msg.From
> > >     pmMto.Value = msg.To
> > >     pmCC.Value = msg.CC
> > >     pmBCC.Value = msg.BCC
> > >     pmPriority.Value = msg.Fields(cdoPriority)
> > >     pmImportance.Value = msg.Fields(cdoImportance)
> > >         Call EventLog("saveinddb", "c")

> > >     Set rs = cmd.execute
> > >         Call EventLog("saveindb", "c2", , CStr(rs.State))
> > >         Call EventLog("saveindb", "cmd 0  " & CStr(cmd(0)) & vbTab &
> "cmd
> > 1
> > > " & CStr(cmd(1)))

> > >     Dim i As Integer
> > >     While Not rs.EOF
> > >         For Each fldloop In rs.Fields
> > >             Call EventLog("Saveindb", CStr(rs.Fields(i)))
> > >             i = i + 1
> > >         Next fldloop
> > >         i = 0
> > >         rs.MoveNext
> > >     Wend

> > >     SaveMsgInDB = 1

> > >     Call

...

read more »



Sat, 31 Jul 2004 16:23:03 GMT  
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
Not sure if you know this but there are other ways to use the Execute method
of a command object.

If you know the stored procedure does not return a recordset then you
can/should use this syntax   cmd.Execute ,, adExecuteNoRecords,   this is
slightly more efficient.

You cannot update the recordset returned by the Execute method as it is a
ForwardOnly ReadOnly cursor. You can however use the Command object as the
Source to the Recordset.Open method.

... define your command object

Set rs = New ADODB.Recordset

rs.Open cmd,, adOpenStatic, adLockBatchOptimistic

Bruce


Quote:
> Bruce,

> thanks for the clarification of the "problem", it's clear now what i have
to
> do

> Many thanks to your all for helping me ....

> Regards,
> Wim Mintiens
> Belgium



> > As far as receiving output parameters and return values (they are
> different)
> > from a stored procedure to an ADO command object, here's some additional
> > information.

> > SQL Server sends the values in the last data packet. This means a server
> > side recordset must be closed before receiving the parameter values.  A
> > clientside recordset does not have to be closed.

> > The return value parameter should be the first one defined in the ado
> > command.

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

> > Your stored procedure does not return any records.  No where in your
> > procedure is there a Select statement.  Your rs variable does get
> > instantiated by the cmd.Execute statement but it's state is closed.
Since
> > you then try to access the EOF property you receive this error.   You
> should
> > add a check for possible scenarios, something like.

> > Set rs = cmd.Execute

> > If Not rs is Nothing then
> >     if rs.state = adStateOpen then
> >         Do
> >             ' ........ record processing.
> >             rs.movenext
> >         Loop Until rs.EOF
> >     else
> >          'Recordset is closed.
> >     end if
> > Endif

> > HTH
> > Bruce



> > > 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



> > > > 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:
> > > > "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 :
> > > > ============================================================
> > > > 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















> > > > END

===========================================================================

- Show quoted text -

Quote:

> > > > 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 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

> > > >         Call EventLog("saveIndb", "init")
> > > >     DBUsrName = GetRegKey(DBUSRID)
> > > >     DBPWD = ""                             ' GetRegKey (DBPWD)
> > > >     DBSrvName = GetRegKey(DBSYS)
> > > >     DBName = GetRegKey(DBDBNAME)

> > > >         Call EventLog("saveindb", "connec", , DBUsrName & vbTab &
> DBPWD
> > &
> > > > vbTab & DBSrvName & vbTab & DBName)
> > > >     ' Set connection properties.
> > > >     cn.ConnectionTimeout = 25
'
> > Set
> > > > the time out.
> > > >     cn.Provider = "sqloledb"
'
> > > > Specify the OLE DB provider.
> > > >     cn.Properties("Data Source").Value = DBSrvName
'
> > Set
> > > > SQLOLEDB connection properties.
> > > >     cn.Properties("Initial Catalog").Value = DBName
'
> > Set
> > > > SQLOLEDB connection properties.
> > > >     cn.Properties("User ID").Value = DBUsrName
> > > >     cn.Properties("Password").Value = DBPWD
> > > >     cn.CursorLocation = adUseServer
> > > >         Call EventLog("saveindb", "open conn")
> > > >     ' Open the database.
> > > >     cn.Open

> > > >     Set cmd.ActiveConnection = cn
> > > >     cmd.CommandText = "sp_AddMsg"
> > > >     cmd.CommandType = adCmdStoredProc

> > > >         Call EventLog("saveinddb", "1")

> > > >     Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger,
> > adParamOutput)
> > > >     cmd.Parameters.Append pmMsgId
> > > >         Call EventLog("saveinddb", "3")

> > > >     Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput,
> 3)
> > > >     cmd.Parameters.Append pmInOut
> > > >         Call EventLog("saveinddb", "4")

> > > >     Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> > > > adParamInput, 255)
> > > >     cmd.Parameters.Append pmSubject
> > > >         Call EventLog("saveinddb", "5")

> > > >     Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar,
> > adParamInput,
> > > > 255)
> > > >     cmd.Parameters.Append pmMFrom
> > > >         Call EventLog("saveinddb", "6")

> > > >     Set pmMto = cmd.CreateParameter("msgTo", adVarChar,
adParamInput,
> > 255)
> > > >     cmd.Parameters.Append pmMto
> > > >         Call EventLog("saveinddb", "7")

> > > >     Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput,
> > 255)
> > > >     cmd.Parameters.Append pmCC

> > > >     Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar,
adParamInput,
> > > 255)
> > > >     cmd.Parameters.Append pmBCC

> > > >     Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> > > > adParamInput)
> > > >     cmd.Parameters.Append pmPriority

> > > >     Set pmImportance = cmd.CreateParameter("msgImportance",
adInteger,
> > > > adParamInput)
> > > >     cmd.Parameters.Append pmImportance

> > > >     Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> > > > adParamInput)
> > > >     cmd.Parameters.Append pmDateRcv

> > > >     Set pmDateTrans = cmd.CreateParameter("msgDtTrns",
adDBTimeStamp,
> > > > adParamInput)
> > > >     cmd.Parameters.Append pmDateTrans

> > > >         Call EventLog("saveinddb", "a")
> > > >     Select Case way
> > > >         Case "IN"
> > > >             pmInOut.Value = INCOMMING
> > > >             pmDateRcv.Value = msg.ReceivedTime

...

read more »



Sun, 01 Aug 2004 00:07:53 GMT  
 
 [ 11 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