HOW TO: Format Data and Handle Null Values in a DataGrid Web Control

View products that this article applies to.

Summary

This article demonstrates how to create a DataGrid Web control, to bind it to a database, to handle null values, and to format the values.

Displaying data in a table by using "plain" HTML can be tedious to code and difficult to maintain. ASP.NET provides a DataGrid Web control that you can bind to a data source and customize to provide a fast, flexible way to display a lot of information.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows XP Professional
Microsoft .NET Framework
Microsoft Visual Studio .NET
Microsoft Internet Information Services (IIS) 5.0
Microsoft SQL Server 2000

This article assumes that you are familiar with the following topics:

Web applications
ASP.NET
Microsoft Visual Basic .NET

Using the DataGrid Web Control

ASP.NET provides a DataGrid Web control that you can bind to several different data sources. You can customize it to provide a fast, flexible way to display a lot of information. The DataGrid Web control generates the necessary HTML on the client to produce a customized table for displaying data. This article demonstrates how to create a DataGrid Web control, to bind it to a data source, to handle null values in a data source that is to be loaded into the grid, and to format data fields as they are displayed in the browser.

To Use the DataGrid Web Control (Part 1)

These steps create a DataGrid Web control and bind the DataGrid Web control to a data source:

Start Visual Studio .NET.
Create a new ASP.NET Web Application project by using Visual Basic .NET. Name the new project DataGridControlExample.
In HTML view in WebForm1.aspx, paste the following code between the opening and closing form tags. This create a DataGrid Web control on the form that can be accessed by code on the server:
<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
Switch to the Code window by right-clicking the window, and then clicking View Code.
Import the System.Data.SqlClient namespace. Place the following line of code at the top of the code file before the class declaration:
Imports System.Data.SqlClient
Paste the following code just below the INHERITS statement in the WebForm1 class (unless this code has already been added automatically):
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Paste the following code inside the Page_Load event procedure in the WebForm1 class. A connection to the Pubs database is made in this event. A DataSet object is populated from the Titles table by using a SQLDataAdapter object. The DataSource property of the DataGrid object is set to specify the source of the information that will be displayed in the control. The resulting DataSet object is then bound to the DataGrid1 control and displayed by using the default view that is associated with the table. The SELECT statement for the data source also modifies null price values. Instead of a null value, the code returns a price of 0 (zero):

Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
Dim myConnString As String myConnString = "Initial Catalog=pubs;server=localhost;User ID=<username>;Password=<strong password>;" Dim myConnection As SqlConnection = New SqlConnection(myConnString) Dim myCommand As SqlCommand = New SqlCommand _ ("SELECT title, ISNULL(price, 0) AS price, pubdate" & _ " FROM titles", MyConnection) Dim myAdapter As SqlDataAdapter = New SqlDataAdapter() myAdapter.SelectCommand = myCommand myConnection.Open() Dim myDataset As DataSet = New DataSet() myAdapter.Fill(myDataset, "Titles") myConnection.Close() DataGrid1.DataSource = myDataset.Tables("Titles").DefaultView DataGrid1.DataBind()
Note Make sure to modify the connection string to use your SQL Server user ID and password. Also, for the code to work, make sure that the authentication is set to SQL Server and Windows in the Security section of the SQL Server properties. To view or modify the properties, start SQL Server Enterprise Manager, right-click your SQL server under the console root, and then click Properties.
On the File menu, click Save All.
On the Debug menu, click Start to build and run the application. WebForm1 is displayed. You see a table that contains the contents of the Titles database table. All of the column headers are automatically populated with the field names. The fields appear in the order in which they occur in the table. No price column values are blank. Instead, a value of 0 (zero) is displayed. All of the other data is displayed without any special formatting.

To Use the DataGrid Web Control (Part 2)

Although the DataGrid control is easy to code and implement, this method performs only basic formatting of the data. You can use the DataBinder class to link properties of the DataGrid1 control to a DataSource column at run time, and to add formatting to that data before it is displayed in the browser. In the next example, you use the DataBinder class to format the price and pubdate field values before they are output to the browser:

In HTML view in WebForm1.aspx, replace the code between the form tags with the following HTML code. This creates a DataGrid control that does not generate columns and headers automatically from the data source. Instead, several of the data source fields are bound to the control. The price and pubdate fields are formatted at run time by using the TemplateColumn class. The TemplateColumns class contains a label control that uses the Eval method of the DataBinder class to format its contents by using a data-binding expression and a format string. The DataBinder.Eval method accepts the container, data-binding expression, and a format string:
<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:boundcolumn headertext="Title" datafield="title"/> <asp:TemplateColumn> <HeaderTemplate> Price </HeaderTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, _ "price", "{0:c}")%>'/> </ItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn> <HeaderTemplate> Publish Date </HeaderTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, _ "pubdate", "{0:d}")%>'/> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid>
On the File menu, click Save All.
On the Debug menu, click Start to build and run the application. WebForm1 is displayed. A table that contains only the title, price, and pubdate value for each item in the Titles table is displayed. The column headers are shown as "Title," "Price," and "Publish Date." The price field column is formatted as currency. Null prices are replaced with "$0.00." The pubdate values are formatted by using a long date format.
Quit the web browser.

