Executing a Stored Procedure having INPUT parameters using a Command object in Visual Basic

Executing a Stored Procedure  having INPUT parameters  using a Command object

The stored procedure returns names when we pass the account category. As you can well understand, the below procedure accepts an input parameter. So, in order to execute the stored procedure, you need to create a Parameter object by using the CreateParameter method of the Command object. The code for the stored procedure is given below. First, create the stored procedure and then write code in the frmDetails form.

Create Procedure GetAccountByCat
@AccCat varchar(1)
As
SELECT  AccountName FROM AccountsTable WHERE AccountCat =@AccCat
Go

Declaration Section

In this section, create a Command object, comobj, a string strCat, and a Parameter object, prmByCat as shown below.

Option Explicit
Dim CN As Connection
Dim comobj As Command
Dim prmByCat As Parameter
Dim strCat As String

Initialize the CN object and the comobj object. Now initialize the ConnectionString properties of the CN object. We need to use the Open method, as given in the following code.

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

Initialize the ActiveConnection, CommandText, and CommandType properties of the comobj object as shown in the following code.

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

The procedure GetAccountByCat accepts an input parameter, which needs to be accepted from the user. So, the next step is accepting it from the user and storing it in the String type variable, strCat.

strCat = Trim(InputBox(“Enter Customer Category.:”))

The next step is to initialize the Parameter object, prmByCat, declared earlier. We need to call the CreateParameter method of the comobj object.

Set parameter=Command.CreateParameter (Name, Type, Direction, Size, Value), Where parameter is the Parameter object returned by the CreateParameter method and Command is the Command object. The code for calling the CreateParameter function is as follows

Set prmByCat = comobj.CreateParameter(“ACat”, adBSTR, adParamInput)

comobj.Parameters.Append prmByCat

The Parameter object has a property called Value. You need to assign the value entered by the user to this Value property. The value entered by the user is stored in the string type variable, strCat.

prmByCat.Value = strCat

The last step is to call the Execute method of the Command object.

comobj.Execute

End Sub