ASP.NET Tutorial

Editing with a DataGrid

A DataGrid can be set up for editing like the previous DataList, with an ItemTemplate and an EditItemTemplate to which it switches in edit mode. The easiest method, however, is to configure it like a Repeater, with only editing fields displayed. This layout is shown in the following example. Fortunately, a DataGrid has a built-in pager so that it is not necessary to display the entire database.

Note that making actual changes to the BooksDB.mdb database is not permitted in these tutorials; however, all other functions work as expected.

Book Edit

ID	Type	Title	Author	Description	Price	Qty	Sale
				Get thorough coverage of Oracle Database 10g from the most comprehensive reference available, published by Oracle Press. With in-depth details on all the new features, this powerhouse resource provides an overview of database architecture and Oracle Grid Computing technology, and covers SQL, SQL*Plus, PL/SQL, dynamic PL/SQL, object-oriented features, and Java programming in the Oracle environment. You'll also find valuable database administration and application development techniques, plus an alphabetical reference covering major Oracle commands, keywords, features, and functions, with cross-referencing of topics.
				In Database in Depth, author and well-known database authority Chris Date lays out the fundamentals of the relational model. Don't let a lack to formal education in database theory hold you back. Instead, let Chris's clear explanation of relational concepts, set theory, the difference between model and implementation, relational algebra, normalization, and much more set you apart and well above the competition when it comes to getting work done with a relational database.
				Revised to reflect the needs of today's users, this 10th edition of Database Processing assures that you will learn marketable skills. By presenting SQL SELECT statements near the beginning of the book readers will know early on how to query data and obtain results-seeing firsthand some of the ways that database technology is useful in the marketplace. By utilizing free software downloads, you will be able to actively use a DBMS product by the end of the 2nd chapter. Each topic appears in the context of accomplishing practical tasks. Its spiral approach to database design provides users with enhanced information not available in other database books on the market.
				When using software products with graphical interfaces, we frequently focus so much on the details of how to use the interface that we forget about the general concepts that allow us to understand and use the software effectively. This is particularly true of a powerful database product like Microsoft Access. Novice, and sometimes even experienced, programmers are so concerned with how something is done in Access that they often lose sight of the general principles that underlie their database applications. Access Database Design and Programming takes you behind the details of the Access interface, focusing on the general knowledge necessary for Access power users or developers to create effective database applications.
				Get a developer-focused introduction to the new programmability features in the next version of Microsoft SQL Server-including integration with the Microsoft .NET Framework-and learn powerful new ways to manipulate your servers. Whether you're a developer currently working with T-SQL or Microsoft Visual Studio.NET, or you're responsible for database administration, you'll see how to draw from your existing skills and knowledge to exploit new SQL Server technology. With introductory-level code samples written in both T-SQL and C#, you'll understand how to take advantage of the cross-platform interoperability, native support for XML and Web services, shared language base, and other programming innovations to build better solutions from business intelligence to enterprise data management.
1 2 3 4 5 6

Figure 9-28. Editing a database with a DataGrid.

Coding the DataGrid

The DataGrid displays five records at a time from the Books table using the DataGrid's built-in paging feature. Data items are edited directly in the TextBoxes and other controls across the rows of the grid. All buttons are configured as command buttons to call the Edit_Record subprogram through the DataGrid's OnItemCommand event handler. The current Edit_Record subprogram does not perform data validation although routines can be added as described for the Repeater.

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  SelectCommand = "SELECT * FROM Books ORDER BY BookID"/>

<asp:AccessDataSource id="TypeSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  SelectCommand = "SELECT DISTINCT BookType FROM Books ORDER BY BookType"/>
	
<h3>Book Edit</h3>

<asp:Label id="EditMessage" Text=" " ForeColor="#FF0000" Runat="Server"
EnableViewState="False"/>

