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()