Executing a Stored Procedure having INPUT and OUTPUT parameter using a Command object using Visual Basic

Executing a Stored Procedure  having INPUT and OUTPUT parameter  using a Command object

Stored procedures may contain output parameters and return values. For example, the procedure GetAccountName, can return the name of the Account for an Account Code passed as a parameter. First, create the stored procedure and then write code in the frmAccountDetails form.

Create Procedure GatAccountName
@AccCode varchar(6),
@AccName varchar(30) OUTPUT
As

SELECT  ,@AccName=AccountName FROM AccountsTable WHERE AccountCode =@AccCode
Go

The following code shows how to retrieve the output parameter from a stored procedure.

Option Explicit
Dim CN As Connection
Dim comobj As Command
Dim prmByAcc As Parameter
Dim strAcc As String

Private Sub Form_Load()
Set CN = New Connection
Set comobj = New Command

With CN
.ConnectionString = “Integrated Security=SSPI;Initial Catalog=FinAccounting;Data Source=SYS1”
.Provider = “SQLOLEDB”
.Open
End With

With comobj
.ActiveConnection = CN
.CommandText = “GetAccountName”
.CommandType = adCmdStoredProc
End With

strAcc = Trim(InputBox(“Enter Customer Code.:”))
Set prmByAcc = comobj.CreateParameter(“ACode”, adBSTR, adParamInput, 6, strAcc)
comobj.Parameters.Append prmByAcc

Set prmByAcc = comobj.CreateParameter(“AName”, adBSTR, adParamOutput, 30)
comobj.Parameters.Append prmByAcc
comobj.Execute
MsgBox comobj.Parameters(“AName”)
End Sub