Introduction

Basics¶

• To place a DataGrid on an ASP.NET Web page you simply need to add the following code. The id you choose will be the name of the DataGrid you'll use when referring to it in your server-side code.
  

<asp:datagrid runat="server" id="ID_of_DataGrid" /> 

• In order to have the DataGrid display anything useful we need to bind the DataGrid to some collection of information. This collection of information can be any object that supports the IEnumerable interface, including DataReaders. (For more information on reading database results into DataReaders using ADO.NET be sure to read: Efficiently Iterating Through Results from a Database Query using ADO.NET.)
  

• The following example displays the database contents in a plane-Jane HTML table - it's pretty ugly.
  

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
< script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub	
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(
          ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Const strSQL as String = "sp_Popularity"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to the datareader and databind
    myConnection.Open()
    dgPopularFAQs.DataSource = myCommand.ExecuteReader(
                              CommandBehavior.CloseConnection)
    dgPopularFAQs.DataBind()	
  End Sub
</script>

<asp:datagrid id="dgPopularFAQs" runat="server" />

Display Properties¶

Specifying DataGrid Formatting Options¶

<%@ Import Namespace="System.Drawing" %>

< script runat="server">
  sub Page_Load(sender as Object, e as EventArgs)
    ... 
    DataGridID.BackColor = Color.Red
    ...
  end sub
</script>

<asp:datagrid runat="server" BackColor="Red" /> 

• BackColor - specifies the background color.
• Font - specifies the font information for the DataGrid. Font information includes things like the font family, the point size, if its bold, italicized, etc.
• CellPadding - specifies the cellpadding of the HTML table
• CellSpacing - specifies the cellspacing of the HTML table
• Width - specifies the width of the HTML table (can be in pixels, percentages, etc.)
• HorizontalAlign - specifies how the table should be aligned (Left, Right, Center, NotSet)
  

<asp:DataGrid runat="server" id="dgFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Bold="True" Font-Name="Verdana"
                Font-Size="10pt" />

DataGrid Styles¶

• HeaderStyle - Specifies the style information for the header (the top row of the table that lists the column names; you must have the DataGrid's ShowHeader property set to true (the default).)
• FooterStyle - Specifies the style information for the footer (the bottom row of the table that lists the column names; you must have the DataGrid's ShowFooter property set to true (the default is false).)
• ItemStyle - Specifies the style information for each row in the table.
• AlternatingItemStyle - Specifies the style information for alternate rows in the table. You can set the ItemStyle and AlternatingItemStyle to different values for an easier-to-read DataGrid (see the below demo for an example).
  

<asp:DataGrid runat="server">
  <HeaderStyle BackColor="Red" />
</asp:DataGrid>

<asp:DataGrid runat="server" id="dgFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana"
                Font-Size="10pt">
  <HeaderStyle BackColor="Black" ForeColor="White" 
               Font-Bold="True" HorizontalAlign="Center" />
  <AlternatingItemStyle BackColor="White" />
</asp:datagrid>

Specifying what Columns Should Appear¶

<asp:DataGrid runat="server" AutoGenerateColumns="False">
  <Columns>
    <asp:BoundColumn DataField="DatabaseColumnName1" ... />
    <asp:BoundColumn DataField="DatabaseColumnName2" ... />
    ...
    <asp:BoundColumn DataField="DatabaseColumnNameN" ... />
  </Columns>
</asp:datagrid>

• HeaderText - Specifies the text that should appear in the column's header.
• FooterText - Specifies the text that should appear in the column's footer. Set ShowFooter to True if you want to display a footer in your DataGrid.
• HeaderStyle / FooterStyle / ItemStyle - Contains all of the properties that the DataGrid's styles did. Useful for centering columns, specifying fore/background color, etc.
• DataFormatString - Specifies formatting instructions.
  

• we want to not show the FAQID or FAQCategoryID columns
• we'd like to perform formatting for the number column (ViewCount), as well as the date/time column (DateEntered
• we'd like to have numeric columns have their values centered.
  

<asp:DataGrid runat="server" id="dgPopularFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana" CellPadding="4"
                Font-Size="10pt" AutoGenerateColumns="False">
  <HeaderStyle BackColor="Black" ForeColor="White" 
             Font-Bold="True" HorizontalAlign="Center" />
  <AlternatingItemStyle BackColor="White" />
	  
  <Columns>
    <asp:BoundColumn DataField="CatName" HeaderText="Category Name"  />
    <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    <asp:BoundColumn DataField="ViewCount" DataFormatString="{0:#,###}" 
           HeaderText="Views" ItemStyle-HorizontalAlign="Center" />
    <asp:BoundColumn DataField="SubmittedByName" HeaderText="Author"  />
    <asp:BoundColumn DataField="SubmittedByEmail" HeaderText="Author's Email"  />
    <asp:BoundColumn DataField="DateEntered" HeaderText="Date Added"
			DataFormatString="{0:MM-dd-yyyy}"  />    
  </Columns>
</asp:datagrid>

• The DataFormatString property looks a bit funny. It should always look like: {0:format string} . The {0:...} specifies to format the first argument (the value of the particular column returned by the DataReader) using the format string specified by .... In my example I used the format string #,### to format a number with commas after every three digits, and MM-dd-yyyy to format the date/time field to show the month, day, and year. Review the docs for more information on formatting strings.
  

Custom Events¶

Building on a Foundation¶

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana" CellPadding="4"
                Font-Size="10pt" AutoGenerateColumns="False">
    <HeaderStyle BackColor="Black" ForeColor="White" Font-Bold="True" 
                 HorizontalAlign="Center" />
    <AlternatingItemStyle BackColor="White" />
	  
    <Columns>
	  <asp:ButtonColumn Text="Details" HeaderText="FAQ Details" />
	  <asp:BoundColumn DataField="FAQID" Visible="False" />
      <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    </Columns>
  </asp:datagrid>
</form>

• The ButtonType property specifies the type of button to use. The default is a textual link button. If you want to display a standard button, you can simply specify: ButtonType="PushButton" in the ButtonColumn tag.
• The HeaderText property specifies the text that should go in the header of the DataGrid's column in which the button appears.
• The Text property specifies the textual display for each button.
• As with the BoundColumn tag, the ButtonColumn tag can have each button's text be the value of some column in the DataGrid's DataSource - simply omit the Text property in the ButtonColumn class and set the DataTextField property to the name of the database column whose value you wish to have displayed as the button's text.
  

Responding to a Button Click¶

Sub eventHandlerName(sender as Object, e as DataGridCommandEventArgs) 
...
End Sub 

<asp:datagrid runat="server"
...
OnItemCommand="eventHandlerName">
...
</asp:datagrid> 

Determining What Row's Button was Clicked¶

<asp:DataGrid runat="server" ... >
  <Columns>
    <asp:ButtonColumn Text="Details" HeaderText="FAQ Details" CommandName="details" />
    <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID" />
    <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
  </Columns>
</asp:datagrid>

Sub detailsClicked(sender as Object, e As DataGridCommandEventArgs)
    Dim buttonColumn as TableCell = e.Item.Cells(0)
    Dim FAQIDColumn as TableCell = e.Item.Cells(1)
    Dim DescColumn as TableCell = e.Item.Cells(2)
    
    Dim buttonColText as String = buttonColumn.Text
    Dim FAQIDColText as String = FAQIDColumn.Text
    Dim DescColText as String = DescColumn.Text
End Sub

Custom Sorting¶

Preparing a DataGrid for Sorting¶

1. Set the AllowSorting property of the DataGrid to True. If the DataGrid's AutoGenerateColumns property is set to False, specify the SortExpression property in the BoundColumn controls that represent the rows you'd like the user to be able to sort on.
2. Create a sort event handler that repopulates the DataGrid with the sorted data, and specify the name of this sort event handler in the DataGrid's OnSortCommand event.
3. Create a function that will allow you to grab the data from your data store in sorted format.
   

<asp:DataGrid runat="server" id="id" 
            AllowSorting="True"
            ... >
  ...
</asp:DataGrid> 

<asp:DataGrid runat="server" id="id" 
              AutoGenerateColumns="False"
              AllowSorting="True">  
  <Columns>
    <asp:BoundColumn DataField="dbField"  />
    <asp:BoundColumn DataField="dbField2" 
                  SortExpression="dbField2"  />
  </Columns>
</asp:datagrid>

Creating a Sort Event Handler¶

Sub SortEventHandler(sender as Object, e as DataGridSortCommandEventArgs)
   ...
End Sub

• Inside your event handler you can retrieve the name of the column that was sorted via e.SortExpression.
• If you explicitly specified the SortExpression property in a BoundColumn, then the value of e.SortExpression is the value of the SortExpression property; if you did not specify a SortExpression property (because you had AutoGenerateColumns set to True), then the value of e.SortExpression is the value of the database column name that represents the column clicked.
• To associate your sort event handler with the DataGrid's sort event, simply set the DataGrid's OnSortCommand property to the name of the sort event handler you created, like this.
  

<asp:DataGrid runat="server" id="id" 
                AllowSorting="True"
                OnSortCommand="NameOfSortEventHandler">
  ...
</asp:DataGrid>

Creating a Sorting Function¶

Sub SortData(sender as Object, e as DataGridSortCommandEventArgs)
    BindData(e.SortExpression)
End Sub 

Specifying the Sort Order Using a Stored Procedure¶

Template Columns¶

Using a TemplateColumn¶

• HeaderText - specifies the HTML content to appear in the column's header
• Visible - specifies if the column is rendered or not.
  

• ItemTemplate - specifies the template for each row for the particular column the TemplateColumn represents.
• HeaderTemplate - specifies the template for the column's header.
• FooterTemplate - specifies the template for the column's footer.
• EditItemTemplate - specifies the template for the a cell of a particular row that has been selected for editing. This can occur when using the DataGrid's Edit/Update/Delete features - see below for details on these features.
  

<%# DataBinder.Eval(Container.DataItem, "ColumnName") %> 

<asp:TemplateColumn HeaderText="FAQ Information">
  <ItemTemplate>
    <b><%# DataBinder.Eval(Container.DataItem, "ColumnName") %></b>
  </ItemTemplate>
</asp:TemplateColumn>

TemplateColumn versus a BoundColumn¶

<asp:datagrid id="dgPopularFAQs" runat="server"
	AutoGenerateColumns="False">	
  <Columns>
    <asp:BoundColumn DataField="FAQID" ItemStyle-Width="10%" 
			ItemStyle-HorizontalAlign="Center" HeaderText="FAQ ID" />
	    
    <asp:TemplateColumn HeaderText="FAQ Information">
      <ItemTemplate>
        <table border="0">
          <tr>
            <td align="right"><b>Description:</b></td>
            <td><%# DataBinder.Eval(Container.DataItem, "Description") %></td>
          </tr>
          <tr>
            <td align="right"><b>Category Name:</b></td>
            <td><%# DataBinder.Eval(Container.DataItem, "CatName") %></td>
          </tr>
          <tr>
            <td align="right"><b>View Count:</b></td>
            <td><%# DataBinder.Eval(Container.DataItem, "ViewCount", "{0:#,###}") %>
            </td>
          </tr>
        </table>
      </ItemTemplate>
    </asp:TemplateColumn>
  </Columns>	
</asp:datagrid>

Default Editing¶

First Things First: Displaying the Data¶

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
< script runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
   If Not Page.IsPostBack
      BindData()
   End If
  End Sub


  Sub BindData()
    '1. Create a connection
    Const strConnStr as String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                        "Data Source=C:\GrocerToGo.mdb"
    Dim objConn as New OleDbConnection(strConnStr)
    objConn.Open()
      
    '2. Create a command object for the query
    Const strSQL as String = "SELECT * FROM Products"
    Dim objCmd as New OleDbCommand(strSQL, objConn)
      
    '3. Create/Populate the DataReader
    Dim objDR as OleDbDataReader
    objDR = objCmd.ExecuteReader()    
      
    dgProducts.DataSource = objDR
    dgProducts.DataBind()   
  End Sub
</script>

<asp:DataGrid id="dgProducts" runat="server"
    AutoGenerateColumns="False" CellPadding="4"
    HeaderStyle-BackColor="Black"
    HeaderStyle-ForeColor="White"
    HeaderStyle-HorizontalAlign="Center"
    HeaderStyle-Font-Bold="True">
        
    <Columns>
        <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" />
        <asp:BoundColumn HeaderText="Price" DataField="UnitPrice"
                ItemStyle-HorizontalAlign="Right"
                DataFormatString="{0:$#,###.##}" />
        <asp:BoundColumn HeaderText="Name" DataField="ProductName" />
        <asp:BoundColumn HeaderText="Description" 
                DataField="ProductDescription" />                        
    </Columns>        
</asp:DataGrid>

Allowing the End User to Edit a Row¶

<asp:DataGrid id="dgProducts" runat="server"
   AutoGenerateColumns="False" CellPadding="4"
   HeaderStyle-BackColor="Black"
   HeaderStyle-ForeColor="White"
   HeaderStyle-HorizontalAlign="Center"
   HeaderStyle-Font-Bold="True">
        
   <Columns>
    <asp:EditCommandColumn EditText="Edit Info" 
          ButtonType="PushButton"
          UpdateText="Update" CancelText="Cancel" />

       <asp:BoundColumn HeaderText="Product ID" DataField="ProductID"
               ReadOnly="True" />                
       <asp:BoundColumn HeaderText="Price" DataField="UnitPrice"
               ItemStyle-HorizontalAlign="Right"
               DataFormatString="{0:$#,###.##}" />
       <asp:BoundColumn HeaderText="Name" DataField="ProductName" />
       <asp:BoundColumn HeaderText="Description" 
               DataField="ProductDescription" />                        
   </Columns>        
</asp:DataGrid>

• ButtonType - specifies if a hyperlink (LinkButton, the default) or push button (PushButton) should be used
• EditText, UpdateText, and CancelText - the text for the Edit, Update, and Cancel buttons, respectively.
• HeaderText, ItemStyle, etc.
  

Selecting a Row to be Edited¶

Sub dgProducts_Edit(sender As Object, e As DataGridCommandEventArgs)
    dgProducts.EditItemIndex = e.Item.ItemIndex
    BindData()
End Sub

<asp:DataGrid id="dgProducts" runat="server"
   ...   
   OnEditCommand="dgProducts_Edit"
   ... >
        
   <Columns>
       ...
   </Columns>        
</asp:DataGrid>

<asp:DataGrid id="dgProducts" runat="server"
   ... >
        
   <Columns>
      <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" 
            ReadOnly="True" />
      ...
   </Columns>        
</asp:DataGrid>

<asp:DataGrid id="dgProducts" runat="server"
   ...   
   EditItemStyle-BackColor="#eeeeee"
   ... >
        
   <Columns>
       ...
   </Columns>        
</asp:DataGrid>

The Cancel Button¶

Sub dgProducts_Cancel(sender As Object, e As DataGridCommandEventArgs)
    dgProducts.EditItemIndex = -1
    BindData()
End Sub

The Update Button¶

Sub dgProducts_Update(sender As Object, e As DataGridCommandEventArgs)
   'Read in the values of the updated row
   Dim iProductID as Integer = e.Item.Cells(1).Text
   Dim dblPrice as Double = CType(e.Item.Cells(2).Controls(0), TextBox).Text
   Dim strName as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
   Dim strDesc as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text

    ...

...
        
    'Construct the SQL statement using Parameters
    Dim strSQL as String = _
      "UPDATE [Products] SET [ProductName] = @ProdName, " & _
      "[UnitPrice] = @UnitPrice, [ProductDescription] = @ProdDesc " & _
      "WHERE [ProductID] = @ProductID"

    Const strConnString as String = _
       "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\GrocerToGo.mdb"
    Dim objConn as New OleDbConnection(strConnString)
    objConn.Open()

    Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
    myCommand.CommandType = CommandType.Text

    ' Add Parameters to the SQL query
    Dim parameterProdName as OleDbParameter = _
               new OleDbParameter("@ProdName", OleDbType.VarWChar, 75)
    parameterProdName.Value = strName
    myCommand.Parameters.Add(parameterProdName)

    Dim parameterUnitPrice as OleDbParameter = _
               new OleDbParameter("@UnitPrice", OleDbType.Currency)
    parameterUnitPrice.Value = dblPrice
    myCommand.Parameters.Add(parameterUnitPrice)

    Dim parameterProdDesc as OleDbParameter = _
               new OleDbParameter("@ProdDesc", OleDbType.VarWChar)
    parameterProdDesc.Value = strDesc
    myCommand.Parameters.Add(parameterProdDesc)

    Dim parameterProdID as OleDbParameter = _
               new OleDbParameter("@ProductID", OleDbType.Integer)
    parameterProdID.Value = iProductID
    myCommand.Parameters.Add(parameterProdID)

    myCommand.ExecuteNonQuery()   'Execute the UPDATE query
    
    objConn.Close()   'Close the connection
  
  ...

...
   
   'Finally, set the EditItemIndex to -1 and rebind the DataGrid
    dgProducts.EditItemIndex = -1
    BindData()        
End Sub

<asp:DataGrid id="dgProducts" runat="server"
   ...   
   OnUpdateCommand="dgProducts_Update"
   OnCancelCommand="dgProducts_Cancel"
   ... >
        
   <Columns>
       ...
   </Columns>        
</asp:DataGrid>

Custom Editing¶

The EditItemTemplate¶

    <asp:TemplateColumn HeaderText="Category">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "CategoryName") %>
      </ItemTemplate>
      
      <EditItemTemplate>
        <asp:DropDownList runat="server" id="lstCategories"
                DataValueField="FAQCategoryID"
                DataTextField="Name"
                DataSource="???" />
       </EditItemTemplate>                
    </asp:TemplateColumn>

Specifying the DropDownList's DataSource¶

    <asp:TemplateColumn HeaderText="Category">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "CategoryName") %>
      </ItemTemplate>
      
      <EditItemTemplate>
        <asp:DropDownList runat="server" id="lstCategories"
                DataValueField="FAQCategoryID"
                DataTextField="Name"
                DataSource="<%# GetCategories() %>" />
       </EditItemTemplate>               
    </asp:TemplateColumn>

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
< script language="vb" runat="server">
  'Create a connection
  Dim myConnection as New SqlConnection(connString)
  Dim ddlDataSet as DataSet = New DataSet()

  Function GetCategories() as DataSet
    'Populate the ddlDataSet
    Const strSQLDDL as String = _
         "SELECT FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name"    
    
    Dim myDataAdapter as SqlDataAdapter = New _
                          SqlDataAdapter(strSQLDDL, myConnection)    
    
    myDataAdapter.Fill(ddlDataSet, "Categories")

    Return ddlDataSet
  End Function

  ...

Selecting the Correct Item in the DropDownList¶

<asp:TemplateColumn HeaderText="Category">
  <ItemTemplate>
    <%# DataBinder.Eval(Container.DataItem, "CategoryName") %>
  </ItemTemplate>
      
  <EditItemTemplate>
    <asp:DropDownList runat="server" id="lstCategories"
         DataValueField="FAQCategoryID"
         DataTextField="Name"
         DataSource="<%# GetCategories() %>" 
         SelectedIndex='<%# GetSelIndex(Container.DataItem("FAQCategoryID")) %>'
    />
   </EditItemTemplate>               
</asp:TemplateColumn>

Function GetSelIndex(CatID as String) as Integer
  Dim iLoop as Integer
  
  'Loop through each row in the DataSet
  Dim dt as DataTable = ddlDataSet.Tables("Categories")
  For iLoop = 0 to dt.Rows.Count - 1
    If Int32.Parse(CatID) = _
          Int32.Parse(dt.Rows(iLoop)("FAQCategoryID")) then
      Return iLoop
    End If
  Next iLoop
End Function

Retrieving the DropDownList's Value¶

  Sub dgPopFAQs_Update(sender As Object, e As DataGridCommandEventArgs)
    'Determine what category was selected
    Dim strCategoryID as String, strCategoryName as String    
    strCategoryID = CType(e.Item.FindControl("lstCategories"), _
                          DropDownList).SelectedItem.Value
    strCategoryName = CType(e.Item.FindControl("lstCategories"), _
                            DropDownList).SelectedItem.Text
    
    ... Make a SQL call to update the database ...
  
    'Return the DataGrid to its pre-editing state
    dgPopularFAQs.EditItemIndex = -1
    BindData()
  End Sub

Deleting Records¶

Introduction¶

1. Create a ButtonColumn that contains a Delete button.
2. Somehow be able to determine when the Delete button has been clicked and have some server-side code ready to execute.
3. Be able to determine the primary key field value for the row whose Delete button has been clicked. We need to primary key field value so that we can issue a SQL statement to delete the selected row.
   

Creating a Delete Button¶

Sub eventHandlerName(sender as Object, e as DataGridCommandEventArgs) 
...
End Sub 

< script language="vb" runat="server">
  ...

  Sub dgPopularFAQs_Delete(sender As Object, e As DataGridCommandEventArgs)
    ' Place code to perform delete here...
  End Sub
</script>

<form runat="server">
  <asp:datagrid id="dgPopularFAQs" runat="server"
  	  ...
      OnDeleteCommand="dgPopularFAQs_Delete">
	
    <Columns>
  	<asp:ButtonColumn Text="Delete" CommandName="Delete" />
  	<asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID"
  	     ItemStyle-HorizontalAlign="Center" />
  	<asp:BoundColumn DataField="Description" HeaderText="Question" />
    </Columns>	
  </asp:datagrid>
</form>

Alternate Method of Determining the Clicked Row's Primary Key¶

< script language="vb" runat="server">
  ...

  Sub dgPopularFAQs_Delete(sender As Object, e As DataGridCommandEventArgs)
    'Get the FAQID of the row whose Delete button was clicked
    Dim SelectedFAQID as String = dgPopularFAQs.DataKeys(e.Item.ItemIndex)
    
    'TODO: Delete the record from the database
    
    'TODO: Rebind the DataGrid
  End Sub
</script>

<form runat="server">
  <asp:datagrid id="dgPopularFAQs" runat="server"
  	  ...
      DataKeyField="FAQID">

    ...	
  </asp:datagrid>
</form>

Adding a Client-Side Confirmation¶

< script language="vb" runat="server">
  Sub Page_Load(sender As Object, e As EventArgs)
    'Set the button's client-side onmouseover event
    btnClick.Attributes("onmouseover") = "alert('Hello, World!');"
  End Sub
</script>

<form runat="server">
  <asp:button runat="server" Text="Click Me!" id="btnClick" />
</form>

Sub eventHandlerName(sender as Object, e as DataGridItemEventArgs) 
...
End Sub 

< script language="vb" runat="server">
  ...
   
  Sub dgPopularFAQs_ItemDataBound(sender as Object, e as DataGridItemEventArgs)
    ' First, make sure we're NOT dealing with a Header or Footer row
    If e.Item.ItemType <> ListItemType.Header AND _
         e.Item.ItemType <> ListItemType.Footer then
      'Now, reference the LinkButton control that the Delete ButtonColumn 
      'has been rendered to
      Dim deleteButton as LinkButton = e.Item.Cells(0).Controls(0)

      'We can now add the onclick event handler
      deleteButton.Attributes("onclick") = "javascript:return " & _
                 "confirm('Are you sure you want to delete FAQ #" & _
                 DataBinder.Eval(e.Item.DataItem, "FAQID") & "?')"    
    End If
  End Sub
</script>

<form runat="server">
  <asp:datagrid id="dgPopularFAQs" runat="server"
      ...
      OnItemDataBound="dgPopularFAQs_ItemDataBound">
	
      ...
  </asp:datagrid>
</form>

Setting Focus¶

Setting the Focus to a Control¶

<input type="text" id="textBoxID" />

<html>
<head>
  < script language="JavaScript">
    function setFocus()
    {
      frmInfo.name.focus();
    }
  </script>
</head>
<body onload="setFocus();">
  <form id="frmInfo">
    Name: <input type="text" id="name" /><br />
    Age:  <input type="text" id="age" />
  </form>
</body>
</html>

Declaring our DataGrid¶

<form runat="server" id="frmEditFAQs">
  <asp:datagrid id="dgPopularFAQs" runat="server"
    AutoGenerateColumns="False"
    ...		
    OnEditCommand="dgPopularFAQs_Edit"
    OnUpdateCommand="dgPopularFAQs_Update"
    OnCancelCommand="dgPopularFAQs_Cancel">
	
    <Columns>
      <asp:EditCommandColumn EditText="Edit" UpdateText="Update" 
                              CancelText="Cancel" />
  	  <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID"
  	       ItemStyle-HorizontalAlign="Center" ReadOnly="True" />
  	  <asp:TemplateColumn HeaderText="Question">
        <ItemTemplate>
          <%# DataBinder.Eval(Container.DataItem, "Description") %>
        </ItemTemplate>
		  
        <EditItemTemplate>
          <asp:TextBox id="txtDesc" runat="server" Width="95%"
             Text='<%# DataBinder.Eval(Container.DataItem, "Description") %>' />
        </EditItemTemplate>
      </asp:TemplateColumn>
    </Columns>	
  </asp:datagrid>
</form>

Dynamically Adding Client-Side Code¶

Sub dgPopularFAQs_Edit(sender as Object, e as DataGridCommandEventArgs)
  dgPopularFAQs.EditItemIndex = e.Item.ItemIndex
  BindData()
    
  ' Create a reference to the TextBox
  Dim descTB as TextBox
  descTB = dgPopularFAQs.Items(e.Item.ItemIndex).Cells(2).FindControl("txtDesc")

  'Set the script to focus and select the TextBox
  RegisterStartupScript("focus", "< script language=""JavaScript"">" & vbCrLf & _
       vbTab & "frmEditFAQs." & descTB.ClientID & ".focus();" & _
       vbCrLf & vbTab & "frmEditFAQs." & descTB.ClientID & ".select();" & _
       vbCrLf & "<" & "/script>")
End Sub

< script language="JavaScript">
	frmEditFAQs.dgPopularFAQs__ctl12_txtDesc.focus();
	frmEditFAQs.dgPopularFAQs__ctl12_txtDesc.select();
</script>

Automatic Filtering¶

Changing the BindData Subroutine¶

1. Retrieves the data to display in the DataGrid from the SQL database that houses the ASPFAQs.com database, placing this data in a SqlDataReader.
2. Specifies that the DataGrid's DataSource property should be assigned the SqlDataReader from the previous step.
3. Calls the DataGrid's DataBind() method.
   

SELECT Columns
FROM tblFAQ
WHERE FAQCategoryID = @FAQCategoryIDParameter 

Sub BindData(FilterOnFAQCategoryID as Integer)

  '2. Create the command object, passing in the SQL string
  Dim strSQL as String 
  strSQL = "SELECT FAQID, F.FAQCategoryID, F.Description, " & _
                  "FC.Name AS CategoryName " & _
           "FROM tblFAQ F " & _
             "INNER JOIN tblFAQCategory FC ON " & _
                "F.FAQCategoryID = FC.FAQCategoryID " & _
           "WHERE F.FAQCategoryID = @FAQCatID " & _
           "ORDER BY FAQID"

  'Set the datagrid's datasource to the datareader and databind
  Dim myConnection as New _
      SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
  Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) 
    
  Dim FAQCatIDParam as New SqlParameter("@FAQCatID", SqlDbType.Int, 4)
  FAQCatIDParam.Value = FilterOnFAQCategoryID
  myCommand.Parameters.Add(FAQCatIDParam)
    
  myConnection.Open()
  dgPopularFAQs.DataSource = myCommand.ExecuteReader()
  dgPopularFAQs.DataBind()
  myConnection.Close()
