Using SqlCommand to populate Label text
May 19th, 2009
Sometimes SqlDataSource doesn’t work for what you want to do. It’s a great control, but as I’ve mentioned before, it can interfere with your plans because of the page lifecycle. You should also know how to run queries and get the results using the ADO.NET classes from System.Data namespace.
The following example shows you how to set up a simple ASPX page with a GridView. The GridView (bound with an easy SqlDataSource) grabs the employees from AdventureWorks. When you select a row, the manager’s details will show up in the Label controls. We make use of the DataKeyNames collection to make grabbing the ManagerID easier in the SelectedIndexChanged event. We use the ManagerID as a parameter to the query, then use SqlDataReader to read the results and populate the Label Text.
This example uses AdventureWorks, with schemas removed.
On to the code.
ASPX
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default4.aspx.vb" Inherits="Default4" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<a href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd</a>"> <html xmlns="<a href="http://www.w3.org/1999/xhtml">http://www.w3.org/1999/xhtml</a>" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <asp:SqlDataSource ID="sqldsEmployees" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>" SelectCommand="SELECT Employee.EmployeeID, Employee.ManagerID, Employee.Title, Contact.FirstName, Contact.LastName FROM Employee INNER JOIN Contact ON Employee.ContactID = Contact.ContactID"> </asp:SqlDataSource> <div style="z-index: 101; left: 17px; width: 370px; position: absolute; top: 83px; height: 355px"> <asp:GridView ID="gvEmployees" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" AutoGenerateSelectButton="True" Caption="Employee List" CellPadding="4" DataKeyNames="EmployeeID,ManagerID" DataSourceID="sqldsEmployees" ForeColor="#333333" GridLines="None" Width="461px"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" Visible="False" /> <asp:BoundField DataField="ManagerID" HeaderText="ManagerID" SortExpression="ManagerID" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" /> <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" /> </Columns> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </div> <div style="z-index: 102; left: 502px; width: 310px; position: absolute; top: 85px; height: 364px"> <h3>Manager Info</h3> Title: <asp:Label ID="lblManagerTitle" runat="server"></asp:Label><br /> Name: <asp:Label ID="lblManagerName" runat="server"></asp:Label><br /> E-mail: <asp:Label ID="lblManagerEmail" runat="server"></asp:Label><br /> Phone: <asp:Label ID="lblManagerPhone" runat="server"></asp:Label></div> </form> </body> </html>
Code Behind
Imports System.Collections.Specialized
Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Class Default4
Inherits System.Web.UI.Page
' use the SelectedIndexChanged event, not RowCommand, because the properties for the selected row aren't populated yet in row command
Protected Sub gvEmployees_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvEmployees.SelectedIndexChanged
Dim managerID As New Integer
' we can get the manager ID because we set it in the GridView's DataKeys collection as the second one
Dim selectedManger As Object = Me.gvEmployees.SelectedDataKey.Values("ManagerID")
If Not selectedManger Is Nothing Then
managerID = CType(selectedManger, Integer)
Dim oSqlDataReader As SqlDataReader = Nothing
Try
Using oSqlConnection As New SqlConnection
oSqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AdventureWorksConnectionString").ConnectionString
Using oSqlCommand As New SqlCommand
oSqlCommand.Connection = oSqlConnection
oSqlCommand.CommandText = "SELECT Employee.Title, Contact.FirstName, Contact.LastName, Contact.EmailAddress, Contact.Phone FROM Employee INNER JOIN Contact ON Employee.ContactID = Contact.ContactID WHERE <a href="mailto:EmployeeID=@EmployeeID">EmployeeID=@EmployeeID</a>"
oSqlCommand.CommandType = Data.CommandType.Text
oSqlCommand.Parameters.Clear()
oSqlCommand.Parameters.AddWithValue("@EmployeeID", managerID)
oSqlConnection.Open()
oSqlDataReader = oSqlCommand.ExecuteReader(Data.CommandBehavior.CloseConnection)
While oSqlDataReader.Read()
' there should only be one match
Me.lblManagerTitle.Text = oSqlDataReader("Title")
Me.lblManagerName.Text = oSqlDataReader("FirstName") & " " & oSqlDataReader("LastName")
Me.lblManagerEmail.Text = oSqlDataReader("EmailAddress")
Me.lblManagerPhone.Text = oSqlDataReader("Phone")
End While
End Using
End Using
Finally
If Not oSqlDataReader Is Nothing AndAlso Not oSqlDataReader.IsClosed Then
oSqlDataReader.Close()
End If
End Try
End If
End Sub
End Class
Did this article help you? Please share it on Digg, DZone, or your favorite social network site! As always, I appreciate comments, they let me know if people like what I’m posting. Thanks!
