Sending Values from Stored Procedure to the Application in VB.NET

Many times we may be in need of sending the output value from stored procedure. Here is the stored procedure and the vb.net code to send and receive the value from the stored procedure

create procedure pro_employees

@LastName varchar(25),

@FirstName varchar(25),

@EmployeeID int output

insert into employees

(

lastname,

firstname

)

values

(

@Lastname,

@firstname

)

set @employeeid=(select EmployeeID from Employees where LastName=@lastname and FirstName=@firstname)

Here in the above stored procedure will return the employee id. The keyword output should be specified to return value from the stored procedure. The output value from the stored procedure can be received in vb.net by

Private Function Insertingdata()

Dim str2 As String = ConfigurationManager.ConnectionStrings("myconnection").ToString()

con = New OleDbConnection(str2)

Dim par As OleDbParameter = New OleDbParameter()

Dim cmd As OleDbCommand = New OleDbCommand()

cmd.Connection = con

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "pro_employees"

cmd.Parameters.Add("@lastname", OleDbType.VarChar, 50).Value = TextBox1.Text

cmd.Parameters.Add("@firstname", OleDbType.VarChar, 50).Value = TextBox2.Text

par = cmd.Parameters.Add("@EmployeeID", OleDbType.Integer)

par.Direction = ParameterDirection.Output

con.Open()

cmd.ExecuteNonQuery()

Session("spoutput") = par.Value.ToString()

Return Nothing

cmd.Dispose()

con.Close()

End Function

Here in the above code we have the

par = cmd.Parameters.Add("@EmployeeID", OleDbType.Integer)

par.Direction = ParameterDirection.Output

Session("spoutput") = par.Value.ToString()

The parameter value will have the employee id generated by the stored procedure. The parameter can be passed in 4 different way

ParameterDirection.Input

ParameterDirection.InputOutput
ParameterDirection.Output
ParameterDirection.ReturnValue

Input - used to specify the parameter passed is for input
Inputoutput - used to specify the parameter can be used both for input and output
Output - used to receive the output from the stored procedure
Returnvalue - returns value from the stored procedure