End Sub

Displaying Hyperlinks for Each FAQ Category¶

<form runat="server">
  <asp:LinkButton runat="server" Text="Application Object"
        CommandArgument="7" OnCommand="FilterData" />
  <asp:LinkButton runat="server" Text="Arrays"
        CommandArgument="1" OnCommand="FilterData" />
  ...
  <asp:LinkButton runat="server" Text="Uploading"
        CommandArgument="19" OnCommand="FilterData" />


  <asp:DataGrid runat="server" id="dgFilteredFAQs" ...>
    ...
  </asp:DataGrid>
</form>

  Sub FilterData(sender as Object, e as CommandEventArgs)
	BindData(e.CommandArgument)
  End Sub

Removing the Hardcoded LinkButtons¶

Sub AddFilterButtons()
  'Get a list of the categories
  '2. Create the command object, passing in the SQL string
  Dim strSQL as String 
  strSQL = "SELECT FAQCategoryID, Name AS CategoryName " & _
           "FROM tblFAQCategory " & _
           "ORDER BY CategoryName"

  'Set the datagrid's datasource to the datareader and databind
  Dim myConnection as New _
      SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
  Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) 
    
  myConnection.Open()
  Dim myDataReader as SqlDataReader = myCommand.ExecuteReader()
    
  'Loop through the results, creating a LinkButton for each row
  Dim filterButton as LinkButton
  While myDataReader.Read()
    'Add the LinkButton
    filterButton = New LinkButton()
    filterButton.Text = myDataReader("CategoryName")
    filterButton.CommandArgument = myDataReader("FAQCategoryID")
    AddHandler filterButton.Command, AddressOf Me.FilterData      
      
    phFilterLinkButtons.Controls.Add(filterButton)
      
    'Add some white space
    phFilterLinkButtons.Controls.Add(New LiteralControl(" | "))
  End While
    
  myDataReader.Close()
  myConnection.Close()    
