Edit DataGrid Rows

Let users update the contents of a row directly within the DataGrid.

DataStream

LANGUAGES: VB

TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server

 

Edit DataGrid Rows

Let users update the contents of a row directly within the DataGrid.

 

By Wayne S. Freeze

 

In the last installment of this column ( Add a Delete Column to Your DataGrid http://www.aspnetpro.com/NewsletterArticle/2003/03/asp200303wf_l/asp200303wf_l.asp), I showed you how to add a Delete button column to a DataGrid, which lets users delete a row from the database. This time, you ll learn how to use the DataGrid s facilities to let users update the contents of a row directly within the DataGrid.

 

In order to edit the contents of a row, you must include an Edit button column (see Figure 1). Like the Delete column, the Edit column lets you define either a link button or a push button.

 


Figure 1. Editing a row inside a DataGrid means you need to add a second button column containing the Edit button.

 

When the user clicks on the Edit button, the contents of each cell that can be edited is displayed as a textbox, and the Edit button is replaced with two buttons an Update button and a Cancel button (see Figure 2).

 


Figure 2. The facilities inside the DataGrid let you edit a row of data using a series of textboxes along with buttons that allow the user either to approve or cancel their changes.

 

Pressing the Cancel button removes the textboxes and returns the DataGrid to normal. Pressing the Update button triggers the code in your program to save the changes to the database before returning the DataGrid to normal.

 

The downside to this approach is each of the textboxes consumes more space than the normal contents of each cell. Consequently, your users might be forced to scroll the page from side to side while editing the contents of the row.

 

You can specify which columns users can edit by using this ReadOnly attribute:

 

  SortExpression="CustomerId" HeaderText="Customer Id">

  

 

When the ReadOnly attribute is set to True, the cell will not be converted to a textbox, ensuring its contents won t be changed.

 

Edit the Row

Although the DataGrid control provides the raw tools to edit a row, you still need to include the appropriate code in your application to handle the transitions between the normal state of the DataGrid and the edit state. These state changes are managed by special versions of the ItemCommand event. Clicking on the Edit button triggers the EditCommand event; clicking on the Cancel button triggers the CancelCommand event; and clicking on the Update button triggers the UpdateCommand event.

 

You control the row you want to edit by using the EditItemIndex property. Setting this property to the index of the row you want to edit causes all the cells not marked as ReadOnly to be displayed as textboxes. When you re finished editing a row, simply set the EditItemIndex property to -1 to display the row as normal.

 

This routine shows you how to handle the EditCommand event:

Sub DataGrid1_EditCommand(s As Object, _

  e As DataGridCommandEventArgs)

Dim i As Integer

i = DataGrid1.CurrentPageIndex

DataGrid1.EditItemIndex = e.Item.ItemIndex

DataGrid1.DataSource = Session("Customers")

DataGrid1.DataBind()

UpdatePageLocation(i)

End Sub

This routine begins by saving the location of the current page. Then the EditItemIndex property is set to the ItemIndex value of the currently selected row. Next, the data is re-bound to the DataGrid. Finally, the UpdatePageLocation routine is called to rebuild the page labels below the DataGrid.

 

As you might expect, the CancelCommand event looks similar to the EditCommand event. The only difference is that the EditItemIndex is set to -1 in order to convert the textboxes back to normal:

Sub DataGrid1_CancelCommand(s As Object, _

  e As DataGridCommandEventArgs)

Dim i As Integer

i = DataGrid1.CurrentPageIndex

DataGrid1.EditItemIndex = -1

DataGrid1.DataSource = Session("Customers")

DataGrid1.DataBind()

UpdatePageLocation(i)

End Sub

 

Update a Row

The real work in this sample is performed by the UpdateCommand event (see Figure 3). This routine begins by preserving the currently displayed page in the datagrid and setting the EditItemIndex to -1 to hide the editing textboxes.

 

Sub DataGrid1_UpdateCommand(s As Object, _

  e As DataGridCommandEventArgs)

Dim i As Integer

Dim ConnectionStr As String _

  = ConfigurationSettings.AppSettings("ConnStr")

Dim cmd As SqlCommand

Dim conn As SqlConnection

i = DataGrid1.CurrentPageIndex

DataGrid1.EditItemIndex = -1

Try

  Label1.Text = "Customer saved"

  conn = New SqlConnection(ConnectionStr)

  cmd = New SqlCommand("UpdateCustomer", conn)

  cmd.CommandType = CommandType.StoredProcedure

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

     = CInt(e.Item.Cells(0).Text)

  cmd.Parameters.Add("@Name", SqlDbType.VarChar, 64). _

     Value = CType(e.Item.Cells(1).Controls(0), _

     TextBox).Text

  cmd.Parameters.Add("@City", SqlDbType.VarChar, 64). _

     Value = CType(e.Item.Cells(2).Controls(0), _

     TextBox).Text

  cmd.Parameters.Add("@State", SqlDbType.Char, 2).Value _

     = CType(e.Item.Cells(3).Controls(0), TextBox).Text

  cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar, 10). _

     Value = CType(e.Item.Cells(4).Controls(0), _

     TextBox).Text

  cmd.Parameters.Add("@Phone", SqlDbType.VarChar, 32). _

     Value = CType(e.Item.Cells(5).Controls(0), _

     TextBox).Text

  cmd.Parameters.Add("@EMailAddress", SqlDbType.VarChar, _

     128).Value = CType(e.Item.Cells(6).Controls(0), _

     TextBox).Text

  cmd.Connection.Open()

  cmd.ExecuteNonQuery()

  cmd.Connection.Close()

Catch ex As SqlException

  Label1.Text = "Database error: " & ex.message

  If cmd.Connection.State <> ConnectionState.Closed Then

      cmd.Connection.Close()

  End If

Catch ex As Exception

  Label1.Text = "General error: " & ex.message

  If cmd.Connection.State <> ConnectionState.Closed Then

     cmd.Connection.Close()

  End If

End Try

RefreshData()

UpdatePageLocation(i)

End Sub

Figure 3. The UpdateCommand event takes the newly edited information and saves it to the database using a stored procedure, then it refreshes the information in the DataGrid.

 

Next, a Try statement surrounds the database update logic. The Label1 control, which is used to display status information on the Web page, is set to indicate that the database operation was successful. Then a SqlConnection object is created using a connection string extracted from web.config. A SqlCommand object is then created using the newly created connection object. The SqlParameter objects are then added to the command object.

The Customer Id column is marked as read-only in the DataGrid because it contains the primary key for the customer value. Thus this value can be extracted by using the Cells(0).Text property from the datagrid, then assigned to the @CustomerId SqlParameter object.

 

But the remaining parameter values can t be extracted from the cell directly; instead, they must be extracted from the textbox contained within the cell. To get an object reference to the TextBox control, you need to use the cell s Controls collection. Because the TextBox control is the first and only control in the collection, it has an index of zero. To reference the Text property of the TextBox control, the object reference must be cast to a TextBox using the CType function. Thus, you can use an expression such as CType(e.Item.Cells(1).Controls(0), TextBox).Text to assign the value from the second column of the DataGrid to the parameter object.

 

This routine concludes by opening the connection, executing the stored procedure, and closing the connection. Any errors are trapped and handled in the appropriate Catch clause. Finally, the data displayed in the grid is refreshed to capture the updated row, and the paging information below the grid is then regenerated.

 

The sample code in this article is available for download.

 

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 to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish