Pulling Information using DataAdapter with ADO.NET - How to retrieve only one row returned by a stored procedure using "DataAdapter" in ADO.NET from ASP.NET
(Page 3 of 4 )
I already introduced "DataAdapter" in my previous section. We shall further extend it to get only a single row!
Let us consider a simple stored procedure as follows:
CREATE PROCEDURE dbo.sp_emp_getEmployeeDetails
(
@empno int
)
AS
SELECT * FROM emp
WHERE empno = @empno
RETURN
From the above stored procedure, it is quite certain that it returns only one row (or even none). I would like to read that row and present it in the text boxes!
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_getEmployeeDetails "
.Parameters.Add("@empno",1001)
.Connection = cn
End With
Dim dt As New DataTable
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
da.Dispose()
cmd.Dispose()
cn.Close()
Dim dr As DataRow = dt.Rows(0)
Me.txtEname.text = dr("ename")
Me.txtSal.text = dr("sal")
Me.txtDeptno.text = dr("deptno")
dt.Dispose()
End Sub
We need to concentrate only on the highlighted rows (in red). You can observe that I am retrieving only the first row from the data table using the following statement:
Dim dr As DataRow = dt.Rows(0)
Once we get the handle to that row, we can simply retrieve all the column information and assign it to the text boxes using:
Me.txtEname.text = dr("ename")
Me.txtSal.text = dr("sal")
Me.txtDeptno.text = dr("deptno")
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft SQL Server 2000. If anything does not work, please drop me a line so that I can guide you. The entire solution for this article is freely available in the form of a zip downloadable.
Next: How to retrieve multiple result sets from stored procedure using "DataAdapter" in ADO.NET from ASP.NET >>
More MS SQL Server Articles
More By Jagadish Chaterjee