End Sub

Radio Buttons¶

Introduction¶

Why the TemplateColumn Approach Won't Work¶

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" ... >
    <Columns>
      <asp:TemplateColumn>
        <ItemTemplate>
          <asp:RadioButton runat="server" 
                 GroupName="ThisDoesntWork" id="NeitherDoesThis" />
        </ItemTemplate>
      </asp:TemplateColumn>
      <asp:BoundColumn ... />
      ...
    </Columns>
  </asp:datagrid>
</form>

<table cellspacing="0" cellpadding="4" ...>
<tr align="Center" ...>
	<td>&nbsp;</td><td>FAQ ID</td><td>FAQ Description</td>
</tr><tr>
	<td>
        <input id="dgPopularFAQs__ctl2_NeitherDoesThis" 
               type="radio" 
               name="dgPopularFAQs:_ctl2:ThisDoesntWork" 
               value="NeitherDoesThis" />
      </td><td>144</td><td>Where can I host my ASP Web ...</td>
</tr><tr style="background-color:White;">
	<td>
        <input id="dgPopularFAQs__ctl3_NeitherDoesThis" 
               type="radio" 
               name="dgPopularFAQs:_ctl3:ThisDoesntWork" 
               value="NeitherDoesThis" />
      </td><td>115</td><td>I am using Access and ...</td>
</tr><tr>
	<td>
        <input id="dgPopularFAQs__ctl4_NeitherDoesThis" 
               type="radio" 
               name="dgPopularFAQs:_ctl4:ThisDoesntWork" 
               value="NeitherDoesThis" />
      </td><td>161</td><td>How can I convert a Recordset ...</td>
</tr>
...

Making Each Radio Button's Name Identical¶

<%@ Register TagPrefix="prefix" Namespace="MetaBuilders.WebControls"
    Assembly="MetaBuilders.WebControls.RowSelectorColumn" %> 

Creating a Column of Radio Buttons in a DataGrid¶

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<%@ Register TagPrefix="mbrsc" Namespace="MetaBuilders.WebControls" 
         Assembly="MetaBuilders.WebControls.RowSelectorColumn" %>
< script language="vb" runat="server">
  ...

  Sub RecordVote(sender as Object, e as EventArgs)
    Dim rsc as RowSelectorColumn = RowSelectorColumn.FindColumn(dgPopularFAQs)
    
    If rsc.SelectedIndexes.Length = 0 then
      lblVoteResults.Text = "You did not select a FAQ!"
    Else
      Dim selIndex as Integer = rsc.SelectedIndexes(0)
      lblVoteResults.Text = "You voted for item " & selIndex & _
              ", which is the FAQ with FAQID " & _
              dgPopularFAQs.DataKeys(selIndex) & "<p>"
    End If
  End Sub
