get return value from proc

[sagey]

i haven't used ado fro a while and i've forgotten some of it. below is my ado code and the storedproc. the insert works fine. but i can't get/display the return value.

any help would be appreciated

    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim con As New ADODB.Connection
    Dim unidParam As ADODB.Parameter
    Dim changeReasonParam As ADODB.Parameter
    Dim changeNotesParam As ADODB.Parameter
    Dim userNameParam As ADODB.Parameter
    Dim changedToParam As ADODB.Parameter
    Dim returnValue As ADODB.Parameter
    Dim conString As String
    
    cmd.CommandType = 4
    cmd.CommandText = "sp_Save_Update"
    'set params
    Set unidParam = cmd.CreateParameter("unid", adChar, adParamInput, 32)
    Set changeReasonParam = cmd.CreateParameter("changereason", adInteger, adParamInput, 4)
    Set changeNotesParam = cmd.CreateParameter("changenotes", adVarChar, adParamInput, 2048)
    Set changedToParam = cmd.CreateParameter("changedto", adInteger, adParamInput, 4)
    Set userNameParam = cmd.CreateParameter("username", adVarChar, adParamInput, 30)
    Set returnValue = cmd.CreateParameter("returnvalue", adInteger, adParamReturnValue, 4)
    'append params to command
    cmd.Parameters.Append unidParam
    cmd.Parameters.Append changeReasonParam
    cmd.Parameters.Append changeNotesParam
    cmd.Parameters.Append changedToParam
    cmd.Parameters.Append userNameParam
    cmd.Parameters.Append returnValue
    
    unidParam.Value = UNID
    changeReasonParam.Value = ChangeReason
    changeNotesParam.Value = Notes
    changedToParam.Value = ChangedTo
    userNameParam.Value = Username
    
    '(RS) tidy this up.
    'in clsdb create a function called getcmd that returns a comand
    'object with its active connection set, then will just need to set
    'all the otehr properties
    ' Create recordset by executing the command.
    Set con = db.getConnectionObject
    conString = db.getConnectionString
    con.Open conString
    Set cmd.ActiveConnection = con
    Set rs = cmd.Execute

@unid char(32),
@changereason int,
@changenotes varchar(2048),
@changedto int,
@username varchar(30),
@returnvalue int output

 AS

insert into 
	tbl_update(unid, username, change_reason, change_to, change_notes, date_created)
values
	(@unid, @username, @changereason, @changedto, @changenotes, getdate())

set
	@returnvalue = @@identity

[sagey]

i have done the following, but i don't think its the best way of doing it.

proc

SET NOCOUNT ON
insert into 
	tbl_update(unid, username, change_reason, change_to, change_notes, date_created)
values
	(@unid, @username, @changereason, @changedto, @changenotes, getdate())

	select @@identity as 'identity'

vb

rs.Fields("identity").Value

[Bombdrop]

herse the way i do it.

Option Explicit
 
Private Sub Command1_Click()
Dim conPolicyMaster As ADODB.Connection
Dim cmdPolicyMaster As ADODB.Command
Dim recPolicyMaster As ADODB.Recordset
Dim Params As ADODB.Parameters
Dim Param As ADODB.Parameter
 
Set conPolicyMaster = New ADODB.Connection
Set cmdPolicyMaster = New ADODB.Command
 
Dim strConnString As String
strConnString = Connection string
 
'Set connection properties and open
conPolicyMaster.ConnectionString = strConnString
conPolicyMaster.CursorLocation = adUseClient
conPolicyMaster.Open
 
'Set command properties
Set cmdPolicyMaster.ActiveConnection = conPolicyMaster
cmdPolicyMaster.CommandText = "testh1"
cmdPolicyMaster.CommandType = adCmdStoredProc
Set Params = cmdPolicyMaster.Parameters
 
'Define stored procedure parameters and append to command.
Params.Append cmdPolicyMaster.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
Params.Append cmdPolicyMaster.CreateParameter("@clientshort", adVarChar, adParamInput, 10)
 
'Specify input parameters
Params("@clientshort") = "TEST"
 
'Execute the command
Set recPolicyMaster = cmdPolicyMaster.Execute
 
'Disconnect recordset
Set recPolicyMaster.ActiveConnection = Nothing
 
'Close connection
conPolicyMaster.Close
 
'YOU can access the reutrn using either of these methods
Debug.Print cmdPolicyMaster.Parameters(0).Value
Debug.Print cmdPolicyMaster.Parameters("RETURN_VALUE")
End Sub

Hope this helps!!

[szlamany]

The SET NOCOUNT ON is kind of required in all SPROCS - as the ERROR COLLECTION of the COMMAND object gets populated with all those ROWS AFFECTED values if you don't use SET NOCOUNT ON.

You will notice that BombDrop closes the recordset before attempting to view the return parameters. That's meaningful, because ADO will not expose those parameters while there are still RECORDSET and ERROR collections to process from the server to the client.

Also, if the parameter is in the argument list it's not really a RETURN PARAMETER, really an output parameter...

We have several in our SPROCS

    With objCmd
        .CommandTimeout = 0
        .CommandText = s1
        .ActiveConnection = gCn
        .CommandType = adCmdStoredProc
        
        .Parameters.Append .CreateParameter("@PassConnId", adInteger, adParamInput, 8, glngConnId)
        .Parameters.Append .CreateParameter("@RetStat", adInteger, adParamOutput, 8)
        .Parameters.Append .CreateParameter("@RetText", adVarChar, adParamOutput, 100)
        .Parameters.Append .CreateParameter("@RetMode", adInteger, adParamOutput, 8)
        .Parameters.Append .CreateParameter("@RetGrid", adInteger, adParamOutput, 8)
        .Parameters.Append .CreateParameter("@RetExtra", adVarChar, adParamOutput, 100)