DataStream
LANGUAGES: VB
TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server
Real-World DataGrid Editing
Try this practical approach to editing DataGrid content.
By Wayne S. Freeze
Although DataGrids are useful for displaying tabular data,they leave a lot to be desired if you want to edit the data in the grid.Because you need to display all the fields in the grid, you easily could end upwith a very wide Web page, which forces the user to scroll the pagehorizontally to view the data. To make this situation even worse, you need toadd a couple of columns to the DataGrid that contain buttons allowing the userto edit and delete a particular row.
Rather than use this approach, you might consider using aseparate page to edit a particular row of data. By using a separate page forediting, you can limit the number of columns you include in the DataGrid toonly the most important columns. This keeps the overall width of the grid smallenough to avoid horizontal scrolling. It also means you create a friendlier environmentfor editing a particular row of data.
This article expands the sample DataGrid program(available for download) developed over the last few DataStream articles todemonstrate this approach for editing data.
Link From the DataGrid
Because you are dealing with two independent pages, youneed a method to communicate which row of data you want to edit from theDataGrid page to the edit page. The easiest way to do this is simply to add aparameter to the URL when the page is displayed and retrieve this parameterusing the Request object. It's important that this parameter reference theprimary key or some other value that identifies uniquely the row you want toedit. For example, this URL would load the page with the information forCustomerId 2:
Data11B.aspx?CustomerId=2
Next, convert one of the columns in the DataGrid into ahyperlink column. Stylistically, this column should contain a unique value -such as a name - though it isn't critical. You could use the primary keycolumn, but you might want to drop it from the DataGrid, especially if itcontains an Identity value. After all, this type of value rarely containsuseful information to end users.
Within the DataGrid definition, you need to use an<asp:HyperLinkColumn> definition similar to the one shown below. Thisdefinition contains a few key attributes. The DataNavigateUrl FormatStringattribute has a format string containing the URL to be generated. TheDataNavigateUrlField contains the column from the bound table that will beinserted into the format string. The DataTextField contains the value thatshould be displayed in the in the DataGrid:
<asp:HyperLinkColumnDataNavigateUrlField="CustomerId"
DataNavigateUrlFormatString="Data11B.aspx?CustomerId={0}"
DataTextField="Name" SortExpression="Name"
HeaderText="CustomerName"></asp:HyperLinkColumn>
Although the hyperlinks allow you to display an existingrow, you need to add a hyperlink that will allow a visitor to add a new row tothe database. One way to do this is to use a hyperlink like this:
Data11B.aspx?CustomerId=New
By substituting the value New in place of the primary key,you easily can detect it in the edit page and handle it appropriately. All ofthese changes result in the page you see in Figure 1.
Figure 1. The Customer List Web pagedisplays a DataGrid with a hyperlink column, allowing visitors to jump to a newWeb page where they can edit that particular row.
Retrieve the Edit Data
The edit page is straightforward, containing a table ofcolumn names and values, along with three hyperlink buttons that allow you tosave or delete the row or return to the DataGrid page (see Figure 2).
Figure 2. The Edit Customer pagecontains complete information for the customer, including information thatmight not have been displayed on the DataGrid in an easily editable format.
In the Page_Load event (see Figure 3), you need to handletwo basic conditions. The first condition is when the query string parametercontains a value of New. In this case, you merely need to display a blank pageand let the visitor know they should enter the information for a new customer.The second is when the query string parameter contains a properly formattedprimary key value. In this case the appropriate stored procedure is called toreturn the information about that particular customer and the information iscopied to the Web form.
Private Sub Page_Load(ByVal sender AsSystem.Object, ByVal e As System.EventArgs)
Dim ds As DataSet
Dim dr As DataRow
Dim ConnectionStr As String = ConfigurationSettings.AppSettings("ConnStr")
Dim adpt As SqlDataAdapter
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim Id As Integer
If Not IsPostBack Then
IfRequest.QuerySTring("CustomerId") Is Nothing Then
Status.Text ="New customer"
CustomerId.Text ="New"
ElseIf Request.QueryString("CustomerId") = "New"Then
Status.Text ="New customer"
CustomerId.Text ="New"
Else
Try
Status.Text ="Customer retrieved"
Id =CInt(Request.QueryString("CustomerId"))
ds = New DataSet()
conn = NewSqlConnection(ConnectionStr)
cmd = NewSqlCommand("GetCustomer", conn)
cmd.CommandType =CommandType.StoredProcedure
cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = Id
adpt = New SqlDataAdapter(cmd)
adpt.Fill(ds,"Customer")
ifds.Tables("Customer").Rows.Count = 0 Then
Status.Text ="Customer not found."
Else
dr =ds.Tables("Customer").Rows(0)
CustomerId.Text = dr("CustomerId")
Name.Text =dr("Name")
Street.Text =dr("Street")
City.Text =dr("City")
State.Text =dr("State")
ZipCode.Text =dr("ZipCode")
Phone.Text =dr("Phone")
EMailAddress.Text = dr("EMailAddress")
MailingList.Checked = dr("MailingList")
Comments.Text= dr("Comments")
End If
Catch ex as InvalidCastException
Status.Text ="Invalid Customer Id value."
Catch ex As SqlException
Status.Text ="Database error: " & ex.message
If cmd.Connection.State <> ConnectionState.ClosedThen
cmd.Connection.Close()
End If
Catch ex As Exception
Status.Text ="General error: " & ex.message
If cmd.Connection.State <> ConnectionState.ClosedThen
cmd.Connection.Close()
End If
End Try
End If
End If
Figure 3. ThePage_Load event displays an empty form to enter a new customer. It also canretrieve information about a customer that exists in the database already.
The Page_Load event begins by checking the IsPostBackproperty so the information about a customer is loaded only when the page isfirst loaded. If the CustomerId parameter in the query string doesn't exist,the page request is treated as a new customer. Likewise, if CustomerId containsthe value New, a blank form is returned.
If the CustomerId value contains something else, it willbe converted to an integer and saved into the variable Id. If the conversionfails, the Catch clause will display an error message. Otherwise, a newinstance of the DataSet class is created, along with a new SqlConnectionobject. Next, a SqlCommand object is created that will call the GetCustomerstored procedure. The value stored in Id is stored in the @CustomerId parameterassociated with the stored procedure. Then a new SqlDataAdapter object iscreated using the SqlCommand object. Then the adapter's Fill method retrievesthe requested data from the database into the DataSet object. Finally, if theDataSet object has a table containing at least one row, the values from thefirst row are copied to the Web form; otherwise an error message is displayed.
Update the Database
Once the data is displayed on the form, the user is freeto change any value - except CustomerId, which is displayed using a Labelcontrol. When the user clicks on the Save Customer hyperlink button, theLinkButton1_Click event is fired (see Figure 4), saving the information fromthe form to the database.
Sub LinkButton1_Click(sender AsObject, e As EventArgs)
Dim ConnectionStr As String =ConfigurationSettings.AppSettings("ConnStr")
Dim cmd As SqlCommand
Dim conn As SqlConnection
Dim id As Integer
Try
Status.Text ="Customer saved"
If CustomerId.Text = "New" Then
id = -1
Else
id =CInt(CustomerId.Text)
End If
conn = NewSqlConnection(ConnectionStr)
cmd = NewSqlCommand("PutCustomer", conn)
cmd.CommandType =CommandType.StoredProcedure
cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = Id
cmd.Parameters("@CustomerId").Direction =ParameterDirection.InputOutput
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 64).Value =Name.Text
cmd.Parameters.Add("@Street", SqlDbType.VarChar, 64).Value =Street.Text
cmd.Parameters.Add("@City", SqlDbType.VarChar, 64).Value =City.Text
cmd.Parameters.Add("@State", SqlDbType.Char, 2).Value =State.Text
cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar, 10).Value =ZipCode.Text
cmd.Parameters.Add("@Phone", SqlDbType.VarChar, 32).Value =Phone.Text
cmd.Parameters.Add("@EMailAddress", SqlDbType.VarChar,128).Value = EMailAddress.Text
cmd.Parameters.Add("@MailingList", SqlDbType.Bit).Value =MailingList.Checked
cmd.Parameters.Add("@Comments", SqlDbType.VarChar, 256).Value= Comments.Text
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
CustomerId.Text = cmd.Parameters("@CustomerId").Value.ToString
Catch ex as InvalidCastException
Status.Text ="Invalid Customer Id value."
Catch ex As SqlException
Status.Text ="Database error: " & ex.message
If cmd.Connection.State<> ConnectionState.Closed Then
cmd.Connection.Close()
End If
Catch ex As Exception
Status.Text ="General error: " & ex.message
If cmd.Connection.State<> ConnectionState.Closed Then
cmd.Connection.Close()
End If
End Try
End Sub
Figure 4. TheLinkButton1_Click event saves the information from the Web form to the databaseusing a stored procedure.
This routine begins by assuming everything will workproperly. If the CustomerId field contains New, Id is assigned a value of -1;otherwise it will contain the real CustomerId value. Next, a new storedprocedure object is created, which calls the PutCustomer stored procedure. The PutCustomerstored procedure contains one parameter for each value displayed on the Webform. As each parameter is added to the SqlCommand's Parameters collection, theappropriate type information is supplied and the appropriate value from the Webform is saved. Next, the connection object is opened, then the stored procedureis executed and the connection object is closed.
Note that one of the parameters of the stored procedure isspecial. The CustomerId parameter works in both directions. This means the storedprocedure can return a value as well as use the supplied value. In thissituation, if CustomerId doesn't exist in the database, the stored procedureinserts a new row into the database and returns the new value for CustomerIdthrough this parameter. This value is saved into the CustomerId Label controlwhen the stored procedure has finished.
Figure 5 contains the PutCustomer stored procedure. Thisroutine begins by looking for the row associated with the value stored in@CustomerId. If the row doesn't exist, an Insert statement is used to add therow to the Customers table, and the value stored in @@Identity is saved in the@CustomerId parameter. Otherwise, an Update statement is executed to save thenew values into the database.
CREATE Procedure PutCustomer
@CustomerId Int Out,
@Name Varchar(64),
@Street Varchar(64),
@City Varchar(64),
@State Char(2),
@ZipCode Varchar(10),
@Phone Varchar(32),
@EMailAddress Varchar(128),
@MailingList Bit,
@Comments Varchar(256)
As
If Exists(Select * From Customers Where CustomerId=@CustomerId)
Update Customers
Set
Name=@Name,
Street=@Street,
City=@City,
State=@State,
ZipCode=ZipCode,
Phone=@Phone,
EMailAddress=@EMailAddress,
MailingList=@MailingList,
Comments=@Comments
WhereCustomerId=@CustomerId
Else
Begin
Insert IntoCustomers(
Name,
Street,
City,
State,
ZipCode,
Phone,
EMailAddress,
MailingList,
Comments)
Values(
@Name,
@Street,
@City,
@State,
@ZipCode,
@Phone,
@EMailAddress,
@MailingList,
@Comments)
Set @CustomerId = @@Identity
End
Figure 5. ThePutCustomer stored procedure takes a series of parameters and either inserts anew row into the Customers table (if the value of @CustomerId is less thanzero) or updates the Customers table corresponding to @CustomerId.
Finish the Job
The Delete Customer LinkButton calls a stored procedure todelete the specified customer. It traps an attempt to delete a new customer andreturns an error message to the user. Otherwise the Visual Basic code issimilar to the code executed by the Save Customer LinkButton; the main differenceis the DeleteCustomer stored procedure has only one parameter - @CustomerId.
The event associated with the Return to Customer ListLinkButton contains a single call to Response.Redirect that returns the visitorback to the original DataGrid page. Response.Redirect was used rather thanServer.Transfer because there is no need to hide the new page's URL from thevisitorIit is important that the DataGrid page get a fresh copy of the datafrom the database each time it's displayed. Otherwise the data stored in theSession object would not reflect the latest changes.
Wayne S. Freeze isa full-time computer book author with more than a dozen titles to his credit,including Windows Game Programming with VisualBasic and DirectX (Que) and UnlockingOLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25years of experience using all types of computers, from small, embeddedmicroprocessor control systems to large-scale IBM mainframes. Freeze has amaster's degree in management information systems as well as degrees incomputer science and engineering. You can visit his Web site at http://www.JustPC.comand send him e-mail at mailto:WFreeze@JustPC.com. He lovesreading e-mail from his readers, whose ideas, questions, and insights oftenprovide inspiration for future books and articles.