</script>

<form runat="server">
   <h3>Vote for Your Favorite FAQ!</h3>
   <asp:Label runat="server" id="lblVoteResults" ForeColor="Red"
       Font-Italic="True" Font-Size="14pt" />

  <asp:DataGrid runat="server" id="dgPopularFAQs"
                  ...
                  DataKeyField="FAQID"
                  AutoGenerateColumns="False">
    <Columns>
      <mbrsc:RowSelectorColumn SelectionMode="Single" />
      <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID" />
      <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    </Columns>
  </asp:datagrid><br />
  <asp:Button runat="server" onclick="RecordVote" text="Record Vote" />
</form>

<mbrsc:RowSelectorColumn SelectionMode="Single" /> 

Creating a Column of CheckBoxes in a DataGrid¶

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<%@ Register TagPrefix="mbrsc" Namespace="MetaBuilders.WebControls" 
         Assembly="MetaBuilders.WebControls.RowSelectorColumn" %>
< script language="vb" runat="server">
  ...

  Sub RecordVote(sender as Object, e as EventArgs)
    Dim rsc as RowSelectorColumn = RowSelectorColumn.FindColumn(dgPopularFAQs)
    
    Dim selIndex as Integer
    For Each selIndex in rsc.SelectedIndexes
      lblVoteResults.Text &= "You chose item " & selIndex & _
              ", which is the FAQ with FAQID " & _
              dgPopularFAQs.DataKeys(selIndex) & "<br>"    
    Next
    
    lblVoteResults.Text &= "<p>"
  End Sub
</script>

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" 
                  ...
                  DataKeyField="FAQID"
                  AutoGenerateColumns="False">
    <Columns>
      <mbrsc:RowSelectorColumn AllowSelectAll="True" SelectionMode="Multiple" />
      <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID" />
      <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    </Columns>
  </asp:datagrid>
  <br />
  <asp:Button runat="server" onclick="RecordVote" text="Record Vote" />
</form>

Bi-Directional Sorting¶

Introduction¶

Displaying the Contents of the Authors Table¶

<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
< script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
  ' Only bind the data on the first visit to the page
  If Not Page.IsPostBack
    GetAuthors("au_fname asc")
  End If
End Sub

Sub GetAuthors(sSortStr as string)
  Dim cn as SQLConnection
  Dim cmd as SQLCommand
  Dim rdr as SQLDataReader
  Dim sConnectString as String = "server=localhost;database=pubs;user id=sa"
  Dim sSql as String = "SELECT au_fname, au_lname, city, state, zip " & _
                       "from authors order by " & sSortStr
  
  ' Connect to the database
  cn = New SQLConnection(sConnectString)
  cn.open()

  ' execute the SQL 
  cmd = New SQLCommand(sSQL, cn)
  rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

  ' Associate the data grid with the data
  DispAuthors.DataSource = rdr
  DispAuthors.DataBind()
End Sub

' this method is called when user clicks on any of the column headings
Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs)
  '... Code coming soon! :-) ...
End Sub
</script>