<asp:DataGrid id="DataGridEdit" DataSourceID="BookSource" Runat="Server"
  OnItemCommand="Edit_Record"
  AutoGenerateColumns="False"
  AllowPaging="True"
  PageSize="5"
  OnPageIndexChanged="Change_Page"
  PagerStyle-Mode="NumericPages"
  PagerStyle-BackColor="#E0E0E0"
  HeaderStyle-BackColor="#E0E0E0"
  HeaderStyle-Font-Size="10pt"
  HeaderStyle-Font-Bold="True"
  HeaderStyle-HorizontalAlign="Center"
  HeaderStyle-VerticalAlign="Top"
  ItemStyle-VerticalAlign="Top">
  
  <Columns>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
      ID<br/>
      <asp:TextBox id="BookID" Runat="Server"
        Font-Size="8pt" Width="45px"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:TextBox id="BookID" ReadOnly="True" Runat="Server"
        Text='<%# Eval("BookID") %>'
        Font-Size="8pt" Width="45px"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
    Type<br/>
    <asp:DropDownList id="BookType" Runat="Server"
        DataSourceID="TypeSource"
        DataTextField="BookType"
        DataValueField="BookType"
        Font-Size="8pt"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:DropDownList id="BookType" Runat="Server"
        DataSourceID="TypeSource"
        DataTextField="BookType"
        DataValueField="BookType"
        SelectedValue='<%# Eval("BookType") %>'
        Font-Size="8pt"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
      Title<br/>
      <asp:TextBox id="BookTitle" Runat="Server"
        Font-Size="8pt" Width="80px"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:TextBox id="BookTitle" Runat="Server"
        Text='<%# Eval("BookTitle") %>'
        Font-Size="8pt" Width="80px"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
      Author<br/>
      <asp:TextBox id="BookAuthor" Runat="Server"
        Font-Size="8pt" Width="100px"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:TextBox id="BookAuthor" Runat="Server"
        Text='<%# Eval("BookAuthor") %>'
        Font-Size="8pt" Width="100px"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
      Description<br/>
      <asp:TextBox id="BookDescription" Runat="Server"
        TextMode="MultiLine"
        Cols="30" Rows="2"
        Style="font-family:arial; font-size:8pt"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:TextBox id="BookDescription" Runat="Server"
        Text='<%# Eval("BookDescription") %>'
        TextMode="MultiLine"
        Cols="30" Rows="2"
        Style="font-family:arial; font-size:8pt"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
    Price<br/>
      <asp:TextBox id="BookPrice" Runat="Server"
        Text="0.00" Font-Size="8pt" Width="50px"
        Style="text-align:right"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:TextBox id="BookPrice" Runat="Server"
        Text='<%# Eval("BookPrice") %>'
        Font-Size="8pt" Width="50px" 
        Style="text-align:right"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
      Qty<br/>
      <asp:TextBox id="BookQty" Runat="Server"
        Text="0" Font-Size="8pt" Width="30px"
        Style="text-align:right"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:TextBox id="BookQty" Runat="Server"
        Text='<%# Eval("BookQty") %>'
        Font-Size="8pt" Width="30px"
        Style="text-align:right"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
      Sale<br/>
      <asp:CheckBox id="BookSale" Runat="Server"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:CheckBox id="BookSale" Runat="Server"
        Checked='<%# Eval("BookSale") %>'/>
    </ItemTemplate>
  </asp:TemplateColumn>	
  
  <asp:TemplateColumn>
    <HeaderTemplate>
      <br/>
      <asp:Button Text="Insert" CommandName="Insert" Runat="Server"
        Font-Size="7pt" Width="45"/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:Button Text="Update" CommandName="Update" Runat="Server"
        Font-Size="7pt" Width="45"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  <asp:TemplateColumn>
    <HeaderTemplate>
      <br/>
    </HeaderTemplate>
    <ItemTemplate>
      <asp:Button Text="Delete" CommandName="Delete" Runat="Server"
        Font-Size="7pt" Width="45"/>
    </ItemTemplate>
  </asp:TemplateColumn>
  
  </Columns>

