IN THIS CHAPTER
The DataGrid
control is the most feature-rich control in the ASP.NET Framework. In this chapter, you’ll learn how to use the DataGrid
control to work with SQL Server database data. In particular, you’ll learn:
How to display data with the DataGrid
control
How to customize the appearance of the DataGrid
control
How to sort data with the DataGrid
control
How to page through data with the DataGrid
control
How to edit data with the DataGrid
control
Even
though the DataGrid
control is one of the most complicated controls in the ASP.NET Framework, it is also the easiest control to use for quickly displaying the contents of a database table. Unlike the Repeater
or DataList
control, the DataGrid
control does not require you to create a template to display the contents of a database table.
For example, suppose that you need to quickly display the contents of the Northwind Products database table (see Figure 13.1).
First, you’ll need to create the necessary database objects:
Add a Web Form Page named DisplayProductsGrid.aspx
to your project.
Drag and drop the Products database table from under the Northwind data connection in the Server Explorer window onto the Designer surface.
Add an Untyped DataSet
to the page.
Next, you need to add the DataGrid
control to the page and bind the DataGrid to the Products DataSet:
Drag and drop the DataGrid
control from under the Web Forms tab in the Toolbox onto the Designer surface.
Switch to the Code Editor by double-clicking the Designer surface.
Enter the following code for
the Page_Load
method:
C#.
private void Page_Load(object sender, System.EventArgs e) { sqlDataAdapter1.Fill( dataSet1 ); DataGrid1.DataSource = dataSet1; DataGrid1.DataBind(); }
VB.NET.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load SqlDataAdapter1.Fill(DataSet1) DataGrid1.DataSource = DataSet1 DataGrid1.DataBind() End Sub
Right-click the DisplayProductsGrid.aspx page in the Solution Explorer window and select Build and Browse.
When the DisplayProductsGrid.aspx page opens, all the columns and all the rows are displayed from the Products database table.
If you prefer, you can display only certain columns from the Products table instead of all columns. To do this, don’t drag and drop the Products database table onto the Designer surface. Instead, expand the Products table in the Server Explorer window, select one or more columns while holding down the Ctrl key, and drag the selected columns onto the Designer surface.
If you are using a DataGrid simply to display database data, it’s a good idea to turn off
View State for the DataGrid (you’re not using it and you are wasting bandwidth with View State enabled). Disable View State for the DataGrid by assigning the value False
to the DataGrid
control’s EnableViewState
property. To learn more about View State see Chapter 17, “Maintaining Application State.”
One problem with the page that we created in the previous section is that it is not very pretty. You wouldn’t want to use it in a production application. The column headings are named after the database table column names, and the content of the columns is not formatted. For example, the UnitPrice column is displayed as a decimal value (12.34) instead of a currency value ($12.34).
In this section, you’ll learn how to format a DataGrid by taking advantage of auto formatting and by explicitly defining the columns in a DataGrid.
We can immediately improve the appearance of the DataGrid by taking advantage of auto formatting. Right-click the DataGrid and select Auto Format. The Auto Format dialog box will appear (see Figure 13.2). Notice that you can select among a variety of pre-made format schemes, such as Professional, Colorful, or Classic.
Unfortunately, you cannot modify the list of format schemes displayed in the Auto Format dialog box. The list of format schemes is hard-coded into the System.Design.dll assembly within the AutoFormatDialog
class.
If you don’t like any of
the pre-made format schemes, you can modify
the appearance of a DataGrid directly by working with the Style
objects associated with the DataGrid
control. The DataGrid
control has the
following Style
objects that you can modify in the Properties window:
AlternatingItemStyle
. These
formatting properties are applied to every other row displayed in the DataGrid.
EditItemStyle
. These
formatting properties are applied to the row that is currently selected for editing.
FooterStyle
. These
formatting properties are applied to the content of the DataGrid footer.
HeaderStyle
. These
formatting properties are applied to the content of the DataGrid header.
ItemStyle
. These
formatting properties are applied to every row displayed in the DataGrid.
PagerStyle
. These
formatting properties are applied to the user interface for navigating through a DataGrid when paging is enabled.
SelectedItemStyle
. These
formatting properties are applied to the row that is currently selected.
For example, if you want every other row displayed by a DataGrid to have a yellow background color, you can expand the AlternatingItemStyle
property and modify the value of the BackColor
property.
By default,
the DataGrid
control will display all the columns from the database table to which it is bound. Typically, this isn’t what you want because it provides you with little control over the formatting of each column.
You can control exactly which columns are displayed, and how the columns are formatted, by opening the Property Builder for a DataGrid. Right-click the DataGrid and select Property Builder, click the Columns tab, and you’ll see the dialog box shown in Figure 13.3.
It’s a bad idea to retrieve columns in your SELECT
statement that you don’t plan to display. For example, if you retrieve all the columns from the Products table and only show the ProductName column in the DataGrid, you have pushed a lot of unnecessary data across the wire.
Notice that the Create Columns Automatically at Run Time check box is checked by default. The first thing that you should do, if you want to control the columns that appear in the DataGrid, is to uncheck this check box.
You can select columns to display by clicking Bound Column in the Available columns panel and clicking the arrow to move the Bound Column to the Selected columns panel. For each Bound Column, you can set the following properties:
Header text—. The text that appears at the top of the column
Header image—. An image that appears at the top of the column
Data Field—. The name of the database column to display in the column
Data formatting expression—. A format string used to format the values of this column
Footer text—. The text that appears at the bottom of the column
Sort expression—. Value passed to the SortCommand
handler when sorting is enabled for the DataGrid
Read Only—. Indicates that the column should not be editable when the row is selected for editing
Visible—. Indicates whether the column is hidden or visible
For example, suppose that you want to modify the DisplayProductsGrid.aspx page that we created in the previous section so that it shows the ProductID, ProductName, and UnitPrice columns.
Right-click the DataGrid in the DisplayProductsGrid.aspx page, select Property Builder and click the Columns tab.
Uncheck the Create Columns Automatically at Run Time check box.
Add a Bound Column to the Selected columns panel with the following properties:
Property | Value |
---|---|
|
|
|
|
Add a second Bound Column to the Selected columns panel with the following properties:
Property | Value |
---|---|
|
|
|
|
Add a third Bound Column to the Selected columns panel with the following properties:
Property | Value |
---|---|
|
|
|
|
|
|
Click OK to close the DataGrid1 Properties dialog box.
Right-click the DisplayProductsGrid.aspx page in the Solution Explorer window and select Build and Browse.
After you complete these steps, the page shown in Figure 13.4 appears. Notice that the column headings are no longer the same as the database column names. Furthermore, the UnitPrice column is now formatted correctly.
You can use a DataGrid to display a list of hyperlinks. This is useful when you want to create a two-page Master/Detail form. You can create the master page by using a DataGrid to show a list of links. Each link can pass the ID of a record to a detail page.
For example, suppose that you want to create a master and detail page that enables you to look up detailed information on different authors. First, you need to create the master page by using the DataGrid
control. We’ll start by creating the page and adding all the necessary database objects.
Add a Web Form Page to your project named AuthorMaster.aspx
.
Drag and drop the Authors database table from the Server Explorer window onto the Designer surface.
Add an Untyped DataSet to the page.
Next, we need to add a DataGrid
control to the page and configure it to display a list of links to the AuthorDetail.aspx page:
Add a DataGrid
control to the page.
Right-click the DataGrid, select Property Builder, and click the Columns tab.
Uncheck the Create Columns Automatically at Run Time check box.
Add a HyperLink Column to the Selected columns panel with the following properties:
Property | Value |
---|---|
|
|
|
|
|
|
|
|
Double-click the Designer surface and enter the
following code for the Page_Load
method:
C#.
private void Page_Load(object sender, System.EventArgs e) { sqlDataAdapter1.Fill( dataSet1 ); DataGrid1.DataSource = dataSet1; DataGrid1.DataBind(); }
VB.NET.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load SqlDataAdapter1.Fill(DataSet1) DataGrid1.DataSource = DataSet1 DataGrid1.DataBind() End Sub
Right-click the AuthorMaster.aspx page in the Solution Explorer window and select Build and Browse.
When the AuthorMaster.aspx page opens, the page should resemble the page in Figure 13.5. If you hover your mouse over the links, you can see that each link passes the author ID to the AuthorDetail.aspx page.
Next, we need to create the
AuthorDetail.aspx page. On this page, we’ll use a DataReader to represent the detail information for an author and display the information in Label
controls. We’ll start by creating the page and adding all the necessary database objects and controls:
Add a Web Form Page to your project named AuthorDetail.aspx
.
In the Server Explorer window, drag the Pubs data connection from under the Data Connections tab onto the Designer surface.
Add an SqlCommand
object to the page.
In the Properties window, select the sqlCommand1
object and assign the value sqlConnection1
to its Connection property.
In the Properties window, select the sqlCommand1
object and assign the following value to the CommandText
property (when the Regenerate Parameters dialog box appears, click Yes):
Select * from Authors Where au_id=@authorID
Add three Web Forms Labels to the page. Provide the first Label with an ID of lblFirstName
, the second Label with an ID of lblLastName
, and the third Label with an ID of lblPhone
.
Next, we need to add the application logic to the page that will grab the necessary author record from the Authors database table.
Double-click the Designer surface to switch to the Code Editor.
Enter the following code for the
Page_Load
handler:
C#.
private void Page_Load(object sender, System.EventArgs e) { System.Data.SqlClient.SqlDataReader dtrAuthor; string AuthorID = Request.QueryString[ "id" ]; if (AuthorID == null) Response.Redirect( "AuthorMaster.aspx" ); sqlCommand1.Parameters["@authorID"].Value=AuthorID; sqlConnection1.Open(); dtrAuthor = sqlCommand1.ExecuteReader(); if (dtrAuthor.Read()) { lblFirstName.Text = (string)dtrAuthor["au_fname"]; lblLastName.Text = (string)dtrAuthor["au_lname"]; lblPhone.Text = (string)dtrAuthor["phone"]; } sqlConnection1.Close(); }
VB.NET.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim dtrAuthor As System.Data.SqlClient.SqlDataReader Dim AuthorID As String AuthorID = Request.QueryString("id") If IsNothing(AuthorID) Then Response.Redirect("AuthorMaster.aspx") End If SqlCommand1.Parameters("@authorID").Value = AuthorID SqlConnection1.Open() dtrAuthor = SqlCommand1.ExecuteReader() If dtrAuthor.Read() Then lblFirstName.Text = dtrAuthor("au_fname") lblLastName.Text = dtrAuthor("au_lname") lblPhone.Text = dtrAuthor("phone") End If SqlConnection1.Close() End Sub
Right-click the AuthorMaster.aspx page—not the AuthorDetail.aspx page—in the Solution Explorer window and select Build and Browse.
When you click a particular author’s last name in the AuthorMaster.aspx page, the author’s first name, last name, and phone number appears in the AuthorDetail.aspx page.
The Page_Load
handler contains code that first checks whether an author ID has been passed to the page. If not, the user is automatically redirected to the AuthorMaster.aspx page. If an author ID can be retrieved, the author record is returned from the Authors database table and assigned to the Label
controls.
You can use a DataGrid to sort records retrieved from a database table. When a DataGrid has sorting enabled, the DataGrid column headers appear as links. When you click a link, the rows in the DataGrid are sorted in order of that column.
For example, suppose that you want to display a DataGrid that enables you to sort the records in the Titles database table. First, you need to create the Web Form Page and add the necessary database objects:
Add a Web Form Page to your project named SortGrid.aspx
.
Drag and drop the Titles database table from under the Pubs data connection in the Server Explorer window onto the Designer surface.
Add an Untyped DataSet to the page.
Add a DataView to the page.
Notice that we’ve added a DataView to the page. We need to add a DataView to create a sortable representation of the data in the DataSet.
Next, you need to add a DataGrid
control to the page and enable sorting for the DataGrid:
Add a DataGrid to the page.
Right-click the DataGrid and select Property Builder. Check the Allow Sorting check box and click OK.
Next, you need to add the application logic to the page that retrieves the database data and binds the data to the DataGrid:
Switch to the Code Editor by double-clicking the Designer surface.
Enter the following code for
the Page_Load
handler:
C#.
private void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) BindGrid( "Title" ); }
VB.NET.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not Page.IsPostBack Then BindGrid("Title") End If End Sub
Enter the following
BindGrid()
method below the Page_Load
handler:
C#.
void BindGrid( string strSort ) { sqlDataAdapter1.Fill( dataSet1 ); dataView1 = dataSet1.Tables[0].DefaultView; dataView1.Sort = strSort; DataGrid1.DataSource = dataView1; DataGrid1.DataBind(); }
VB.NET.
Sub BindGrid(ByVal strSort As String) SqlDataAdapter1.Fill(DataSet1) DataView1 = DataSet1.Tables(0).DefaultView DataView1.Sort = strSort DataGrid1.DataSource = DataView1 DataGrid1.DataBind() End Sub
If you build and browse the page at this point, the DataGrid will appear with the data from the Titles database table. However, if you click a column heading, nothing happens. The data isn’t sorted.
There’s one last step you must perform to get the DataGrid to sort the data. The DataGrid
control raises the SortCommand
event whenever you click a column heading. You must add a handler for this event to the page.
Procedure 13.1. C# Steps
Switch back to the Designer by selecting Designer from the View menu.
In the Properties window, select the DataGrid
control and click the Events icon (the lightning bolt). Clicking the Events icon will display a list of all the events associated with the DataGrid.
Double-click next to the SortCommand
event. This will switch you to the Code Editor and add a
SortCommand
event handler.
Enter the following code for the SortCommand
handler:
private void DataGrid1_SortCommand(object source, System.Web.UI.WebControls .DataGridSortCommandEventArgs e) { BindGrid( e.SortExpression ); }
Right-click the SortGrid.aspx page in the Solution Explorer window and select Build and Browse.
Procedure 13.2. VB.NET Steps
In the Class Name drop-down list that appears at the top-left of the Code Editor, select the DataGrid1 control.
In the Method Name drop-down list that appears at the top-right of the Code Editor, select the SortCommand
event. This will add a SortCommand
event handler to the Code Editor.
Enter the following code for the SortCommand
handler:
Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridSortCommandEventArgs) Handles DataGrid1.SortCommand BindGrid(e.SortExpression) End Sub
Right-click the SortGrid.aspx page in the Solution Explorer window and select Build and Browse.
After you complete these steps, the DataGrid will display the contents of the Titles table. If you click any of the column headings, that column will sort the rows displayed by the DataGrid.
Notice that you had to add the application logic yourself to the page to enable sorting. The contents of the DataGrid are sorted with the DataView
object.
One problem with the page that we created in the previous section is that it is not very efficient. Whenever you click a column to sort the DataGrid, all the data must be retrieved from the database again. Because accessing a database table is a slow operation, the page is not as efficient as it could be.
There’s an easy way to fix this. You can cache the records retrieved from the database in the Web server’s memory by taking advantage of the
Cache
object. Retrieving records from a database table is slow, but retrieving records from memory is lightning fast.
In the SortGrid.aspx page, make the following modifications to the
BindGrid()
method:
C#.
void BindGrid( string strSort ) { dataSet1 = (DataSet)Cache[ "Titles" ]; if (dataSet1 == null ) { dataSet1 = new DataSet(); sqlDataAdapter1.Fill( dataSet1 ); Cache[ "Titles" ] = dataSet1; } dataView1 = dataSet1.Tables[0].DefaultView; dataView1.Sort = strSort; DataGrid1.DataSource = dataView1; DataGrid1.DataBind(); }
VB.NET.
Sub BindGrid(ByVal strSort As String) DataSet1 = Cache("Titles") If DataSet1 Is Nothing Then DataSet1 = New DataSet() SqlDataAdapter1.Fill(DataSet1) Cache("Titles") = DataSet1 End If DataView1 = DataSet1.Tables(0).DefaultView DataView1.Sort = strSort DataGrid1.DataSource = DataView1 DataGrid1.DataBind() End Sub
If you rebuild the page (by right-clicking the SortGrid.aspx page in the Solution Explorer window and selecting Build and Browse), the database will be accessed only once, when the page first opens. You can test this by temporarily stopping the Microsoft SQL Server service:
Open the Microsoft SQL Server Service Manager by going to Start, Programs, Microsoft SQL Server, Service Manager.
Click the Stop button.
Now that you’ve shut down Microsoft SQL Server, there’s no possibility that the SortGrid.aspx page can retrieve data from the Titles database table. However, if you refresh the page in the browser, the DataGrid will continue to display the database records. The records have been cached in memory.
If you click a column heading twice in the SortGrid.aspx page, the rows in the DataGrid will not be sorted in a different order. Clicking a column heading always sorts in ascending order. In other words, our SortGrid.aspx page does not support ascending and descending sorts.
We can fix this by adding some memory to the page. The page needs to remember the last order in which the records were sorted and reverse the order.
To enable both ascending and descending sorts, make the following modifications to the
SortCommand
event handler:
C#.
private void DataGrid1_SortCommand(object source, System.Web.UI.WebControls .DataGridSortCommandEventArgs e) { string strSort = (string)ViewState[ "Sort" ]; string strDirection = (string)ViewState[ "Direction" ]; if (strSort != e.SortExpression) { strSort = e.SortExpression; strDirection = "asc"; } else { if (strDirection == "asc") strDirection = "desc"; else strDirection = "asc"; } ViewState[ "Sort" ] = strSort; ViewState[ "Direction" ] = strDirection; BindGrid( strSort + " " + strDirection ); }
VB.NET.
Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridSortCommandEventArgs) Handles DataGrid1.SortCommand Dim strSort As String Dim strDirection As String strSort = ViewState("Sort") strDirection = ViewState("Direction") If (strSort <> e.SortExpression) Then strSort = e.SortExpression strDirection = "asc" Else If (strDirection = "asc") Then strDirection = "desc" Else strDirection = "asc" End If End If ViewState("Sort") = strSort ViewState("Direction") = strDirection BindGrid(strSort + " " + strDirection) End Sub
This code stores the previous sort order in the page’s ViewState
. If you click a column heading and it matches the column name stored in ViewState
, the sort direction is
reversed.
If you have a large
number of records that you need to display, it makes sense to divide the records into multiple logical pages (see Figure 13.6). The DataGrid
control has built-in support for paging. You can navigate from page to page by using page numbers or by using next and previous links.
For example, suppose that you want to display the contents of the Products database table by displaying five records per page. First, you need to create a new Web Form Page and add the necessary database objects:
Add a Web Form Page to your project named PageGrid.aspx
.
Drag and drop the Products database table from the Server Explorer window onto the Designer surface.
Add an Untyped DataSet to the page.
Next, you need to add a DataGrid and enable paging for the DataGrid:
Add a DataGrid
control to the page.
Right-click the DataGrid
control and select Property Builder. Click the Paging tab (you should see the dialog box shown in Figure 13.7).
Select the Allow Paging check box.
Enter the value 5
for the Page Size property.
Change Mode to Page Numbers.
Next, you need to add the application logic necessary to bind the DataGrid to the DataSet:
Double-click the Designer surface to switch to the Code Editor.
Enter the following code for the Page_Load
handler:
C#.
private void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) BindGrid(); }
VB.NET.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not Page.IsPostBack Then BindGrid() End If End Sub
Below the Page_Load
handler, enter the following
BindGrid()
method:
C#.
void BindGrid() { sqlDataAdapter1.Fill( dataSet1 ); DataGrid1.DataSource = dataSet1; DataGrid1.DataBind(); }
VB.NET.
Sub BindGrid() SqlDataAdapter1.Fill(DataSet1) DataGrid1.DataSource = DataSet1 DataGrid1.DataBind() End Sub
If you build and browse the page at this point, you would see the records from the Products table displayed in the DataGrid. Furthermore, you would see a list of page numbers at the bottom of the DataGrid. If you click a page number, however, nothing would happen. The same set of records would be displayed.
Before paging will work in a DataGrid, you must add an event handler for the
PageIndexChanged
event. This event is raised whenever you click a page number:
Procedure 13.3. C# Steps
Switch back to the Designer by selecting Designer from the View menu.
In the Properties window, select the DataGrid
control.
Click the Events icon at the top of the Properties window (it looks like a lightning bolt).
Double-click next to the PageIndexChanged
event. This will switch you back to the Code Editor.
Enter the following code for the PageIndexChanged
handler:
private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls .DataGridPageChangedEventArgs e) { DataGrid1.CurrentPageIndex = e.NewPageIndex; BindGrid(); }
Right-click the PageGrid.aspx page in the Solution Explorer window and select Build and Browse.
Procedure 13.4. VB.NET Steps
Select DataGrid1
in the Class Name drop-down list that appears at the top left of the Code Editor.
Select the PageIndexChanged
event from the Method Name drop-down list that appears at the top right of the Code Editor.
Enter the following code for the PageIndexChanged
subroutine:
Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged DataGrid1.CurrentPageIndex = e.NewPageIndex BindGrid() End Sub
Right-click the PageGrid.aspx page in the Solution Explorer window and select Build and Browse.
When the page opens, you can click the different page numbers that appear at the bottom of the DataGrid to navigate to different pages.
You can
customize the appearance of the navigation links for paging in two places. First, you can modify the appearance of the paging user interface by modifying the DataGridPagerStyle
object exposed by the DataGrid
control’s PagerStyle
property.
You can view all the formatting options made available by the DataGridPagerStyle
object by selecting the DataGrid in the Properties window and expanding the PagerStyle
property. For example, you can modify such properties as the background color, the font, the horizontal alignment, and the position of the paging user interface.
You can also modify the majority of these properties by taking advantage of the Property Builder. Right-click the DataGrid
control on the Designer surface and select Property Builder. Select the
Paging tab to view the list of properties that you can modify:
Allow Paging—. Enables
or disables paging for the DataGrid. This property corresponds to the DataGrid
control’s AllowPaging
property.
Page Size—. The number
of records to display on a page. This property corresponds to the DataGrid
control’s PageSize
property.
Show navigation buttons—. Hides
or displays the paging user interface. This property corresponds to the DataGrid
control’s PagerStyle Visible
property.
Position—. Indicates whether
the paging user interface appears at the bottom, top, or both top and bottom of the DataGrid. This property corresponds to the DataGrid
control’s PagerStyle Position
property.
Mode—. Indicates
whether page numbers or next and previous links are displayed (you can’t pick both). This property corresponds to the DataGrid
control’s PagerStyle Mode
property.
Next page button text—. The
text that appears for the previous page link. This property corresponds to the DataGrid
control’s PagerStyle NextPageText
property.
Previous page button text—. The
text that appears for the next page link. This property corresponds to the DataGrid
control’s PagerStyle PrevPageText
property.
Numeric Buttons—. The number
of page numbers to display before showing ellipsis points(useful when there are a lot of page numbers). This property corresponds to the DataGrid
control’s PagerStyle PageButtonCount
property.
There’s one big
problem with our PageGrid.aspx page. Whenever you click a page number in the PageGrid.aspx page, the PageIndexChanged
handler is executed. This handler retrieves all the records from the Products database table. It’s important to realize that this handler doesn’t only retrieve the records for the current page; it retrieves all records. If the Products database table contains three billion records, all three billion records would be retrieved just to show the five records for the currently selected page.
If you are working with a relatively small table, you might decide to live with this inefficiency. But, when it comes to large tables, the DataGrid
control’s built-in paging mechanism won’t work.
For certain applications, you can get around this problem by taking advantage of the Cache
object. You can cache all the records in the Web server’s memory and avoid accessing the database whenever someone navigates to a new page. The following code modifies the BindGrid()
method to take advantage of the Cache
object:
C#.
void BindGrid() { dataSet1 = (DataSet)Cache[ "Products" ]; if (dataSet1 == null) { dataSet1 = new DataSet(); sqlDataAdapter1.Fill( dataSet1 ); Cache[ "Products" ] = dataSet1; } DataGrid1.DataSource = dataSet1; DataGrid1.DataBind(); }
VB.NET.
Sub BindGrid() DataSet1 = Cache("Products") If DataSet1 Is Nothing Then DataSet1 = New DataSet() SqlDataAdapter1.Fill(DataSet1) Cache("Products") = DataSet1 End If DataGrid1.DataSource = DataSet1 DataGrid1.DataBind() End Sub
This code will add the DataSet that represents the Products database table to the Cache
the first time the page is accessed. Any time the page is accessed in the future, the database records will be retrieved from the server’s memory instead of the
database.
Using the Cache
object will not completely solve the problem. If you are working with a truly huge set of data, you might not want to cache the entire set of database records. In that case, you have no choice but to implement custom paging. The topic of custom paging is outside the scope of this book. To learn more about custom paging, see Chapter 11, “Using the DataList and DataGrid Controls,” in ASP.NET Unleashed.
In this section, you’ll learn two ways of editing the database records displayed in a DataGrid.
First, you’ll learn how to implement editing by using Bound Columns. This is the fastest method of implementing editing, but it has some significant limitations. Next, we’ll take a look at how you can implement editing with Template Columns.
Before walking
through the steps necessary to enable editing with a DataGrid
control, you need to be warned that you must write all the database logic to perform the editing yourself. Microsoft has provided you only with the user interface for editing. You must write the application logic to update the underlying database.
We’ll write all the necessary code for updating a database table in this section. We’ll create a DataGrid that enables us to update the Products database table.
Let’s start by creating the page and adding the necessary database objects:
Add a Web Form Page to your project named EditGrid.aspx
.
Drag and drop the Products database table from the Server Explorer window onto the Designer surface.
Add an Untyped DataSet to the page.
Next, we need to add a DataGrid
control to the page and configure the DataGrid for editing:
Add a DataGrid
control to the page.
Right-click the DataGrid, select Property Builder, and click the Columns tab.
Uncheck the Create Columns Automatically at Run Time check box.
In the Available columns panel, expand Button Column, and add the Edit, Update, Cancel Columns to the Selected columns panel.
Enter the following three bound columns:
Header Text | Data Field | Read Only |
---|---|---|
|
| Checked |
|
| Unchecked |
|
| Unchecked |
Click OK to close the Property Builder dialog box.
Next, we need to bind the DataGrid
control to the DataSet that represents the Products database table:
Double-click the Designer surface to switch to the Code Editor.
Enter the following code
for the Page_Load
handler:
C#.
private void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) BindGrid(); }
VB.NET.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not Page.IsPostBack Then BindGrid() End If End Sub
Enter the following BindGrid()
method below the Page_Load
handler:
C#.
void BindGrid() { sqlDataAdapter1.Fill( dataSet1 ); DataGrid1.DataSource = dataSet1; DataGrid1.DataKeyField = "ProductID"; DataGrid1.DataBind(); }
VB.NET.
Sub BindGrid() SqlDataAdapter1.Fill(DataSet1) DataGrid1.DataSource = DataSet1 DataGrid1.DataKeyField = "ProductID" DataGrid1.DataBind() End Sub
If you build and browse the EditGrid.aspx page at this
point, you would see the page in Figure 13.8. If you click the Edit link, however, nothing would happen. You must add a handler for the
EditCommand
event before the DataGrid will do anything.
Procedure 13.5. C# Steps
Switch back to the Designer by selecting Designer from the View menu.
In the Properties window, select the DataGrid and click the Events icon at the top of the Properties window (the icon that looks like a lightning bolt).
Double-click the EditCommand
event. This will switch you to the Code Editor.
Enter the following code for the EditCommand
handler:
private void DataGrid1_EditCommand(object source, System.Web.UI.WebControls .DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = e.Item.ItemIndex; BindGrid(); }
Procedure 13.6. VB.NET Steps
In the Class Name drop-down list that appears at the top-left of the Code Editor, select DataGrid1
.
In the Method Name drop-down list that appears at the top-right of the Code Editor, select the EditCommand
event. This will add a new EditCommand
handler to the Code Editor.
Enter the following code for the EditCommand
handler:
Private Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridCommandEventArgs) Handles DataGrid1.EditCommand DataGrid1.EditItemIndex = e.Item.ItemIndex BindGrid() End Sub
The EditCommand
event handler that we just added selects
a row for
editing and calls BindGrid()
to display the updated DataGrid
control. When you click the Edit link next to a row, any column that is not marked as read-only is automatically displayed as a single-line text box. Furthermore, the Edit link is replaced with an Update and Cancel link.
The next step is to create an event-handler for the CancelCommand
—the
event that is raised when you click the Cancel link.
Procedure 13.7. C# Steps
Switch back to the Designer by selecting Designer from the View menu.
In the Properties window, select the DataGrid and click the Events icon (the lightning bolt) that appears at the top of the Properties window.
Double-click next to the CancelCommand
event. This will switch you back to the Code Editor.
Enter the following code for the CancelCommand
event:
private void DataGrid1_CancelCommand(object source, System.Web.UI.WebControls .DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = -1; BindGrid(); }
Procedure 13.8. VB.NET Steps
In the Class Name drop-down list, select the DataGrid
control.
In the Method Name drop-down list, select the CancelCommand
event. This will add a CancelCommand
handler to the Code Editor.
Enter the following code for the CancelCommand
handler:
Private Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridCommandEventArgs) Handles DataGrid1.CancelCommand DataGrid1.EditItemIndex = -1 BindGrid() End Sub
When you click the Cancel link, the value -1 is
assigned to the EditItemIndex
property. This value unselects all rows for editing.
Finally, we need to add the event handler for the UpdateCommand
event. We’ll use this handler to execute an SqlCommand to update the underlying database. Let’s start by adding the SqlCommand
object to the page:
Switch back to the Designer by selecting Designer from the View menu.
Add a SqlCommand
object to the page.
In the Properties window, select the SqlCommand
object.
In the Properties window, double-click next to Connection
property to assign sqlConnection1
as the value of the Connection
property.
In the Properties window, enter the following code for the CommandText
property:
UPDATE Products SET ProductName = @ProductName, UnitPrice = @UnitPrice WHERE (ProductID = @ProductID)
Now, we can execute
the SqlCommand within the UpdateCommand
handler:
Procedure 13.9. C# Steps
In the Properties window, select the DataGrid and click the Events icon (the lightning bolt).
Double-click next to the UpdateCommand
event. This will switch you back to the Code Editor.
Enter the following code for the UpdateCommand
handler:
private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls .DataGridCommandEventArgs e) { // Retrieve TextBox Controls from DataGrid TextBox txtProductName = (TextBox)e.Item.Cells[2].Controls[0]; TextBox txtUnitPrice = (TextBox)e.Item.Cells[3].Controls[0]; // Assign Parameters to SqlCommand sqlCommand1.Parameters["@ProductName"].Value= txtProductName.Text; sqlCommand1.Parameters["@UnitPrice"].Value = txtUnitPrice.Text; sqlCommand1.Parameters["@ProductID"].Value = DataGrid1.DataKeys[ e.Item.ItemIndex ]; // Execute SqlCommand sqlConnection1.Open(); sqlCommand1.ExecuteNonQuery(); // Deselect Row for Editing DataGrid1.EditItemIndex = -1; BindGrid(); sqlConnection1.Close(); }
Right-click the EditGrid.aspx page in Solution Explorer and select Build and Browse.
Procedure 13.10. VB.NET Steps
Double-click the Designer surface to switch back to the Code Editor.
Select DataGrid1
from the Class Name drop-down list that appears at the top left of the Code Editor.
Select the UpdateCommand
event from the Method Name drop-down list that appears at the top right of the Code Editor. This will add an UpdateCommand
event handler.
Enter the following code for
the UpdateCommand
handler:
Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridCommandEventArgs) Handles DataGrid1.UpdateCommand ' Retrieve TextBox Controls from DataGrid Dim txtProductName As TextBox Dim txtUnitPrice As TextBox txtProductName = CType(e.Item.Cells(2).Controls(0), TextBox) txtUnitPrice = CType(e.Item.Cells(3).Controls(0), TextBox) ' Assign Parameters to SqlCommand SqlCommand1.Parameters("@ProductName").Value = txtProductName.Text SqlCommand1.Parameters("@UnitPrice").Value = txtUnitPrice.Text SqlCommand1.Parameters("@ProductID").Value = DataGrid1.DataKeys(e.Item.ItemIndex) ' Execute SqlCommand SqlConnection1.Open() SqlCommand1.ExecuteNonQuery() ' Deselect Row for Editing DataGrid1.EditItemIndex = -1 BindGrid() SqlConnection1.Close() End Sub
Right-click the EditGrid.aspx page in the Solution Explorer window and select Build and Browse.
When the EditGrid.aspx page opens, you can click next to any row to edit it. You can change the contents of both the ProductName and UnitPrice columns in the underlying database table.
The UpdateCommand
event handler requires some explanation. The first lines in the handler are used to retrieve the ProductName and UnitPrice TextBox
controls from the DataGrid. The Cells
collection represents the columns in the DataGrid, so passing the index 2 to the Cells
collection returns the ProductName text box, and passing the index 3 returns the UnitPrice text box. The first two cells contain the Edit, Update, Cancel, and ProductID columns.
Next, the UpdateCommand
handler initializes the parameters for the SqlCommand
object and executes the Update
command. Finally, the current row is deselected for editing by assigning the value -1 to the DataGrid
control’s EditItemIndex
property, and the DataGrid is updated by
calling BindGrid()
.
The DataGrid
control
that we created in the previous section enables you to update rows in the Products database table. However, it does not allow you to delete a row. In this section, we’ll add an additional column to the DataGrid that contains a Delete link.
Right-click the DataGrid
control on the Designer surface, select Property Builder, and click the Columns tab.
In the Available Columns panel, expand Button Columns, select the Delete column, and copy the Delete column over to the Selected columns panel.
Move the Delete column to the top of the list in the Selected columns panel by clicking the up arrow.
Click OK to close the Property Builder dialog box.
Next, you need to add a new SqlCommand
object to the EditGrid.aspx page that represents an SQL Delete
command.
Add a new SqlCommand
object to the page.
In the Properties window, select the new SqlCommand
.
Double-click next to the SqlCommand’s Connection
property. This will assign the value sqlConnection1
to the Connection
property.
Enter the following SQL Delete
command for the CommandText
property:
DELETE FROM Products WHERE (ProductID = @ProductID)
Finally, we need to add an event handler for the Delete link that will execute the SqlCommand
.
Procedure 13.11. C# Steps
In the Properties window, select the DataGrid
control and click
the Events icon (the lightning bolt).
Double-click next to the
DeleteCommand
event. This will switch you to the Code Editor.
Enter the following code for the DeleteCommand
handler:
private void DataGrid1_DeleteCommand(object source, System.Web.UI.WebControls .DataGridCommandEventArgs e) { sqlCommand2.Parameters[ "@ProductID" ].Value = DataGrid1.DataKeys[ e.Item.ItemIndex ]; sqlConnection1.Open(); sqlCommand2.ExecuteNonQuery(); BindGrid(); sqlConnection1.Close(); }
Right-click the EditGrid.aspx page in the Solution Explorer window and select Build and Browse.
Procedure 13.12. VB.NET Steps
Double-click the Designer surface to switch to the Code Editor.
From the Class Name drop-down list that appears at the top left of the Code Editor, select DataGrid1
.
From the Method Name drop-down list that appears at the top right of the Code Editor, select DeleteCommand
. This will add a DeleteCommand
handler to the Code Editor.
Enter the following code for the DeleteCommand
handler:
Private Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridCommandEventArgs) Handles DataGrid1.DeleteCommand SqlCommand2.Parameters("@ProductID").Value = DataGrid1.DataKeys(e.Item.ItemIndex) SqlConnection1.Open() SqlCommand2.ExecuteNonQuery() BindGrid() SqlConnection1.Close() End Sub
Right-click the EditGrid.aspx page in the Solution Explorer and select Build and Browse.
After you complete these steps, you can delete any row displayed in the DataGrid
control by clicking the Delete link. Clicking the Delete link causes the DeleteCommand
handler to execute. This handler executes an SQL Delete
command to delete the row from the underlying database
table.
When you
click the Edit, Update, Cancel, or Delete link next to a row in the DataGrid
control, the page containing the DataGrid is reloaded. Regardless of where you click in a DataGrid, the page reloads and you are brought back to the very top of the page.
This can be confusing. Fortunately, there is an easy fix for this problem. You can take advantage of something called smart navigation. Smart navigation automatically returns you to the same place in a DataGrid when the page containing the DataGrid is reloaded.
Perform the following steps to enable smart navigation for a page:
You should be warned that smart navigation only works with Microsoft Internet Explorer 5.0 or later. It does not work with any version of the Netscape browser (it’s ignored).
There are
two significant limitations involved with using a DataGrid to edit database records when using bound columns. First, you cannot perform any validation. If you attempt to enter the value apple
for the Price column, a big, fat error is generated when the page is rendered.
Second, you have no control over the appearance of the editing user interface. Columns selected for editing appear in a single-line TextBox
control. You can’t edit fields using other types of controls, such as RadioButtonList
or DropDownList
controls.
The solution to both of these problems is Template Columns. You can add validation controls to a Template Column. Furthermore, you can add any form controls, such as DropDownList
and multi-line TextBox
controls, to a Template Column that you want.
In this section, we’ll create another page for editing the records in the Products database table. This time, however, we’ll add a RequiredFieldValidator
control to the ProductName column. We’ll also enable users to select the category for a product from a DropDownList
control (see Figure 13.9).
First, let’s create the Web Form Page and add the necessary database objects:
Add a Web Form Page to your project named EditTemplate.aspx
.
Drag and drop the Products database table from the Server Explorer window onto the Designer surface.
Add an Untyped DataSet to the page.
Drag and drop the Categories database table from the Server Explorer window onto the Designer surface.
Add a second Untyped DataSet to the page.
After you complete these steps, you’ll have two DataSets on the page—one DataSet represents the Products table, and one DataSet represents the Categories table.
Next, you need to add the DataGrid control to the page and configure its Template Columns:
Add a DataGrid
control to the page.
Right-click the DataGrid, select Property Builder, and click the Columns tab.
Uncheck the Create Columns Automatically at Run Time check box.
Add an Edit, Update, Cancel
column by expanding Button Column in the Available columns panel, selecting the Edit, Update, Cancel
column, and moving it to the Selected columns panel.
Add the following two Template Columns to the Selected columns panel:
Header text
Name
Category
Click OK to close the Property Builder dialog box.
Right-click the DataGrid and select Auto Format. Select the Professional 1 scheme and click OK.
Next, we need to add templates to the two Template Columns that we created in the DataGrid. For both the Name and Category columns, we need to create an ItemTemplate
and EditItemTemplate
.
Switch to HTML View by clicking the HTML tab at the bottom left of the Designer.
Find the DataGrid tag in the HTML source.
Inside the DataGrid tag, add the following
code to the first TemplateColumn
:
<asp:TemplateColumn HeaderText="Name"> <ItemTemplate> <%# DataBinder.Eval( Container, "DataItem.ProductName" )%> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtProductName" Text='<%# DataBinder.Eval( Container, "DataItem.ProductName" )%>' runat="server"/> <asp:RequiredFieldValidator ControlToValidate="txtProductName" Text="*" Runat="server"/> </EditItemTemplate> </asp:TemplateColumn>
Inside the DataGrid tag, add the following code to the second TemplateColumn
:
<asp:TemplateColumn HeaderText="Category"> <ItemTemplate> <%# ShowCategory( DataBinder.Eval( Container, "DataItem.CategoryID" ) )%> </ItemTemplate> <EditItemTemplate> <asp:DropDownList id="dropCategory" DataSource='<%# dataSet2 %>' SelectedIndex='<%# SelectCategory( DataBinder.Eval( Container, "DataItem.CategoryID" ) )%>' DataTextField='CategoryName' DataValueField='CategoryID' Runat="server" /> </EditItemTemplate> </asp:TemplateColumn>
The first Template Column contains
an ItemTemplate
for displaying the value of the ProductName column and an EditItemTemplate
for editing the value of the ProductName column. Notice that the EditItemTemplate
contains a RequiredFieldValidator
control. This control prevents a user from attempting to submit a blank value for ProductName.
The second Template Column contains an ItemTemplate
for displaying the current category associated with the product. It also contains an EditItemTemplate
that contains a drop-down list that displays a list of all the categories from the Categories database table so that a user can pick a new category when editing the row.
The TemplateColumn
used for displaying the product category takes advantage of two methods. The ItemTemplate
uses a
ShowCategory()
method to display the current category. The EditItemTemplate
uses a SelectCategory()
method to make the current category the default category in the DropDownList
control.
We’ll create those two methods by doing the following:
Switch to the Code Editor by double-clicking the Designer surface.
Type the following method (ShowCategory()
) into the Code Editor:
C#.
public string ShowCategory(object CategoryID ) { if (CategoryID==DBNull.Value) return String.Empty; DataRow[] drows = dataSet2.Tables[0].Select("CategoryID=" + CategoryID); return (string)drows[0]["CategoryName"]; }
VB.NET.
Function ShowCategory(ByVal CategoryID As Object) As String Dim drows() As DataRow If IsDBNull(CategoryID) Then Return String.Empty End If drows = DataSet2.Tables(0).Select("CategoryID=" & CategoryID) Return drows(0)("CategoryName") End Function
Type the following
method
(SelectCategory()
) into the Code Editor:
C#.
public int SelectCategory(object CategoryID ) { DataTable dtblCategories = dataSet2.Tables[0]; for (int i = 0;i < dtblCategories.Rows.Count;i++) if ((int)dtblCategories.Rows[i]["CategoryID"] == (int)CategoryID ) return i; return 0; }
VB.NET.
Function SelectCategory(ByVal CategoryID As Object) As Integer Dim i As Integer Dim dtblCategories = DataSet2.Tables(0) For i = 0 To dtblCategories.Rows.Count - 1 If dtblCategories.Rows(i)("CategoryID") = CategoryID Then Return i End If Next Return 0 End Function
Next, we need to fill the two DataSets and bind the Products DataSet to the DataGrid:
Enter the following code for the Page_Load
handler:
C#.
private void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) BindGrid(); }
VB.NET.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not Page.IsPostBack Then BindGrid() End If End Sub
Enter the following BindGrid()
method
below the Page_Load
handler:
C#.
void BindGrid() { sqlDataAdapter1.Fill( dataSet1 ); sqlDataAdapter2.Fill( dataSet2 ); DataGrid1.DataSource = dataSet1; DataGrid1.DataKeyField = "ProductID"; DataGrid1.DataBind(); }
VB.NET.
Sub BindGrid() SqlDataAdapter1.Fill(DataSet1) SqlDataAdapter2.Fill(DataSet2) DataGrid1.DataSource = DataSet1 DataGrid1.DataKeyField = "ProductID" DataGrid1.DataBind() End Sub
Now, we have to add the event handlers for the EditCommand
and CancelCommand
events.
Procedure 13.13. C# Steps
Switch back to the Designer by selecting Designer from the View menu.
In the Properties window, select the DataGrid
control and click the Events icon (the lightning bolt).
Double-click next to the EditCommand
event. This will switch you back to the Code Editor.
Enter the following code for the EditCommand
handler:
private void DataGrid1_EditCommand(object source, System.Web.UI.WebControls .DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = e.Item.ItemIndex; BindGrid(); }
Switch back to the Designer by selecting Designer from the View menu.
In the Properties window, select the DataGrid
control and click the Events icon.
Double-click next to the CancelCommand
event. This will switch you back to the Code Editor.
Enter the following code for
the CancelCommand
handler:
private void DataGrid1_CancelCommand(object source, System.Web.UI.WebControls .DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = -1; BindGrid(); }
Procedure 13.14. VB.NET Steps
Select DataGrid1 from the Class Name drop-down list that appears at the top left of the Code Editor.
Select EditCommand
from the Method Name drop-down list that appears at the top-right of the Code Editor. This will add an EditCommand
handler to the Code Editor.
Enter the following code for the EditCommand
handler:
Private Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridCommandEventArgs) Handles DataGrid1.EditCommand DataGrid1.EditItemIndex = e.Item.ItemIndex BindGrid() End Sub
Select DataGrid1
from the Class Name drop-down list that appears at the top-left of the Code Editor.
Select CancelCommand
from the Method Name drop-down list that appears at the top-right of the Code Editor. This will add a CancelCommand
handler to the Code Editor.
Enter the following code for the CancelCommand
handler:
Private Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridCommandEventArgs) Handles DataGrid1.CancelCommand DataGrid1.EditItemIndex = -1 BindGrid() End Sub
The final step to get this DataGrid
control to work is to add the logic for the UpdateCommand
handler. First, we need to add an SqlCommand
that represents the SQL Update
command:
Switch back to the Designer by selecting Designer from the View menu.
Drag an SqlCommand
object from under the Data tab in the Toolbox onto the Designer surface.
In the Properties window, select the SqlCommand
object.
Double-click next to the Connection
property. This will assign sqlConnection1
to the Connection
property.
Enter the following SQL Update
command for the CommandText
property:
UPDATE Products SET ProductName = @ProductName, CategoryID = @CategoryID WHERE (ProductID = @ProductID)
The final, final step is to create
the UpdateCommand
event handler. This event handler executes the SQL Update
command represented by the SqlCommand
object.
Procedure 13.15. C# Steps
In the Properties window, select the DataGrid1
control and click the Events icon (the lightning bolt).
Double-click next to the UpdateCommand
event. This will switch you to the Code Editor.
Enter the following code for the UpdateCommand
handler:
private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls .DataGridCommandEventArgs e) { // Retrieve the Template TextBox Controls TextBox txtProductName = (TextBox)e.Item.FindControl( "txtProductName" ); DropDownList dropCategory = (DropDownList)e.Item.FindControl( "dropCategory" ); // Initialize SqlCommand Parameters sqlCommand1.Parameters[ "@ProductName" ].Value = txtProductName.Text; sqlCommand1.Parameters["@CategoryID" ].Value = dropCategory.SelectedItem.Value; sqlCommand1.Parameters["@ProductID" ].Value = DataGrid1.DataKeys[ e.Item.ItemIndex ]; // Execute SqlCommand sqlConnection1.Open(); sqlCommand1.ExecuteNonQuery(); // Deselect Current Row for Editing DataGrid1.EditItemIndex = -1; BindGrid(); sqlConnection1.Close(); }
Right-click the EditTemplate.aspx page in the Solution Explorer window and select Build and Browse.
Procedure 13.16. VB.NET Steps
Double-click the Designer surface to switch to the Code Editor.
Select DataGrid1
from the Class Name drop-down list located at the top-left of the Code Editor.
Select UpdateCommand
from the Method Name drop-down list located at the top-right of the Code Editor. This will add the UpdateCommand
event handler.
Enter the following code
for the UpdateCommand
handler:
Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI .WebControls.DataGridCommandEventArgs) Handles DataGrid1.UpdateCommand ' Retrieve the Template TextBox Controls Dim txtProductName As TextBox Dim dropCategory As DropDownList txtProductName = CType(e.Item.FindControl("txtProductName"), TextBox) dropCategory = CType(e.Item.FindControl("dropCategory"), DropDownList) ' Initialize SqlCommand Parameters SqlCommand1.Parameters("@ProductName").Value = txtProductName.Text SqlCommand1.Parameters("@CategoryID").Value = dropCategory.SelectedItem.Value SqlCommand1.Parameters("@ProductID").Value = DataGrid1.DataKeys(e.Item.ItemIndex) ' Execute SqlCommand SqlConnection1.Open() SqlCommand1.ExecuteNonQuery() ' Deselect Current Row for Editing DataGrid1.EditItemIndex = -1 BindGrid() SqlConnection1.Close() End Sub
Right-click the EditTemplate.aspx page in the Solution Explorer window and select Build and Browse.
After you complete these steps, you’ll be able to edit the records in the Products database table within the DataGrid control. Notice that if you attempt to update a product without entering a value for the Name text field, you are prevented from updating the record. A red asterisk appears next to the Name text box (see Figure 13.10).
Furthermore, notice that you can pick a product category from a drop-down list. The drop-down list displays all the product categories by retrieving them from the Categories database table.
In this chapter, you learned everything you need to know to take advantage of the DataGrid
control in your applications. In the first section, you learned how to use the DataGrid
control to automatically display the contents of a database table. You also learned how to create a two-page Master/Detail form with the DataGrid
control by displaying records as links to a details page.
Next, you learned how to sort the data in a DataGrid
control. You learned how to implement both simple sorting and ascending/descending sorting.
In the next section, you learned how to page through a database table displayed in a DataGrid. You learned how to enable paging and how to customize the appearance of the paging user interface.
Finally, you learned how to edit database data with the DataGrid
control. You learned how to configure a DataGrid for editing by using both bound columns and template columns.