<form runat="server">
  <ASP:Datagrid id="DispAuthors" runat="server" AutoGenerateColumns="False" 
        AllowSorting="true" onSortCommand="SortAuthors">
    ...
    <Columns>
        <asp:BoundColumn DataField="au_fname" SortExpression="au_fname ASC" 
                                                   HeaderText="First Name" />
        <asp:BoundColumn DataField="au_lname" SortExpression="au_lname" 
                                                   HeaderText="Last Name" />
        <asp:BoundColumn DataField="city" HeaderText="City" />
        <asp:BoundColumn DataField="state" SortExpression="state" 
                                                   HeaderText="State" />
        <asp:BoundColumn DataField="zip" HeaderText="Zip" />
    </Columns>
  </asp:datagrid>
</form>

1. SortAuthors() is the event handler invoked when user clicks on a column header. The source code for this event handler has yet to be provided. We'll be examining the needed code soon.
2. Only the first name, last name and state columns have their SortExpression property specified. This means that only these columns are sortable. Also note that the SortExpression property, along with the column name to sort, also contains information on the sort order (i.e., the First Name column's SortExpression property is "au_fname ASC").
   

Writing the DataGrid's Sort Event Handler¶

Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs)
  Dim SortExprs() As String
  Dim CurrentSearchMode As String, NewSearchMode As String
  Dim ColumnToSort As String, NewSortExpr as String

  '  Parse the sort expression - delimiter space
  SortExprs = Split(e.SortExpression, " ")  
  ColumnToSort = SortExprs(0)

  ' If a sort order is specified get it, else default is descending
  If SortExprs.Length() > 1 Then
      CurrentSearchMode = SortExprs(1).ToUpper()
      If CurrentSearchMode = "ASC" Then
         NewSearchMode = "Desc"
      Else
         NewSearchMode = "Asc"
      End If
  Else   ' If no mode specified, Default is descending
      NewSearchMode = "Desc"
  End If

  '  Derive the new sort expression. 
  NewSortExpr = ColumnToSort & " " & NewSearchMode

  ' Figure out the column index 
  Dim iIndex As Integer
  Select Case ColumnToSort.toUpper()
    case "AU_FNAME"
      iIndex = 0
    case "AU_LNAME"
      iIndex = 1
    case "STATE"
      iIndex = 3
  End Select

  ' alter the column's sort expression 
  DispAuthors.Columns(iIndex).SortExpression = NewSortExpr

  ' Sort the data in new order
  GetAuthors(NewSortExpr)
End Sub

Displaying a Sum in a Footer¶

Computing the Sum of a Column¶

SELECT SUM(ViewCount)
FROM tblFAQs

< script language="VB" runat="server">
  Dim viewCountSum as Integer = 0
  
  Sub ComputeSum(sender As Object, e As DataGridItemEventArgs)
    'First, make sure we are dealing with an Item or AlternatingItem
    If e.Item.ItemType = ListItemType.Item OR _
          e.Item.ItemType = ListItemType.AlternatingItem then
      'Snip out the ViewCount
      Dim viewCount as Integer = _
         Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "ViewCount"))
      viewCountSum += viewCount
    End If
  End Sub
</script>

<asp:DataGrid runat="server" id="dgPopularFAQs"
       ...
       OnItemDataBound="ComputeSum">
         
  <Columns>
    <asp:BoundColumn DataField="Description" HeaderText="Description" />
    <asp:BoundColumn DataField="ViewCount" HeaderText="Page Views" 
              DataFormatString="{0:#,###}" ItemStyle-HorizontalAlign="Right" 
              ItemStyle-Width="15%" />
  </Columns>
</asp:DataGrid>

Displaying the Sum in the DataGrid's Footer¶

  Sub ComputeSum(sender As Object, e As DataGridItemEventArgs)
    'First, make sure we are dealing with an Item or AlternatingItem
    If e.Item.ItemType = ListItemType.Item OR _
          e.Item.ItemType = ListItemType.AlternatingItem then
      'Snip out the ViewCount
      Dim viewCount as Integer = _
         Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "ViewCount"))
      viewCountSum += viewCount
    ElseIf e.Item.ItemType = ListItemType.Footer then
      e.Item.Cells(1).Text = "Total: " & String.Format("{0:#,###}", viewCountSum)
    End If
  End Sub

<asp:DataGrid runat="server" id="dgPopularFAQs"
       ...
       ShowFooter="True"
       OnItemDataBound="ComputeSum">
         
   ...
</asp:DataGrid>

Master/Detail Grids¶

Introduction¶

Listing the FAQ Categories¶

<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
< script language="VB" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New _
       SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Dim strSQL as String = _
         "SELECT FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to the datareader and databind
    myConnection.Open()
    dgMasterDetail.DataSource = _
           myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    dgMasterDetail.DataBind()
    myConnection.Close()
  End Sub
</script>

<asp:DataGrid runat="server" id="dgMasterDetail"
       AutoGenerateColumns="False"
       ...>
         
 <Columns>
    <asp:BoundColumn DataField="Name" HeaderText="FAQ Category" />
    <asp:TemplateColumn HeaderText="FAQs">
      <ItemTemplate>
        <i>The FAQs for the FAQCategory will go here...
      </ItemTemplate>
    </asp:TemplateColumn>
  </Columns>
</asp:DataGrid>

Displaying the FAQs for a Particular Category¶

< script language="VB" runat="server">
  ...	
	
  Sub buildFAQsDataGrid(sender as Object, e as DataGridItemEventArgs)
    If e.Item.ItemType = ListItemType.Item OR _
            e.Item.ItemType = ListItemType.AlternatingItem then
      'Build the DataGrid
      Dim dg as New DataGrid()
      
      'Find out the CategoryID
      Dim CatID as Integer = e.Item.DataItem("FAQCategoryID")
      
      'Bind the Data to the DataGrid
      dg.DataSource = GetFAQsByCategoryID(CatID)
      dg.DataBind()
      
      'Add the DataGrid to the 2nd Column
      e.Item.Cells(1).Controls.Add(dg)
    End If
  End Sub
</script>

<asp:DataGrid runat="server" id="dgFAQsByCategory"
       AutoGenerateColumns="False"
       Font-Name="Verdana" Width="85%"
       Font-Size="11pt" HorizontalAlign="Center"
       ShowFooter="True"
       OnItemDataBound="buildFAQsDataGrid">
   ...
</asp:DataGrid>

Filtering Data with a DataView¶

< script language="VB" runat="server">
  ...	
	
  Sub buildFAQsDataGrid(sender as Object, e as DataGridItemEventArgs)
    If e.Item.ItemType = ListItemType.Item OR _
            e.Item.ItemType = ListItemType.AlternatingItem then
      'Build the DataGrid
      Dim dg as New DataGrid()
      
      'Find out the CategoryID
      Dim CatID as Integer = e.Item.DataItem("FAQCategoryID")
      
      'Create a DataView that has only the applicable FAQs
      Dim properFAQs as DataView = FAQsDS.Tables("FAQs").DefaultView
      properFAQs.RowFilter = "FAQCategoryID=" & CatID
      
      'Bind the Data to the DataGrid
      dg.DataSource = properFAQs
      dg.DataBind()
      
      'Add the DataGrid to the 2nd Column
      e.Item.Cells(1).Controls.Add(dg)
    End If  End Sub
