Edit DataGrid Rows

DataStream

LANGUAGES: VB

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

Edit DataGrid Rows

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

By Wayne S. Freeze

In the last installment of this column ( Add a DeleteColumn 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 usersdelete a row from the database. This time, you ll learn how to use theDataGrid s facilities to let users update the contents of a row directly withinthe DataGrid.

In order to edit the contents of a row, you must includean Edit button column (see Figure 1). Like the Delete column, the Edit columnlets 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 columncontaining the Edit button.

When the user clicks on the Edit button, the contents ofeach cell that can be edited is displayed as a textbox, and the Edit button isreplaced with two buttons an Update button and a Cancel button (see Figure2).

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

Pressing the Cancel button removes the textboxes andreturns the DataGrid to normal. Pressing the Update button triggers the code inyour program to save the changes to the database before returning the DataGridto normal.

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

You can specify which columns users can edit by using thisReadOnly attribute:

  SortExpression="CustomerId" HeaderText="Customer Id">      When the ReadOnly attribute is set to True, the cell willnot be converted to a textbox, ensuring its contents won t be changed.  Edit the Row Although the DataGrid control provides the raw tools toedit a row, you still need to include the appropriate code in your applicationto handle the transitions between the normal state of the DataGrid and the editstate. These state changes are managed by special versions of the ItemCommandevent. Clicking on the Edit button triggers the EditCommand event; clicking onthe Cancel button triggers the CancelCommand event; and clicking on the Updatebutton triggers the UpdateCommand event.   You control the row you want to edit by using theEditItemIndex property. Setting this property to the index of the row you wantto edit causes all the cells not marked as ReadOnly to be displayed astextboxes. When you re finished editing a row, simply set theEditItemIndex property to -1 to display the row as normal.   This routine shows you how to handle the EditCommandevent: Sub DataGrid1_EditCommand(s AsObject, _  e AsDataGridCommandEventArgs) Dim i As Integeri = DataGrid1.CurrentPageIndexDataGrid1.EditItemIndex = e.Item.ItemIndexDataGrid1.DataSource = Session("Customers") DataGrid1.DataBind()UpdatePageLocation(i) End Sub This routine begins by saving the location of the currentpage. Then the EditItemIndex property is set to the ItemIndex value of thecurrently selected row. Next, the data is re-bound to the DataGrid. Finally,the UpdatePageLocation routine is called to rebuild the page labels below theDataGrid.   As you might expect, the CancelCommand event looks similarto the EditCommand event. The only difference is that the EditItemIndex is setto -1 in order to convert the textboxes back to normal:Sub DataGrid1_CancelCommand(s AsObject, _  e AsDataGridCommandEventArgs) Dim i As Integeri = DataGrid1.CurrentPageIndexDataGrid1.EditItemIndex = -1DataGrid1.DataSource = Session("Customers") DataGrid1.DataBind()UpdatePageLocation(i) End Sub  Update a Row The real work in this sample is performed by theUpdateCommand event (see Figure 3). This routine begins by preserving thecurrently displayed page in the datagrid and setting the EditItemIndex to -1 tohide the editing textboxes.  Sub DataGrid1_UpdateCommand(s AsObject, _  e AsDataGridCommandEventArgs) Dim i As IntegerDim ConnectionStr As String _  =ConfigurationSettings.AppSettings("ConnStr") Dim cmd As SqlCommandDim conn As SqlConnectioni = DataGrid1.CurrentPageIndexDataGrid1.EditItemIndex = -1Try  Label1.Text ="Customer saved"   conn = NewSqlConnection(ConnectionStr)   cmd = NewSqlCommand("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 IfCatch ex As Exception  Label1.Text ="General error: " & ex.message  If cmd.Connection.State<> ConnectionState.Closed Then     cmd.Connection.Close()  End IfEnd TryRefreshData()UpdatePageLocation(i) End SubFigure3. The UpdateCommand event takes the newly edited information andsaves it to the database using a stored procedure, thenit 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 Webpage, is set to indicate that the database operation was successful. Then aSqlConnection object is created using a connection string extracted fromweb.config. A SqlCommand object is then created using the newly createdconnection object. The SqlParameter objects are then added to the commandobject. The Customer Id column is marked as read-only in theDataGrid because it contains the primary key for the customer value. Thus thisvalue can be extracted by using the Cells(0).Textproperty from the datagrid, then assigned to the @CustomerId SqlParameterobject.   But the remaining parameter values can t be extracted fromthe cell directly; instead, they must be extracted from the textbox containedwithin the cell. To get an object reference to the TextBox control, you need touse the cell s Controls collection. Because the TextBox control is the firstand only control in the collection, it has an index of zero. To reference theText property of the TextBox control, the object reference must be cast to aTextBox using the CType function. Thus, you can use an expression such as CType(e.Item.Cells(1).Controls(0), TextBox).Text to assignthe 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 aretrapped and handled in the appropriate Catch clause. Finally, the datadisplayed in the grid is refreshed to capture the updated row, and the paginginformation below the grid is then regenerated.   The sample code in thisarticle is available for download.  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 to mailto:[email protected]. He lovesreading e-mail from his readers, whose ideas, questions, and insights oftenprovide inspiration for future books and articles.