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 up with a very wide Web page, which forces the user to scroll the page horizontally to view the data. To make this situation even worse, you need to add a couple of columns to the DataGrid that contain buttons allowing the user to edit and delete a particular row.

Rather than use this approach, you might consider using a separate page to edit a particular row of data. By using a separate page for editing, you can limit the number of columns you include in the DataGrid to only the most important columns. This keeps the overall width of the grid small enough to avoid horizontal scrolling. It also means you create a friendlier environment for editing a particular row of data.

This article expands the sample DataGrid program (available for download) developed over the last few DataStream articles to demonstrate this approach for editing data.

Link From the DataGrid

Because you are dealing with two independent pages, you need a method to communicate which row of data you want to edit from the DataGrid page to the edit page. The easiest way to do this is simply to add a parameter to the URL when the page is displayed and retrieve this parameter using the Request object. It's important that this parameter reference the primary key or some other value that identifies uniquely the row you want to edit. For example, this URL would load the page with the information for CustomerId 2:

Data11B.aspx?CustomerId=2

Next, convert one of the columns in the DataGrid into a hyperlink column. Stylistically, this column should contain a unique value - such as a name - though it isn't critical. You could use the primary key column, but you might want to drop it from the DataGrid, especially if it contains an Identity value. After all, this type of value rarely contains useful information to end users.

Within the DataGrid definition, you need to use an <asp:HyperLinkColumn> definition similar to the one shown below. This definition contains a few key attributes. The DataNavigateUrl FormatString attribute has a format string containing the URL to be generated. The DataNavigateUrlField contains the column from the bound table that will be inserted into the format string. The DataTextField contains the value that should be displayed in the in the DataGrid:

<asp:HyperLinkColumn DataNavigateUrlField="CustomerId"

DataNavigateUrlFormatString="Data11B.aspx?CustomerId={0}"

DataTextField="Name" SortExpression="Name"

HeaderText="Customer Name"></asp:HyperLinkColumn>

Although the hyperlinks allow you to display an existing row, you need to add a hyperlink that will allow a visitor to add a new row to the 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 of these changes result in the page you see in Figure 1.

Figure 1. The Customer List Web page displays a DataGrid with a hyperlink column, allowing visitors to jump to a new Web page where they can edit that particular row.

Retrieve the Edit Data

The edit page is straightforward, containing a table of column names and values, along with three hyperlink buttons that allow you to save or delete the row or return to the DataGrid page (see Figure 2).

Figure 2. The Edit Customer page contains complete information for the customer, including information that might not have been displayed on the DataGrid in an easily editable format.

In the Page_Load event (see Figure 3), you need to handle two basic conditions. The first condition is when the query string parameter contains a value of New. In this case, you merely need to display a blank page and let the visitor know they should enter the information for a new customer. The second is when the query string parameter contains a properly formatted primary key value. In this case the appropriate stored procedure is called to return the information about that particular customer and the information is copied to the Web form.

Private Sub Page_Load(ByVal sender As System.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

   If Request.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 = New SqlConnection(ConnectionStr)

         cmd = New SqlCommand("GetCustomer", conn)

         cmd.CommandType = CommandType.StoredProcedure

         cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = Id

         adpt = New SqlDataAdapter(cmd)

         adpt.Fill(ds, "Customer")

         if ds.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.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 If

End If

Figure 3. The Page_Load event displays an empty form to enter a new customer. It also can retrieve information about a customer that exists in the database already.

The Page_Load event begins by checking the IsPostBack property so the information about a customer is loaded only when the page is first loaded. If the CustomerId parameter in the query string doesn't exist, the page request is treated as a new customer. Likewise, if CustomerId contains the value New, a blank form is returned.

If the CustomerId value contains something else, it will be converted to an integer and saved into the variable Id. If the conversion fails, the Catch clause will display an error message. Otherwise, a new instance of the DataSet class is created, along with a new SqlConnection object. Next, a SqlCommand object is created that will call the GetCustomer stored procedure. The value stored in Id is stored in the @CustomerId parameter associated with the stored procedure. Then a new SqlDataAdapter object is created using the SqlCommand object. Then the adapter's Fill method retrieves the requested data from the database into the DataSet object. Finally, if the DataSet object has a table containing at least one row, the values from the first 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 free to change any value - except CustomerId, which is displayed using a Label control. When the user clicks on the Save Customer hyperlink button, the LinkButton1_Click event is fired (see Figure 4), saving the information from the form to the database.

Sub LinkButton1_Click(sender As Object, 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 = New SqlConnection(ConnectionStr)

   cmd = New SqlCommand("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. The LinkButton1_Click event saves the information from the Web form to the database using a stored procedure.

This routine begins by assuming everything will work properly. If the CustomerId field contains New, Id is assigned a value of -1; otherwise it will contain the real CustomerId value. Next, a new stored procedure object is created, which calls the PutCustomer stored procedure. The PutCustomer stored procedure contains one parameter for each value displayed on the Web form. As each parameter is added to the SqlCommand's Parameters collection, the appropriate type information is supplied and the appropriate value from the Web form is saved. Next, the connection object is opened, then the stored procedure is executed and the connection object is closed.

Note that one of the parameters of the stored procedure is special. The CustomerId parameter works in both directions. This means the stored procedure can return a value as well as use the supplied value. In this situation, if CustomerId doesn't exist in the database, the stored procedure inserts a new row into the database and returns the new value for CustomerId through this parameter. This value is saved into the CustomerId Label control when the stored procedure has finished.

Figure 5 contains the PutCustomer stored procedure. This routine 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 the row to the Customers table, and the value stored in @@Identity is saved in the @CustomerId parameter. Otherwise, an Update statement is executed to save the new 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

      Where CustomerId=@CustomerId

Else

   Begin

      Insert Into Customers(

         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. The PutCustomer stored procedure takes a series of parameters and either inserts a new row into the Customers table (if the value of @CustomerId is less than zero) or updates the Customers table corresponding to @CustomerId.

Finish the Job

The Delete Customer LinkButton calls a stored procedure to delete the specified customer. It traps an attempt to delete a new customer and returns an error message to the user. Otherwise the Visual Basic code is similar to the code executed by the Save Customer LinkButton; the main difference is the DeleteCustomer stored procedure has only one parameter - @CustomerId.

The event associated with the Return to Customer List LinkButton contains a single call to Response.Redirect that returns the visitor back to the original DataGrid page. Response.Redirect was used rather than Server.Transfer because there is no need to hide the new page's URL from the visitorIit is important that the DataGrid page get a fresh copy of the data from the database each time it's displayed. Otherwise the data stored in the Session object would not reflect the latest changes.

Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:WFreeze@JustPC.com. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.