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