Cant Get Client App to recognize SQL OUTPUT Parameter

[metsey]

I have the following VB 6 code calling a sp in SQL 7.0. The sp returns a value through an output parameter. I cannot get the VB app to pickup the output parameter value.

With g_adoComm
.CommandType = adCmdStoredProc

' set the name of the stored procedure
.CommandText = "usp_GroupDesign"

' create and append the input parameter
.Parameters.Append g_adoComm.CreateParameter("ClientCode", adBSTR, adParamInput)

' set the initial value of the parameter
.Parameters("ClientCode&quot.Value = sClient

' create and assign output parameter value to function output
.Parameters.Append g_adoComm.CreateParameter("@Client", adBSTR, adParamOutput)

'set connection object
.ActiveConnection = g_adoConn

.Execute

vReturn = .Parameters("@Client&quot.Value

How do I get the client app to recognize the SQL Output Param? Any help would be greatly appreciated.

Mark

 

[bbosley]

Hi,

I had the exact same problem. I have a question (sorry if it is a stupid question). Is your SQL Server stored proc def something like this (make sure you have the output parameter declared as OUTPUT):

ALTER PROCEDURE usp_GroupDesign
@ClientCode BIT,
@Client BIT OUTPUT

Here is an example of the code that works for me:

Dim cmSQLCommand As ADODB.Command
Dim pmTemp As ADODB.Parameter

' Instantiate a new instance of a command object
Set cmSQLCommand = New ADODB.Command

' Set up the command object for our stored procedure
With cmSQLCommand
.ActiveConnection = vstrConnectionString
.CommandType = adCmdStoredProc
.CommandText = "dbo.spi_CommissionSchedule"
.Parameters.Append .CreateParameter("@ExpenseInputID", adInteger, _
adParamInput, 4, vrstInputValues!ExpenseInputID)
.Parameters.Append .CreateParameter("@BasicLifeFlag", adBoolean, _
adParamInput, 1, vrstInputValues!BasicLifeFlag)
.Parameters.Append .CreateParameter("@DepLifeFlag", adBoolean, _
adParamInput, 1, vrstInputValues!DepLifeFlag)
.Parameters.Append .CreateParameter("@EHCFlag", adBoolean, _
adParamInput, 1, vrstInputValues!EHCFlag)
.Parameters.Append .CreateParameter("@LTDFlag", adBoolean, _
adParamInput, 1, vrstInputValues!LTDFlag)
.Parameters.Append .CreateParameter("@STDWIFlag", adBoolean, _
adParamInput, 1, vrstInputValues!STDWIFlag)
.Parameters.Append .CreateParameter("@DentalFlag", adBoolean, _
adParamInput, 1, vrstInputValues!DentalFlag)
.Parameters.Append .CreateParameter("@CommissionScheduleID", adInteger, _
adParamOutput, 4)
' Execute the command with no records (no recordset returned)
.Execute , , adExecuteNoRecords
End With
vlngCommissionScheduleID = cmSQLCommand.Parameters("@CommissionScheduleID&quot

Hope this helps.

Bryan Bosley