</asp:DataGrid>

Listing 9-63. Code for editing with a DataGrid.

DataGrid Editing

The Edit_Record subprogram to perform insert, update, and delete operations is shown below. It is nearly identical to the like-named subprograms for the previous Repeater and DataList. Likewise, data validation routines can be added to the subprogram to test data being edited and inserted.

Sub Edit_Record (Src As Object, Args As DataGridCommandEventArgs)

  Dim BookID As TextBox = Args.Item.FindControl("BookID")
  Dim BookType As DropDownList = Args.Item.FindControl("BookType")
  Dim BookTitle As TextBox = Args.Item.FindControl("BookTitle")
  Dim BookAuthor As TextBox = Args.Item.FindControl("BookAuthor")
  Dim BookDescription As TextBox = Args.Item.FindControl("BookDescription")
  Dim BookPrice As TextBox = Args.Item.FindControl("BookPrice")
  Dim BookQty As TextBox = Args.Item.FindControl("BookQty")
  Dim BookSale As CheckBox = Args.Item.FindControl("BookSale")
  
  If Args.CommandName = "Insert" Then
    
    Dim SQLString As String = "INSERT INTO Products " & _
      "(BookID, BookType, BookTitle, BookAuthor, BookDescription, " & _
      "BookPrice, BookQty, BookSale) VALUES (" & _
      "'" & BookID.Text            & "', " & _
      "'" & BookType.SelectedValue & "', " & _
      "'" & BookTitle.Text         & "', " & _
      "'" & BookAuthor.Text        & "', " & _
      "'" & BookDescription.Text   & "', " & _
      "'" & BookPrice.Text         & "', " & _
      "'" & BookQty.Text           & "', " & _
      BookSale.Checked             & ")"
    BookSource.InsertCommand = SQLString
    BookSource.Insert()
    EditMessage.Text = "Record " & BookID.Text & " inserted"
  
  End If
  
  If Args.CommandName = "Update" Then
    
    Dim SQLString As String = "UPDATE Products SET "  & _
      "BookType = '"         & BookType.SelectedValue & "', " & _
      "BookTitle = '"        & BookTitle.Text         & "', " & _
      "BookAuthor = '"       & BookAuthor.Text        & "', " & _
      "BookDescription = '"  & BookDescription.Text   & "', " & _
      "BookPrice = '"        & BookPrice.Text         & "', " & _
      "BookQty = '"          & BookQty.Text           & "', " & _
      "BookSale = "          & BookSale.Checked       & " "   & _
      "WHERE BookID = '" & BookID.Text & "'"
    BookSource.UpdateCommand = SQLString
    BookSource.Update()
    EditMessage.Text = "Record " & BookID.Text & " updated"
  
  End If
  
  If Args.CommandName = "Delete" Then
    
    Dim SQLString As String = "DELETE FROM Products " & _
                            "WHERE BookID = '" & BookID.Text & "'"
    BookSource.DeleteCommand = SQLString
    BookSource.Delete()
    EditMessage.Text = "Record " & BookID.Text & " deleted"
    
  End If

End Sub

Sub Change_Page (Src As Object, Args As DataGridPageChangedEventArgs)

  DataGridEdit.CurrentPageIndex = Args.NewPageIndex
  DataGridEdit.DataBind()

End Sub

Listing 9-64. Script for DataGrid editing.

For a paged DataGrid, the Change_Page subprogram is called by the OnPageIndexChanged event handler to set the CurrentPageIndex to the NewPageIndex that is passed to the subprogram by a click on a page button.

The Repeater, DataList, and DataGrid represent "older" ASP.NET technologies for displaying and editing bound recordsets. They are being replaced by the new GridView, DetailsView, and FormView controls with built-in editing functionality that requires little or no scripting. These older controls have been updated slightly to work with data source controls, eliminating much of the scripting previously required. However, they still have limitations in the ease-of-use category. The choice normally should be to use the newer controls where possible.