Working with NULL, OUTPUT and RETURN in ADO.NET - How to work with an OUTPUT parameter of a stored procedure using ADO.NET from ASP.NET: the code
(Page 3 of 4 )
Now we need to call the stored procedure discussed in the previous section. Add a new web form (call it “outputParamDemo”) to an already existing project (as specified in my previous article). Add a button (named “Execute Retrieve OUTPUT parameter”) and a label (named “lblName”).
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your “Execute Retrieve OUTPUT parameter” button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim name As String
Dim cn As New SqlConnection("Data Source=.;initial
catalog=Northwind;user id=sa")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_emp_getEname"
Dim paramName As New SqlParameter
With paramName
.ParameterName = "@name"
.SqlDbType = SqlDbType.VarChar
.Direction = ParameterDirection.Output
.Size = 20
End With
.Parameters.Add("@empno", 1002)
.Parameters.Add(paramName)
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
name = paramName.Value
'release resources
.Connection.Close()
.Dispose()
End With
Me.lblName.Text = name
End Sub
Set the start page, execute your application (by pressing F5) and click on the button “Execute Retrieve OUTPUT parameter.” Once it executes successfully, you should be able to see the value sent by the stored procedure to your ASP.NET web form.
The most important issue from the above code is that you need to specify “ParameterDirection.Output” as part of the “direction” property. What happens if the stored procedure cannot find any row? It places NULL within the parameter “@name” and sends it back. So, it is our task to check it out in our program.
The code can be further simplified (without creating an explicit parameter object) as follows:
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_emp_getEname"
.Parameters.Add("@empno", 1002)
.Parameters.Add("@name", SqlDbType.VarChar, 20)
.Parameters("@name").Direction =
ParameterDirection.Output
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
name = .Parameters("@name").Value
'release resources
.Connection.Close()
.Dispose()
End With
Next: How to retrieve the value returned by a stored procedure using ADO.NET from ASP.NET: the stored procedure >>
More MS SQL Server Articles
More By Jagadish Chaterjee
