ASP.NET Tutorial

Record Editing with Scripts

Database editing has become a much simpler process with the GridView, DetailsView, and FormView controls new to ASP.NET. Much of the back-end processing is encapsulated in these controls and their data source controls to greatly reduce the amount of scripting required. Still, developers may wish to design their own editing forms and to write script to handle database additions, changes, and deletions. The following example uses standard controls and scripts to edit the BooksDB.mdb database in emulation of methods employed for the GridView, DetailsView, and FormView.

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

Books:

ID:	DB444
Type:	Database
Title:	Access Database Design
Author:	S. Roman
Description:	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.
Price:	$34.95
Qty:	25
Sale:

Figure 9-29. Database editing with scripts.

Coding the Form Controls

All editing functionality is built into this single application. However, separate forms are supplied for record displays, inserts, updates, and deletes. Each form is enclosed inside a Panel control which is made visible or hidden as different editing functions are called. In effect, the separate forms overlay one another to give the appearance of a single form. Coding for these forms is shown below. An embedded CSS style sheet is provided to format the XHTML and controls.

<style type="text/css">
table#Display    {border-collapse:collapse; width:500px}
table#Display th {font-size:11pt; background-color:#E0E0E0; text-align:left;
                  vertical-align:top}
table#Display td {font-size:11pt}
table#Update     {border-collapse:collapse; width:500px}
table#Update th  {font-size:11pt; background-color:#E0E0E0; text-align:left;
                  vertical-align:top}
table#Update td  {font-size:11pt}
table#Insert     {border-collapse:collapse; width:500px}
table#Insert th  {font-size:11pt; background-color:#E0E0E0; text-align:left;
                  vertical-align:top}
table#Insert td  {font-size:11pt}
table#Delete     {border-collapse:collapse; width:500px}
table#Delete th  {font-size:11pt; background-color:#E0E0E0; text-align:left;
                  vertical-align:top}