Complete Code Listing

Code Listing for Part 1

This is the code for the code-behind WebForm1.aspx.vb file:

Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Imports System.Data.SqlClient

Public Class WebForm1
    Inherits System.Web.UI.Page

    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
#End Region

    Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        Dim myConnString As String
myConnString = "Initial Catalog=pubs;server=localhost;User ID=<username>;Password=<strong password>;"
        Dim myConnection As SqlConnection = _
            New SqlConnection(myConnString)
        Dim myCommand As SqlCommand = New SqlCommand _
            ("SELECT title, ISNULL(price, 0) AS price, pubdate" & _
                " FROM titles", MyConnection)
        Dim myAdapter As SqlDataAdapter = New SqlDataAdapter()

        myAdapter.SelectCommand = myCommand
        myConnection.Open()

        Dim myDataset As DataSet = New DataSet()
        myAdapter.Fill(myDataset, "Titles")

        myConnection.Close()

        DataGrid1.DataSource = myDataset.Tables("Titles").DefaultView
        DataGrid1.DataBind()
    End Sub
End Class

This is the code for the WebForm1.aspx file:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="DataGridControlExample.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
     <HEAD>
          <title>WebForm1</title>
          <meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
          <meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
          <meta name="vs_defaultClientScript" content="JavaScript">
          <meta name="vs_targetSchema" 
content="http://schemas.microsoft.com/intellisense/ie5">
     </HEAD>
     
     <body MS_POSITIONING="GridLayout">
          <form id="Form1" method="post" runat="server">
               <asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
          </form>
     </body>
</HTML>

Code Listing for Part 2

This is the code for the code-behind WebForm1.aspx.vb file:
Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Imports System.Data.SqlClient

Public Class WebForm1
    Inherits System.Web.UI.Page

    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
#End Region

    Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        Dim myConnString As String
myConnString="Initial Catalog=pubs;server=localhost;User ID=<username>;Password=<strong password>;"
        Dim myConnection As SqlConnection = _
            New SqlConnection(myConnString)
        Dim myCommand As SqlCommand = New SqlCommand _
            ("SELECT title, ISNULL(price, 0) AS price, pubdate" & _
                " FROM titles", MyConnection)
        Dim myAdapter As SqlDataAdapter = New SqlDataAdapter()

        myAdapter.SelectCommand = myCommand
        myConnection.Open()

        Dim myDataset As DataSet = New DataSet()
        myAdapter.Fill(myDataset, "Titles")

        myConnection.Close()

        DataGrid1.DataSource = myDataset.Tables("Titles").DefaultView
        DataGrid1.DataBind()
    End Sub
End Class

This is the code for the WebForm1.aspx file:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="DataGridControlExample.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
     <HEAD>
          <title>WebForm1</title>
          <meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
          <meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
          <meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" 
content="http://schemas.microsoft.com/intellisense/ie5">
     </HEAD>
     <body MS_POSITIONING="GridLayout">
          <form id="Form1" method="post" runat="server">
               <asp:DataGrid id="DataGrid1" runat="server" 
                    AutoGenerateColumns="False">
                    <Columns>
                         <asp:boundcolumn headertext="Title" datafield="title"/>
                         <asp:TemplateColumn>
                              <HeaderTemplate>
                                   Price
                              </HeaderTemplate>
                              <ItemTemplate>
                                   <asp:Label ID="Label2" runat="server" 
                                        Text='<%# DataBinder.Eval(Container.DataItem, _
                                             "price", "{0:c}")%>'/>
                              </ItemTemplate>
                         </asp:TemplateColumn>
                         <asp:TemplateColumn>
                              <HeaderTemplate>
                                   Publish Date
                              </HeaderTemplate>
                              <ItemTemplate>
                                   <asp:Label ID="Label1" runat="server" 
                                        Text='<%# DataBinder.Eval(Container.DataItem, _
                                             "pubdate", "{0:d}")%>'/>
                              </ItemTemplate>
                         </asp:TemplateColumn>
                    </Columns>
               </asp:DataGrid>
          </form>
     </body>
</HTML>

↑ Back to the top