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 

as 

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

Next Recommended Readings