</script>

< script language="VB" runat="server">
  ...	
	
  Dim FAQsDS as New DataSet()
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New 
        _SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Dim strCategorySQL, strFAQSQL as String 
    strCategorySQL = "SELECT FAQCategoryID, Name " & _
                     "FROM tblFAQCategory ORDER BY Name"
    
    strFAQSQL = "SELECT FAQID, FAQCategoryID, Description, ViewCount " & _
                "FROM tblFAQ ORDER BY Description"
                
    Dim myCatCommand as New SqlCommand(strCategorySQL, myConnection)
    Dim myCatDA as New SqlDataAdapter(myCatCommand)
    
    Dim myFAQCommand as New SqlCommand(strFAQSQL, myConnection)    
    Dim myFAQDA as New SqlDataAdapter(myFAQCommand)
    
    'Fill the dataset
    myConnection.Open()
    myCatDA.Fill(FAQsDS, "Categories")
    myFAQDA.Fill(FAQsDS, "FAQs")
    myConnection.Close()
    
    'Bind the Categories DataSet to the DataGrid
    dgFAQsByCategory.DataSource = FAQsDS.Tables("Categories")
    dgFAQsByCategory.DataBind()
  End Sub
</script>

Data Paging¶

Introduction¶

1. Readers cannot be expected to be able to digest such large blocks of information.
2. Dumping large amounts of data in one Web page leads to large Web page sizes, which can take unbearably long to download on dial-up connections.
   

Paging Support in the DataGrid¶

Understanding Paging¶

• CurrentPageIndex - indicates what zero-based page of data to display. This property's default value is 0, so by default the first page of data will be displayed.
• PageSize - indicates how many records to show per page.
• {806} - indicates how many total pages of data there are. This value is computed by the ceiling of the number of total records divided by the number of records per page.
• AllowPaging - this Boolean property indicates whether or not the DataGrid is pageable. In order to use the DataGrid's built-in paging features, you need to set this to True.
  

Working with Default Paging¶

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
< script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub
	
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(connection string)

    '2. Create the command object, passing in the SQL string
    Dim strSQL as String = "SELECT FAQID, Description " & _
                           "FROM tblFAQs ORDER BY FAQID"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to the datareader and databind
    myConnection.Open()
    dgPopularFAQs.DataSource = myCommand.ExecuteReader()
    dgPopularFAQs.DataBind()	
    
    myConnection.Close()
  End Sub
</script>

<asp:DataGrid runat="server" id="dgFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana"
                Font-Size="10pt">
  <HeaderStyle BackColor="Black" ForeColor="White" Font-Bold="True"
		 HorizontalAlign="Center" />
  <AlternatingItemStyle BackColor="White" />
</asp:DataGrid>

1. Set the DataGrid's AllowPaging property to True.
2. Place the DataGrid within a Web Form (the < form runat="server" >). This is needed because navigating through each of the DataGrid's pages of data incurs a postback.
3. Update the BindData() method to use a DataSet instead of a SqlDataReader.
4. Add an event handler for the DataGrid's PageIndexChanged event.
   

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
< script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub	
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(connection string)

    '2. Create the command object, passing in the SQL string
    Dim strSQL as String = "SELECT FAQID, Description " & _
                           "FROM tblFAQs ORDER BY FAQID"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

	Dim myAdapter as New SqlDataAdapter(myCommand)
	Dim ds as New DataSet()
	myAdapter.Fill(ds)
    
    'Set the datagrid's datasource to the DataSet and databind    
    dgPopularFAQs.DataSource = ds
    dgPopularFAQs.DataBind()	
    
    myConnection.Close()
  End Sub  
  
  Sub NewPage(sender As Object, e As DataGridPageChangedEventArgs) 
    dgPopularFAQs.CurrentPageIndex = e.NewPageIndex
    BindData()      
  End Sub

</script>

<form runat="server">
	<asp:DataGrid runat="server" id="dgPopularFAQs" 
	                BackColor="#eeeeee" Width="85%"
	                HorizontalAlign="Center"
	                Font-Name="Verdana"
	                Font-Size="10pt"
	                AllowPaging="True"
	                OnPageIndexChanged="NewPage">
	  <HeaderStyle BackColor="Black" ForeColor="White" Font-Bold="True" 
	                  HorizontalAlign="Center" />
	  <AlternatingItemStyle BackColor="White" />
	</asp:datagrid>
</form>

Customizing the Navigational Interface¶

1. NextPrev - in this paging style, the user can move just to the immediately next or immediately previous page. This is the default.
2. NumericPages - in this paging style, the user can jump to a particular page. You can customize this paging style by setting the NextPageText and PrevPageText properties to the text that you want to appear for the next and previous hyperlinks.
   

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" 
               ...>
      ...
      <PagerStyle NextPageText="Next --&gt;" PrevPageText="&lt;-- Prev."
               HorizontalAlign="Right" />
  </asp:datagrid>
</form>

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" 
               ...>
      ...
      <PagerStyle Mode="NumericPages" HorizontalAlign="Center" />
  </asp:datagrid>
</form>

Editable Grid With Cascading DropDownLists¶

Introduction¶

Examining a Sample Data Model¶

Displaying Employee Information in a DataGrid¶

SELECT 
     EmployeeID          -- Employee info
    ,e.Name
    ,d.DepartmentID      -- Department info
    ,d.Name as DeptName
    ,dv.DivisionID       -- Division info
    ,dv.Name as DivName

FROM 
    Employees e

    INNER JOIN Departments d ON
        d.DepartmentID = e.DepartmentID

    INNER JOIN Divisions dv ON
        dv.DivisionID = d.DividsionID

ORDER BY 
    e.Name

<asp:DataGrid runat="server" ... AutoGenerateColumns="False">
  <Columns>
    <asp:EditCommandColumn ... />
    <asp:BoundColumn DataField="Name" HeaderText="Name" />
    <asp:TemplateColumn HeaderText="Division">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "DivName") %>
      </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="Department">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "DeptName") %>
      </ItemTemplate>
    </asp:TemplateColumn>
  </Columns>
</asp:DataGrid>

Creating the EditItemTemplate¶

  <asp:TemplateColumn HeaderText="Division">
    <ItemTemplate>
      <%# DataBinder.Eval(Container.DataItem, "DivName") %>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:DropDownList runat="server" id="ddlDivision" AutoPostBack="True"
              OnSelectedIndexChanged="ddlDivision_SelectedIndexChanged"
              DataTextField="Name" DataValueField="DivisionID" />
    </EditItemTemplate>
  </asp:TemplateColumn>
  <asp:TemplateColumn HeaderText="Division">
    <ItemTemplate>
      <%# DataBinder.Eval(Container.DataItem, "DeptName") %>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:DropDownList runat="server" id="ddlDepartment"
               DataTextField="Name" DataValueField="DepartmentID" />
    </EditItemTemplate>
  </asp:TemplateColumn>

Populating the DropDownLists¶

1. When the DataGrid is being placed in edit mode. This involves setting the EditItemIndex to the index of the row to be edited, and rebinding the data to the DataGrid. At this point, we need to populate the two DropDownLists and set their selected value based on the database data.
2. When the end user selects a different division. At this point we must populate the Department DropDownList based on the selected division.
   

