Pulling Information Using DataReader With ADO.NET - How to retrieve a single value returned by a stored procedure using Data Reader in ADO.NET from ASP.NET: the code
(Page 2 of 5 )
Now, we shall just follow the steps in the preview section practically. Add a new web form (call it “StoredProcedureReturningSingleValue”) with a single button captioned “Retrieve Single Value” and a label named “lblEname.”
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 “Retrieve Single Value” 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_getName"
.Parameters.Add("@empno", 1201)
.Connection = cn
.Connection.Open()
Dim dr As SqlDataReader = .ExecuteReader
If dr.Read Then
name = dr(0)
Else
name = "Not Found"
End If
'release resources
.Connection.Close()
.Dispose()
End With
Me.lblEname.Text = name
End Sub
Set the start page and execute your application (by pressing F5) and click on the button “Retrieve Single Value.” Once it executes successfully, you will see either the employee name or a simple message, “Not Found.”
From the above code, we need to concentrate only on the following code fragment:
Dim dr As SqlDataReader = .ExecuteReader
If dr.Read Then
name = dr(0)
Else
name = "Not Found"
End If
“ExecuteReader” is a method which returns a “DataReader” object to pull the information from the database (in this case it is stored procedure) continuously, in a forward only and read-only manner.
Next: How to retrieve a single value returned by a stored procedure using ExecuteScalar >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 16 