table#Delete td  {font-size:11pt}
.buttons         {background-color:#E0E0E0}
</style>

<h3>Book Edit</h3>

<b>Books: </b>
<asp:DropDownList id="BookList" Runat="Server"/>
<asp:Button Text="Select" OnClick="Display_Record" Runat="Server"/><br/>

<asp:Panel Width="500" HorizontalAlign="Right" Runat="Server">
  <asp:Label id="UpdateMSG" Text="&nbsp;" Runat="Server"
  EnableViewState="False" ForeColor="#FF0000"/>
</asp:Panel>

<!-- Code for Display Panel -->

<asp:Panel id="DisplayPanel" Visible="True" Runat="Server">
<table id="Display" border="1">
<tr>
  <th>ID:</th>
  <td><asp:Label id="DisplayID" Runat="Server"/></td>
</tr>
<tr>
  <th>Type:</th>
  <td><asp:Label id="DisplayType" Runat="Server"/></td>
</tr>
<tr>
  <th>Title:</th>
  <td><asp:Label id="DisplayTitle" Runat="Server"/></td>
</tr>
<tr>
  <th>Author:</th>
  <td><asp:Label id="DisplayAuthor" Runat="Server"/></td>
</tr>
<tr>
  <th>Description:</th>
  <td><asp:Label id="DisplayDescription" Runat="Server"
        Style="width:400px; height:100px; overflow:auto"/></td>
</tr>
<tr>
  <th>Price:</th>
  <td><asp:Label id="DisplayPrice" Runat="Server"/></td>
</tr>
<tr>
  <th>Qty:</th>
  <td><asp:Label id="DisplayQty" Runat="Server"/></td>
</tr>
<tr>
  <th>Sale:</th>
  <td><asp:CheckBox id="DisplaySale" Enabled="False" Runat="Server"/></td>
</tr>
<tr>
  <th></th>
  <td class="buttons">
    <asp:Button Text=" Edit Record " Runat="Server"
      OnClick="Load_Update_Form"/>
    <asp:Button Text="Insert Record" Runat="Server"
      OnClick="Load_Insert_Form"/>
    <asp:Button Text="Delete Record" Runat="Server"
      OnClick="Load_Delete_Form"/>
  </td>
</tr>
</table>
</asp:Panel>

<!-- Code for Insert Panel -->

<asp:Panel id="InsertPanel" Visible="False" Runat="Server">
<table id="Insert" border="1">
<tr>
  <th>ID:</th>
  <td><asp:TextBox id="AddID" Runat="Server"
        Width="50" MaxLength="5" EnableViewState="False"/></td>
</tr>
<tr>
  <th>Type:</th>
  <td><asp:DropDownList id="AddType" Runat="Server"/></td>
</tr>
<tr>
  <th>Title:</th>
  <td><asp:TextBox id="AddTitle" Runat="Server"
        Width="300" MaxLength="50" EnableViewState="False"/></td>
</tr>
<tr>
  <th>Author:</th>
  <td><asp:TextBox id="AddAuthor" Runat="Server"
        Width="100" MaxLength="20" EnableViewState="False"/></td>
</tr>
<tr>
  <th>Description:</th>
  <td><asp:TextBox id="AddDescription" Runat="Server"
        TextMode="MultiLine" Width="400px" Rows="4"
        EnableViewState="False"/></td>
</tr>
<tr>
  <th>Price:</th>
  <td><asp:TextBox id="AddPrice" Runat="Server"
        Width="50" MaxLength="6"/ EnableViewState="False"></td>
</tr>
<tr>
  <th>Qty:</th>
  <td><asp:TextBox id="AddQty" Runat="Server"
        Width="50" MaxLength="2" EnableViewState="False"/></td>
</tr>
<tr>
  <th>Sale:</th>
  <td><asp:CheckBox id="AddSale" Runat="Server"
        EnableViewState="False"/></td>
</tr>
<tr>
  <th></th>
  <td class="buttons">
    <asp:Button Text="Insert" OnClick="Insert_Record" Runat="Server"/>
    <asp:Button Text="Cancel" OnClick="Display_Record" Runat="Server"/>
  </td>
</tr>
</table>
</asp:Panel>

<!-- Code for Update Panel -->

<asp:Panel id="UpdatePanel" Visible="False" Runat="Server">
<table id="Update" border="1">
<tr>
  <th>ID:</th>
  <td><asp:Label id="UpdateID" Runat="Server"/></td>
</tr>
<tr>
  <th>Type:</th>
  <td><asp:DropDownList id="UpdateType" Runat="Server"/></td>
</tr>
<tr>
  <th>Title:</th>
  <td><asp:TextBox id="UpdateTitle" Runat="Server"
        Width="300" MaxLength="50"/></td>
</tr>
<tr>
  <th>Author:</th>
  <td><asp:TextBox id="UpdateAuthor" Runat="Server"
        Width="100" MaxLength="20"/></td>
</tr>
<tr>
  <th>Description:</th>
  <td><asp:TextBox id="UpdateDescription" Runat="Server"
        TextMode="MultiLine" Font-Name="Arial"
        Width="400px" Rows="4"/></td>
</tr>
<tr>
  <th>Price:</th>
  <td><asp:TextBox id="UpdatePrice" Runat="Server"
        Width="50" MaxLength="6"/></td>
</tr>
<tr>
  <th>Qty:</th>
  <td><asp:TextBox id="UpdateQty" Runat="Server"
        Width="50" MaxLength="2"/></td>
</tr>
<tr>
  <th>Sale:</th>
  <td><asp:CheckBox id="UpdateSale" Runat="Server"/></td>
</tr>
<tr>
  <th></th>
  <td class="buttons">
    <asp:Button Text="Update" OnClick="Update_Record" Runat="Server"/>
    <asp:Button Text="Cancel" OnClick="Display_Record" Runat="Server"/>
	</td>
</tr>
</table>
</asp:Panel>

<!-- Code for Delete Panel -->

<asp:Panel id="DeletePanel" Visible="False" Runat="Server">
<table id="Delete" border="1">
<tr>
  <th>ID:</th>
  <td><asp:Label id="DeleteID" Runat="Server"/></td>
</tr>
<tr>
  <th>Type:</th>
  <td><asp:Label id="DeleteType" Runat="Server"/></td>
</tr>
<tr>
  <th>Title:</th>
  <td><asp:Label id="DeleteTitle" Runat="Server"/></td>
</tr>
<tr>
  <th>Author:</th>
  <td><asp:Label id="DeleteAuthor" Runat="Server"/></td>
</tr>
<tr>
  <th>Description:</th>
  <td><asp:Label id="DeleteDescription" Runat="Server"
        Style="width:400px; height:100px; overflow:auto"/></td>
</tr>
<tr>
  <th>Price:</th>
  <td><asp:Label id="DeletePrice" Runat="Server"/></td>
</tr>
<tr>
  <th>Qty:</th>
  <td><asp:Label id="DeleteQty" Runat="Server"/></td>
</tr>
<tr>
  <th>Sale:</th>
  <td><asp:CheckBox id="DeleteSale" Enabled="False" Runat="Server"/></td>
</tr>
<tr>
  <th></th>
  <td class="buttons">
    <asp:Label Text="Delete this record?" Runat="Server"
      ForeColor="#FF0000"/>
    <asp:Button Text="Yes" OnClick="Delete_Record" Runat="Server"/>
    <asp:Button Text=" No " OnClick="Display_Record" Runat="Server"/></td>
</tr>
</table>
</asp:Panel>

Listing 9-65. Code for editing with server controls.

Scripting the Initial Record Display

Selection of a record for display or editing is made through a DropDownList and accompanying Button. When the page first opens, the DisplayPanel is initially visible and the first product in the list is selected for display. Code for the Page_Load subprogram and other called subprograms to produce this initial display are shown below.

<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim DBReader As OleDbDataReader
Dim SQLString As String

Sub Page_Load

  If Not Page.IsPostBack Then
    
    Bind_Book_DropDown
    BookList.SelectedIndex = 0
    Display_Record(Nothing, Nothing)
  
  End If
	
End Sub

Sub Bind_Book_DropDown

  '-- Load book titles into drop-down list
  DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()
  SQLString = "SELECT BookID, BookTitle FROM Books ORDER BY BookTitle"
  DBCommand = New OleDbCommand(SQLString, DBConnection)
  DBReader = DBCommand.ExecuteReader()
  BookList.DataSource = DBReader
  BookList.DataTextField = "BookTitle"
  BookList.DataValueField = "BookID"
  BookList.DataBind()
  DBReader.Close()
  DBConnection.Close()
  
End Sub

Sub Display_Record (Source As Object, Args As EventArgs)

  DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()
  SQLString = "SELECT * FROM Books WHERE " & _
              "BookID='" & BookList.SelectedValue & "'"
  DBCommand = New OleDbCommand(SQLString, DBConnection)
  DBReader = DBCommand.ExecuteReader()
  DBReader.Read()
  DisplayID.Text = DBReader("BookID")
  DisplayType.Text = DBReader("BookType")
  DisplayTitle.Text = DBReader("BookTitle")
  DisplayAuthor.Text = DBReader("BookAuthor")
  DisplayDescription.Text = DBReader("BookDescription")
  DisplayPrice.Text = FormatCurrency(DBReader("BookPrice"))
  DisplayQty.Text = DBReader("BookQty")
  DisplaySale.Checked = DBReader("BookSale")
  
  DBReader.Close()
  DBConnection.Close()
  
  InsertPanel.Visible = False
  UpdatePanel.Visible = False
  DeletePanel.Visible = False
  DisplayPanel.Visible = True
  
End Sub


...
</SCRIPT>

Listing 9-66. Script to select a record for editing.

It is important to remember to import the System.Data.OleDb namespace to the page in order to support script access to the database. Also, the database connection object, command object, data reader object, and SQLString variable are globally declared since they are used in numerous subprograms.

The Page_Load subprogram builds the initial page display. It calls the Bind_Book_DropDown subprogram to load the DropDownList with book titles and IDs, sets the SelectedIndex of the list to 0 to programmaticaly select the first item in the list, and calls the Display_Record subprogram to display this selected record. The Bind_Book_DropDown subprogram binds book titles to the DataTextField property of the DropDownList and binds book IDs to the DataValueField property. Thus, book titles are shown in the list while book IDs are used for record retrieval.

After loading the DropDownList and selecting the first item in the list, the Display_Record subprogram is called. This subprogram loads the DisplayPanel with all fields from the selected record.

The Display_Record subprogram is called on different occasions by different buttons and by different subprograms to redisplay the display form. Because it is called by buttons, the subprogram must include a signature appropriate for button calls: Display_Record (Source As Object, Args As EventArgs). However, this subprogram is also called by other subprograms for which button arguments are not automatically supplied. Therefore, subprograms which call Display_Record must pass along null arguments (Nothing) in place of expected button arguments. For this reason, the call to this subprogram is coded as Display_Record(Nothing, Nothing) in the Page_Load subprogram.

The Display_Record subprogram opens a connection to the database and issues an SQL SELECT statement to retrieve the record with an BookID matching that of the number selected from the DropDownList. After reading this single record, the subprogram assigns its fields to the output controls in the display form. All of these controls are TextBoxes except for the CheckBox showing the value of the BookSale field. Notice that every time Display_Record is called it hides all other Panels (which may be visible during an insert, update, and delete operation) and makes the DisplayPanel visible.

The DisplayPanel shows record editing buttons for additions, changes, and deletions of records. When the "Insert Record" button is clicked, the Load_Insert_Form subprogram is called to replace the DisplayPanel with the InsertPanel containing the form and appropriate buttons for added a new record to the database. Its code is shown below.

Part of the insert form is a DropDownList (id="AddType") containing book types from which to choose. Therefore, this list must be loaded prior to displaying the insert form. Once the BookType values are bound to the control, the DisplayPanel is hidden and the InsertPanel is made visible.

At this point, users can fill in new information on the insert form and click its "Insert" button to write the new information to the database. This action calls the Insert_Record subprogram whose code is shown below.

An SQL INSERT statement is composed from data values taken from the input form. The statement is issued through the command object's ExecuteNonQuery() method, the method used when no records are returned from an SQL query. After writing the new record to the database, the DropDownList of book titles and IDs is recreated by the Bind_Book_DropDown subprogram to reflect this addition to the database. The DropDownList's SelectedValue property is set to the new book ID value so that when the Display_Record subprogram is called to display this record the DropDownList will show this new book.

When the "Edit Record" button is clicked in the DisplayPanel, the Load_Update_Form subprogram is called to replace the DisplayPanel, with the UpdatePanel containing the form and appropriate buttons for updated an existing record in the database. Its code is shown below.

Again, a DropDownList of book types must be loaded for selecting changed values of this data item. Then a book record is retrieved to populate the update form. The identify of this record is taken from the current selection showing in the book title DropDownList. After controls on the update form are populated, the DisplayPanel is hidden and the UpdatePanel is revealed. Now, changes can be made to the record (with the exception of the BookID key field), and the record rewritten to the database. Rewriting occurs with a click on the "Update" button appearing at the bottom of the update form, and a call to the Update_Record subprogram shown below.

An SQL UPDATE statement is composed from values taken from the update form, and is issued through the command object's ExecuteNonQuery() method to update the database. The current book ID from the UpdateID Label is assigned as the SelectedValue of the selection DropDownList so that this book is retrieved when the Display_Record subprogram is called to display the changed record.

When the "Delete Record" button is clicked in the DisplayPanel, the Load_Delete_Form subprogram is called to replace the DisplayPanel with the DeletePanel containing the form and appropriate buttons for deleting an existing record in the database. Its code is shown below.

The record to be deleted is retrieved from the database and populates the delete form. The currently visible DisplayPanel is hidden and the DeletePanel is made visible, revealing the buttons for record deletion. When the "Yes" button is clicked in this Panel, the Delete_Record subprogram is called.

This subprogram takes the book ID displayed on the delete form and uses it to compose an SQL DELETE statement to delete the record. Since deleting a record affects the books displayed in the DropDownList, the list needs re-binding with the Bind_Book_DropDown subprogram. Then the Display_Record subprogram is called to redisplay the initial record in the database.

The above editing subprograms do not contain validation routines to check on the correctness of added or changed data. These can be added to existing subprograms without too much complication.

As you can see, there is quite a bit of extra coding needed to create your own editing forms. As a general rule, you probably will prefer to stick with the GridView, DetailsView, and FormView controls for their built-in editing conveniences. However, producing your own editing applications is an available option.