Building a DataGrid Helper Control for ASP.NET 1.x: Part 2
page 2 of 5
by Li Chen
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 25487/ 53

A Typical Data Access Page with Update and Delete Capability

[Download Code]

We will first look at what is involved in creating a typical data access page with update and delete functionality by examining an example. A live demo can be found at http://www.dotneteer.com/projects/DataGridHelper/v2/TypicalDataAccessPage.aspx. The page is an enhancement over the same page demonstrated in the first part of this article. I made the following enhancements:

1. Add a button column for the Edit/Update/Cancel commands. Add another button column for the Delete command.
2. Set the AutoGenerateColumns property to false. Instead of letting the DataGrid control to generate the columns automatically, I added some bound columns to the DataGrid control at design time. This allows us to have finer control over the behavior of the DataGrid, such as setting user-friendly headers, the format for the data displayed, as well as the sort expression. More importantly, we can now make some columns read-only--we do not want users to edit an identity column.
3. Set the DataKeyField property to the name of the primary key column of the table we are going to edit. The DataGrid control will then provide the value of the primary key through the DataKeys property at run-time, so we can use it to update and delete a record.
4. Add code to handle the EditCommand, CancelCommand, UpdateCommand and DeleteCommand events of the DataGrid.

The SqlDataAdapter member in the page was configured using the "Data Adapter Configuration Wizard."  Using "advanced options," we turned off the "Use optimistic concurrency" and "Refresh dataset" options because we do not use them.

The code that handles the EditCommand and CancelCommand events is essentially repetitive, as shown below:

private void dataGridProducts_EditCommand(object source, 
 System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
 dataGridProducts.EditItemIndex = e.Item.ItemIndex;
 bindGrid();
} 

private void dataGridProducts_CancelCommand(object source, 
 System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
 dataGridProducts.EditItemIndex = -1;
 bindGrid();
}

We can easily move this code to the DataGridHelper control using the same technique used in Part 1 of this series.

Now let us take a look of the code that handles the DeleteCommand event:

private void dataGridProducts_DeleteCommand(object source, 
 System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
 SqlCommand cmd = sqlDataAdapterProducts.DeleteCommand;
 Object key = dataGridProducts.DataKeys[e.Item.ItemIndex];
 try
 {
  cmd.Parameters["@ProductId"].Value = key;
  sqlConnection.Open();
  cmd.ExecuteNonQuery();
  lblMsg.Text = "Product " + key.ToString() 
   + " successfully deleted.";
  bindGrid();
 }
 catch (Exception ex)
 {
lblMsg.Text = "Failed to delete Product " + key.ToString() 
 + "due to following error:<BR>" + ex.ToString();
 }
}

This code basically retrieves the value of primary key column from the DataKeys property of the DataGrid control and sets it to the parameters of the delete command and then executes the command.

At last, let us take a look of the code that handles the UpdateCommand event:

private void dataGridProducts_UpdateCommand(object source, 
 System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
 SqlCommand cmd = sqlDataAdapterProducts.UpdateCommand;
 Object key = dataGridProducts.DataKeys[e.Item.ItemIndex];
 try
 {
  cmd.Parameters["@Original_ProductId"].Value = key;
    
  String[] cols = {"@Original_ProductId","@ProductName", "@SupplierID", 
   "@CategoryID""@QuantityPerUnit", "@UnitPrice", "@UnitsInStock", 
   "@UnitsOnOrder", "@ReorderLevel", "@Discontinued"};
    
  int numCols = e.Item.Cells.Count;
  for (int i=3; i<numCols; i++) //skip first, second, third and last column
  {
   String colvalue =((System.Web.UI.WebControls.TextBox)
    e.Item.Cells[i].Controls[0]).Text;
                 
   cmd.Parameters[cols[i-2]].Value = colvalue;
  }   sqlConnection.Open();
  cmd.ExecuteNonQuery();
  lblMsg.Text = "Product " + key.ToString() + " successfully updated.";
  dataGridProducts.EditItemIndex = -1;
  bindGrid();
 }
 catch (Exception ex)
 {
  lblMsg.Text = "Failed to update Product " + key.ToString() 
   + "due to following error:<BR>" + ex.ToString();
 }
}

The code for the UpdateCommand event was borrowed from the ASP.NET QuickStart tutorial.  It is no doubt the ugliest part of DataGrid programming. The first part of the task is to locate the table cell that corresponds to the database table column of interest. Once the cell is located, we need to find the control in the cell that was used for data input. We can find the control by either looping through the Controls collection or using the FindControl method. We can then update the database using the retrieved values.


View Entire Article

User Comments