private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
// see if we are working with the row being edited
if (e.Item.ItemType == ListItemType.EditItem)
{
// populate the division DDL
DropDownList ddlDivision = (DropDownList) e.Item.FindControl("ddlDivision");

// connect to Access DB
OleDbConnection myConnection = new OleDbConnection(
                    ConfigurationSettings.AppSettings["connString"]);
myConnection.Open();

const string SQL = @"SELECT DivisionID, Name FROM Divisions ORDER BY Name";
OleDbCommand myCommand = new OleDbCommand(SQL, myConnection);

ddlDivision.DataSource = myCommand.ExecuteReader();
ddlDivision.DataBind();

myConnection.Close();

// now, select the appropriate division
int currentDivisionID = Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "DivisionID"));
ListItem li = ddlDivision.Items.FindByValue(currentDivisionID.ToString());
if (li != null) li.Selected = true;

// finally, populate the department DDL based on the selected division
int currentDepartmentID = Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "DepartmentID"));
if (li != null) li.Selected = true;

DropDownList ddlDepartment = (DropDownList) e.Item.FindControl("ddlDepartment");

PopulateDepartmentBasedOnDivision(ddlDepartment, ddlDivision, currentDepartmentID);
}
}

private void PopulateDepartmentBasedOnDivision(DropDownList ddlDepartment, 
dropDownList ddlDivision, int currentDepartmentID)
{
int divID = Convert.ToInt32(ddlDivision.SelectedValue);

// connect to Access DB
OleDbConnection myConnection = new OleDbConnection(
                    ConfigurationSettings.AppSettings["connString"]);
myConnection.Open();

const string SQL = @"SELECT DepartmentID, Name FROM Departments 
                    WHERE DivisionID = @DivisionID ORDER BY Name";

OleDbCommand myCommand = new OleDbCommand(SQL, myConnection);
myCommand.Parameters.Add(new OleDbParameter("@DivisionID", divID));

ddlDepartment.DataSource = myCommand.ExecuteReader();
ddlDepartment.DataBind();

myConnection.Close();

// now, select the appropriate division
ListItem li = ddlDepartment.Items.FindByValue(currentDepartmentID.ToString());
if (li != null) li.Selected = true;
} 

protected void ddlDivision_SelectedIndexChanged(object sender, EventArgs e)
{
// get a reference to the Department DDL for this row
DropDownList ddlDivision = (DropDownList) sender;
DataGridItem dgi = (DataGridItem) ddlDivision.Parent.Parent;
DropDownList ddlDepartment = (DropDownList) dgi.FindControl("ddlDepartment");

PopulateDepartmentBasedOnDivision(ddlDepartment, ddlDivision, -1);
} 

Fully Editable Grid¶

Introduction¶

1. Using a TemplateColumn for each editable DataGrid column and adding the editable user interface in the ItemTemplates for each TemplateColumn,
2. Creating an "Update All Records" Button Web control that, when clicked, iterates through the DataGrid's items, sending an UPDATE statement to the database for each record.
   

Making the Entire DataGrid Editable¶

<asp:datagrid id="dgPopularFAQs" runat="server"
	AutoGenerateColumns="False"
	...>
	
  <Columns>
    <asp:BoundColumn DataField="FAQID" ItemStyle-Width="10%"
			ItemStyle-HorizontalAlign="Center" HeaderText="FAQ ID" />

    <asp:BoundColumn DataField="CategoryName" HeaderText="Category" />
	    
    <asp:TemplateColumn HeaderText="Question">
      <ItemTemplate>
        <asp:TextBox runat="server" id="txtDescription" Columns="75" 
             Text='<%# Container.DataItem("Description") %>' />
      </ItemTemplate>
    </asp:TemplateColumn>
	    
    <asp:TemplateColumn HeaderText="Submitted By">
      <ItemTemplate>
        <asp:TextBox runat="server" id="txtSubmittedBy"
             Text='<%# Container.DataItem("SubmittedByName") %>' />
      </ItemTemplate>
    </asp:TemplateColumn>

  </Columns>	
</asp:datagrid>

Saving All Changes to the Database¶

For Each record in the DataGrid
  Read in the values from the TemplateColumns
  
  Issue an UPDATE statement to the database
Next

'Create connection and command objects
Dim myConnection as New SqlConnection(connection string)
Dim myCommand as New SqlCommand(strSQL, myConnection)

Dim dgi as DataGridItem
For Each dgi in dgPopularFAQs.Items
  'Read in the Primary Key Field
  Dim id as Integer = Convert.ToInt32(dgPopularFAQs.DataKeys(dgi.ItemIndex))
  Dim question as String = CType(dgi.FindControl("txtDescription"), TextBox).Text
  Dim submittedBy as String = CType(dgi.FindControl("txtSubmittedBy"), TextBox).Text
      
  'Issue an UPDATE statement...
  Dim updateSQL as String = "UPDATE TableName SET Question = @Question, " & _
              "SubmittedByName = @SubmittedByName WHERE FAQID = @ID"
  myCommand.Parameters.Clear()
  myCommand.Parameters.Add("@Question", question)
  myCommand.Parameters.Add("@SubmittedByName", submittedBy)
  
  myCommand.ExecuteNonQuery()
Next

Bi-Directional Sorting Revisited¶

Introduction¶

Storing Sorting Information in the ViewState¶

'The Page-level properties that write to ViewState
Private Property SortExpression() As String
    Get
        Dim o As Object = viewstate("SortExpression")
        If o Is Nothing Then
            Return String.Empty
        Else
            Return o.ToString
        End If
    End Get
    Set(ByVal Value As String)
        viewstate("SortExpression") = Value
    End Set
End Property

Private Property SortAscending() As Boolean
    Get
        Dim o As Object = viewstate("SortAscending")
        If o Is Nothing Then
            Return True
        Else
            Return Convert.ToBoolean(o)
        End If
    End Get
    Set(ByVal Value As Boolean)
        viewstate("SortAscending") = Value
    End Set
End Property

'The SortCommand event handler
Private Sub dgPActivities_SortCommand(ByVal source As Object, _
   ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) _
   Handles dgPActivities.SortCommand
    'Toggle SortAscending if the column that the data was sorted by has
    'been clicked again...
    If e.SortExpression = Me.SortExpression Then 
      SortAscending = Not SortAscending
    Else
      SortAscending = True
    End If
    
    'Set the SortExpression property to the SortExpression passed in
    Me.SortExpression = e.SortExpression

    BindData()  'rebind the DataGrid data
End Sub

Improving the Appearance¶

Sub UpdateColumnHeaders(ByVal dg As DataGrid)
   Dim c As DataGridColumn
   For Each c In dg.Columns
      'Clear any <img> tags that might be present
      c.HeaderText = Regex.Replace(c.HeaderText, "\s<.*>", String.Empty)

      If c.SortExpression = SortExpression Then
          If SortAscending Then
             c.HeaderText &= " <img src=""/images/up.gif"" border=""0"">"
          Else
             c.HeaderText &= " <img src=""/images/down.gif"" border=""0"">"
          End If
      End If
   Next
End Sub

Conclusion (and Making this Approach Reusable)¶