Newsgroup: it.comp.lang.visual-basic
Titolo: get return value from stored procedure

I am writing a macro in Outlook.  It excutes SQL to fire a stored
procedure.  I need my VB code to receive back that @@Identity field
that contains the ID for the record that was inserted by the stored
procedure.  I cannot figure out how to do it.  I have VBscript that
does it, but this must not be set up exactly the same way so it
doesn't work.  I found this: "Then I would create a command object in
VB with an output parameter to receive the returned identity field. " 
But how do I do it?  I am not experienced with VB so I struggle with
the syntax.  Thanks for any help!
The relevant code:
******************************************************************
Function Add2Db(varRecords As Variant, strBody As String) As Integer
Dim strArrLubeRecords() As String
Dim myConn As Object
Dim myCommand As Object
Dim strConnectionString As String
Dim myRS As Object
Dim ServerName, DatabaseName, UserName, Password As String
Dim strConnectionString As String
Dim strSQL As String
Dim intCategCount
Dim Error As ErrObject
MsgBox ("Add2Db is running")
'Get literature records into string array from variant
strArrLubeRecords() = varRecords
Dim ServerName, DatabaseName, UserName, Password As String
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
Set myRS = CreateObject("ADODB.Recordset")
'Put values into connection variables. (details omitted online)
ServerName = "****"
DatabaseName = "****"
UserName = "****"
Password = "*****"
'Specify the OLE DB provider.
myConn.Provider = "sqloledb"
'Set SQLOLEDB connection properties.
myConn.Properties("Data Source").Value = ServerName
myConn.Properties("Initial Catalog").Value = DatabaseName
myConn.Properties("User ID").Value = UserName
myConn.Properties("Password").Value = Password
'Open the database.
myConn.Open
Dim x As Integer
Dim intEmailID As String
Dim iNumAffectedRows As Integer
Dim iNumRows As Integer
intEmailID = 0
iNumRows = 0
        strSQL = "EXEC sp_EmailAddMessage " & _
            "@N_FROM = '" & strArrLubeRecords(1) & "', " & _
            "@N_TO = '" & strArrLubeRecords(2) & "', " & _
            "@N_CC = '" & strArrLubeRecords(3) & "', " & _
            "@N_SUBJECT = '" & strArrLubeRecords(4) & "', " & _
            "@N_BODY = '" & strBody & "', " & _
            "@D_SENT = '" & strArrLubeRecords(0) & "', " & _
            "@C_ORG_ID = " & strArrLubeRecords(6) & ", " & _
            "@intEmailid = NULL"
        Set myRS = myConn.Execute(strSQL)
        intEmailID = myCommand("@@IDENTITY")
'''problem is the line directly above''''''''''''''''''''''''
I tried getting "RETURN_VALUE" and @@IDENTITY from myRS but it usually
says it's not a valid ordinal or whatever.  What value do I need to
request and from which object?  I tried myRS.Fields("RETURN_VALUE")
and several other things....


 Try this code :
yourCmd.ActiveConnection = yourConnection
yourCmd.CommandType = adCmdStoredProc '4 -> Stored Procedures
yourCmd.CommandText = "sp_EmailAddMessage"
yourCmd.Parameters.Refresh
yourCmd.Parameters(1).Value = yourParam
yourCmd.Parameters(2).Value = yourParam
yourCmd.Parameters(3).Value = yourParam
...
...
yourCmd.Parameters(n).Value = yourParam
yourCmd.Execute()
yourRetVal = yourCmd.Parameters(0).Value '* --> This is RETURN_VALUE
Bye
--------------------------------
Inviato via 
http://usenet.libero.it

So I should set this up as running the stored procedure directly,
rather than a SQL string calling the stored procedure.  Makes sense! 
I'll try it on Monday.  Seems so obvious now.  Thanks so much!


What do I dim adCmdStoredProc as?
myCommand.CommandType = adCmdStoredProc '4 -> Stored Procedures
Thanks...


...


 Complete code
  tries to eliminate the parentheses.
  myCommand.Execute
 Bya


Thanks much!  Next time I'll try that first before asking...

 Thanks much! Next time I'll try that first before asking...
 No problem!