Title: Data Grid update   
Name: Rakesh Krishna
Date: 2008-08-20 3:18:10 AM
Comment:
plss help me how to write the code to update linkbutton in grid
Title: how to select all rows of a datagrid   
Name: dhananjay
Date: 2007-05-15 8:20:06 AM
Comment:
how to select all rows of a datagrid
Title: not bad   
Name: srinithy
Date: 2007-04-20 6:38:27 AM
Comment:
such codings is not bad . if there are some outputs means it is very useful to the users.
Title: Ok Good   
Name: S.R.Vidhyasagargee
Date: 2007-03-31 6:32:11 AM
Comment:
I need datagrid select,delete,update move next, delete
anybudy know this concept and I need Handling two grid shares information. Please e-mail me id vsagarin_2003@yahoo.co.in
Title: useless   
Name: jjjjjjjjjjjjjjjj
Date: 2007-03-29 4:29:45 AM
Comment:
this article was really useless
it does not carry any useful information
Title: DATE   
Name: KUSUM
Date: 2007-03-26 4:20:26 AM
Comment:
I was working on datagrid and this article made my work easy. Once again i am thanking the Author.
Title: Never seen before   
Name: govardhan
Date: 2007-01-05 12:19:08 AM
Comment:
This article really helped me a lot,thanks for this team.
Title: datagrid in dot net   
Name: ramesh
Date: 2006-11-15 2:34:53 AM
Comment:
I need datagrid select,delete,update move next, delete
anybudy know this concept mail me id rameshindya@yahoo.com
Title: Thanks   
Name: Arun ::.
Date: 2006-10-12 7:32:55 AM
Comment:
Hai,
I was working on datagrid and this article made my work easy. Once again i am thanking the Author.
Title: Easy to learn   
Name: Umapathi Naidu
Date: 2006-09-26 5:21:04 AM
Comment:
I am very happy work with asp.net, If got ant new task related to asp.net,c#, vb.net.

This site is very helpfull to me.

regards,
Naidu
Title: how set multi line with cel in the data grid?   
Name: suresh
Date: 2006-07-29 2:13:54 AM
Comment:
how set multi line with cel in the data grid?
Title: Wonderful   
Name: Sudhakar
Date: 2006-05-03 5:35:57 AM
Comment:
this is really very useful artical to evevy one
Title: Delete message   
Name: Ramesh
Date: 2006-02-23 4:49:22 AM
Comment:
Can you please send me the sample code for datagrid button column conformation messsage for deletion

Regards
Ramesh.M
Title: nice one   
Name: vishnu
Date: 2005-11-30 5:19:33 AM
Comment:
very useful article. waiting for u r next article
Title: Thank you   
Name: Shyam
Date: 2005-10-31 5:10:10 AM
Comment:
The article is very interesting and helpful. Thanks for the article
Title: Mr   
Name: Navid Iqbal
Date: 2005-06-03 11:50:52 AM
Comment:
Data Grid Control is a very useful option is very great option in asp.net, i thank to you that u provided me the Code.
Title: it help me from my one hours mind exercies   
Name: rajat
Date: 2005-03-16 1:57:55 AM
Comment:
thx for this
Title: Wicked   
Name: Hak
Date: 2005-03-11 6:40:28 AM
Comment:
I loved it, it will more helpfull if we can't ask users to print before deleting. i guess will need a print option.

Any help on that?

TA
Title: Update for VB   
Name: David
Date: 2005-03-08 3:58:57 PM
Comment:
Is VB code needed for the edit and update command to work? I don't see the update and edit VB code in the downloaded example. I keep getting an 'expected parameter' error.

Any help would be great. I love the datagrid.

David
Title: Re: textbox size   
Name: Li Chen
Date: 2004-12-22 12:35:33 PM
Comment:
Here is one of the ways to adjust the text box width:
1. Write an event handler for ItemCreated event of the Datagrid control. 2. If the ItemType is EditItem, use e.Item.Cells(TheColumnYouWantToAdjust).FindControl(0) method to get a reference to the textbox control. You can then set the width.
Title: textbox size   
Name: pandaz
Date: 2004-12-22 3:59:04 AM
Comment:
its a very useful article, but how do i adjust the size of the textbox after pressing the edit button?
Title: Test   
Name: Mayur
Date: 2004-12-21 6:56:59 AM
Comment:
Realy Nice thankx
Title: Very useful. But if I need PK with 2 columns ?   
Name: jkancio
Date: 2004-09-21 1:01:18 PM
Comment:
Congratulation ! very interesting control and tutorial.
You said: "In order to support a multiple-column key, we have to build our own key storage and retrieval mechanism...". Do you have some example showing how to do it ???
Title: DataGridHelper   
Name: Prem Kannan
Date: 2004-08-21 3:13:11 AM
Comment:
Hello,

I've gone through your DataGrid Control Helper. That was nice.

I need one help from you. How can I update the currently edited record automatically. I don't want to show UPDATE button. As and when he leaves the last column of the current record, system should automatically update the record.
Title: Title   
Name: Test
Date: 2004-08-17 11:52:30 AM
Comment:
Thank you for this. It is very helpful.
Title: Nice one   
Name: Pathik
Date: 2004-08-17 5:00:03 AM
Comment:
Really nice one!
Title: thank you!   
Name: John Braga
Date: 2004-08-17 3:58:49 AM
Comment:
Thank you for an interesting set of articles. I shall certainly be trying it out.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-06-24 8:13:09 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search