To read the article online, visit

Accessing and Updating Data in ASP.NET: Deleting Data

By Scott Mitchell


ASP.NET 2.0's data source and data Web controls make working with data much easier than in previous versions by allowing for a declarative approach to working with data. In short, the data source controls make it possible to retrieve, insert, update, and delete data without having to write the tedious dedata access code that was so commonplace in ASP.NET version 1.x. (A previous installment of this article series examined using the SqlDataSource to insert data into a database.)

In this article we will focus on using the SqlDataSource control to delete records from a database. Specifically, we will look at three examples: using a SqlDataSource and a bit of server-side code to delete a record selected from a drop-down list; and using the GridView Web control's built-in deleting support; and how to programmatically cancel a delete operation. Read on to learn more!

An Overview of Deleting Data Using the Data Source Controls

The AccessDataSource, SqlDataSource, and ObjectDataSource controls all support inserting, updating, and deleting capabilities. In a nutshell, all three have a Delete method that, when invoked, kicks off the following deleting workflow:
  1. The data source's Deleting event is fired
  2. The deleting "action" occurs
  3. The data source's Deleting event is fired
The deleting "action" differs between the data source controls. For the AccessDataSource and SqlDataSource, the action involves connecting to the specified database and executing the DELETE statement specified by the control's DeleteCommand property. For the ObjectDataSource, the deleting "action" involves creating an instance of the data source's underlying object and invoking the specified DeleteMethod. This article focuses on the SqlDataSource control; for a detailed look at deleting with the ObjectDataSource, refer to the "Editing, Inserting, and Deleting" tutorials in my Working with Data in ASP.NET 2.0 tutorial series.

Let's explore the deleting "action" for the AccessDataSource and SqlDataSource controls in more detail. The DELETE statement or stored procedure specified by the control's DeleteCommand uses a parameterized query. That is, if you use an ad-hoc SQL statement for the DeleteCommand, that DELETE statement will use parameters like so:

WHERE ColumnName1 = @Parameter1

Most ad-hoc DELETE statements look like the one above: they specify a table name and have a single condition in their WHERE clause. The WHERE clause specifies a particular primary key value to delete. In the case where a table has a composite primary key (i.e., a primary key made up of multiple columns), then the WHERE clause would compare each key column, like so:

WHERE ColumnName1 = @Parameter1 AND ColumnName2 = @Parameter2 AND ... AND ColumnNameN = @ParameterN

In the Filtering Database Data with Parameters article, we looked at using parameters in the SelectCommand to filter the results, such as SELECT * FROM Products WHERE Price < @UnitPrice. The parameter - @UnitPrice, in this instance - has its value specified via the data source control's SelectParameters, which can specify a source for the parameter value. The source may be: a hard-coded value like "3.95", which would return all products less than $3.95; the value of a Web control on the page, allowing the user to enter a price bound in a TextBox; from the querystring; from session state; and so on.

Likewise, the parameter values in the DELETE statement are assigned based on the parameters in the data source control's DeleteParameters collection, and these parameters can use the same sources as the SelectParameters.

The AccessDataSource and SqlDataSource controls, behind the scenes, use the standard ADO.NET classes to perform their data access. That is, they connect to the database using a SqlConnection or OleDbConnection object and specify the command text and parameters via a SqlCommand or OleDbCommand object.

Given this information, the deleting workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:

  1. The data source's Deleting event is fired
  2. SqlConnection and SqlCommand (or OleDbConnection and OleDbCommand) objects are created
  3. The command object's CommandText property is assigned the data source control's DeleteCommand property
  4. The parameters in the data source control's DeleteParameters collection are added the command object's Parameters collection
  5. A connection to the database is established and the command is executed, thereby deleting the data
  6. The data source's Deleted event is fired
The remainder of this article examines three deleting scenarios using the SqlDataSource control: manually deleting data by programmatically calling the SqlDataSource control's Delete method; deleting data using the GridView control; and programmatically short-circuiting the deletion process in the SqlDataSource's Deleting event handler. The full code for these demos is available in the download at the end of this article.

Manually Deleting Data

The demos available for download at the end of this article illustrate different techniques for deleting data into the Products table of the Northwind database. Imagine that we were asked to create a page that allowed users to choose a product from a drop-down list and then delete the selected product by clicking a Delete button. We could create such a Web Form by adding a DropDownList and a Button Web control.

In addition to these two user input controls we could also add a SqlDataSource control to populate the DropDownList control with the set of products and to execute the actual delete. This could be done using two SqlDataSource controls - one to do the select and one for the delete - or just one control that does both. For this demo, I used just one control.

Previous articles in this series have examined using SqlDataSource control's to retrieve data and bind it to a Web control, so I won't step through the tasks for binding the list of products to the DropDownList. Once you have configured the SqlDataSource to retrieve the records from the Products table and have bound them to the DropDownList, you can configure the SqlDataSource's delete-related properties by selecting the SqlDataSource control in the Designer and, from the Properties window, selecting the DeleteQuery property. Doing so will bring up a dialog box where you can enter the SqlDataSource's DeleteCommand property and specify values for the DeleteParameters.

Since we want to delete a particular record from the Products table, set the DeleteCommand property to the following DELETE statement:

DELETE FROM [Products] WHERE [ProductID] = @ProductID

Note the use of the parameter in the WHERE clause. The source for this parameter value will be the selected ProductID value in the DropDownList control on the page. Map this parameter value to the DropDownList's SelectedValue property value via the DeleteParameters collection using ControlParameter. The easiest way to accomplish this is from the dialog box brought up by clicking on the DeleteQuery property for the SqlDataSource control (see the screen shot below).

Alternatively, these parameters can be specified via the SqlDataSource control's declarative syntax:

<asp:SqlDataSource ID="ProductsDataSourceForDropDownList" runat="server" ConnectionString="..."
   DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"
   SelectCommand="SELECT [ProductID], [ProductName] FROM [Products] ORDER BY [ProductName]">
      <asp:ControlParameter ControlID="ProductList" Name="ProductID" PropertyName="SelectedValue"
         Type="Int32" />

Note that the above declarative markup contains both the delete-specific property settings (DeleteCommand and DeleteParameters) as well as the SelectCommand property.

Once the Web controls have been added to the page and the SqlDataSource's properties have been correctly configured, deleting a new record is as simple as calling the data source control's Delete() method. In addition to deleting the product, we also need to rebind the data to the DropDownList (so that it is updated to no longer include the just-deleted product). Also, it would be nice to display some sort of confirmation message to the user upon deletion. The following "Delete Product" Button's Click event handler performs these three tasks:

Protected Sub btnDeleteProduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDeleteProduct.Click
   'Programmatically call the Delete method


   'Display a confirmation message
   Dim productJustDeleted As String = ProductList.SelectedItem.Text
   ClientScript.RegisterStartupScript(Me.GetType(), "Message", String.Format("alert('The product {0} has just been deleted...');", productJustDeleted.Replace("'", "\'")), True)

   'Rebind the data to the DropDownList so that the just-deleted product no longer appears
End Sub

Deleting Data Using a GridView Control

A number of new data Web controls were introduced with ASP.NET 2.0. These include the GridView, DetailsView, and FormView, among others. The GridView is designed to display a set of records in a grid. It also boasts built-in delete functionality that can be turned on with the tick of a checkbox. To demonstrate this functionality, let's look at using a GridView to list all of the products in a pageable GridView and then add a column of Delete buttons to the grid.

Start by adding a SqlDataSource control to the page and use the same DeleteCommand as in the previous example:

DELETE FROM [Products] WHERE [ProductID] = @ProductID

Next, add the @ProductID parameter to the control's DeleteParameters collection. Rather than using ControlParameter as we did in the previous example, use the default Parameter object.

<asp:SqlDataSource ID="ProductsDataSource" runat="server" ConnectionString="..."
   DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"
   SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice], [Discontinued] FROM [Products] ORDER BY [ProductName]">

<asp:Parameter Name="ProductID" Type="Int32" />


Note: The SelectCommand used in the above SqlDataSource differs slightly from the previous example in that it returns two additional fields from the Products table: UnitPrice and Discontinued.

Next, add a GridView to the page and, from its smart tag, bind it to the SqlDataSource control we just added (ProductsDataSource). Doing so automatically adds BoundFields for the columns returned in the SelectCommand. It is also sets the GridView's DataKeys property to the primary key column(s) of the data being returned - ">ProductID", in this case.

To add a column of Delete buttons, go to the GridView's smart tag and check the "Enable Deleting" checkbox. This adds a CommandField to the DetailsView and sets its ShowDeleteButton property to True. A CommandField with this configuration displays a column with a Delete button in each row. Also check the "Enable Paging" checkbox, which is also located in the GridView's smart tag.

After completing the above steps, your GridView's declarative markup should look similar to the following:

<asp:GridView ID="gvProducts" runat="server"


      AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="ProductsDataSource">

<asp:CommandField ShowDeleteButton="True" />

      <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
      <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />
      <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" SortExpression="Discontinued" />

That's all there is to it! When the page is visited and a Delete button clicked, the following workflow transpires:

  1. A postback
  2. The GridView's RowDeleting event fires
  3. The GridView populates the @ProductID using the DataKeys value for the row whose Delete button was clicked. (It is essential that the DataKeys property be set to "ProductID". If you clear this property value out or it gets reset or lost somehow, the @ProductID parameter value won't be assigned when the Delete button is clicked. Consequently, the DELETE statement won't delete any records! Therefore, if you are testing the GridView and find that clicking the Delete button doesn't do anything, the first thing to check is that the GridView's DataKeys property is properly set.)
  4. The GridView invokes its SqlDataSource control's Delete() method
  5. The GridView's RowDeleted event fires

This example illustrates only the simplest facilities of the GridView and omits commonly requested features, such as providing a messagebox confirming the delete. For information on adding a client-side confirmation messagebox to a GridView's Delete buttons, see Adding Client-Side Confirmation When Deleting (VB Version) (C# version).

Cancelling a Delete

As discussed earlier, when a data source control's Delete() method is invoked, it goes through the following sequence of steps:
  1. The data source's Deleting event is fired
  2. The deleting "action" occurs
  3. The data source's Deleting event is fired
Note that before the actual delete "action" occurs, the Deleting event is fired. In certain scenarios, we may wish to proactively cancel a delete after the data source control's Delete() method has been invoked. For example, we might include a GridView that includes a Delete button for each row, although there may be certain business rules that prohibit certain records from being deleted. This business logic could be enforced in one of two ways: by programmatically hiding or disabling the Delete buttons for those records that cannot be deleted; or by programmatically checking the business rules when the Delete button is clicked and cancelling the delete if necessary. Ideally, both techniques would be used.

Let's look at how to implement cancelling a delete from the SqlDataSource control's Deleting event. Building upon the previous example that used a GridView, let's update our page logic so that products that have a UnitPrice greater than $50.00 cannot be deleted. To accomplish this, we need to add create an event handler for the Deleting event and determine the price of the record that is about to be deleted. All that we have in this event handler are the parameter values - in this case, just the ProductID value. Consequently, we need to requery the database to determine this product's UnitPrice value. As the following code illustrates, to cancel a delete simply set the e.Cancel property to True.

Protected Sub ProductsDataSource_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles ProductsDataSource.Deleting
   'Determine if the product being deleted has a unit price > $50
   Dim productID As Integer = Convert.ToInt32(e.Command.Parameters("@ProductID").Value)

   'Determine the unit price of the product that the user wants to delete...
   'There are many ways you can accomplish this - use a SqlDataSource control, write code to query the database...
   'Let's use data access code
   Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
   Const strSql As String = "SELECT UnitPrice FROM Products WHERE ProductID = @ProductID"
   Dim myCommand As New SqlCommand(strSql, myConnection)
   myCommand.Parameters.AddWithValue("@ProductID", productID)

   Dim price As Object = myCommand.ExecuteScalar

   'Prohibit the delete if price is not a database NULL AND it is greater than $50
   If Not Convert.IsDBNull(price) AndAlso Convert.ToDecimal(price) > 50 Then
     e.Cancel = True   'Cancel the delete
     CannotDeleteMessage.Visible = True 'Show a message explaining why
   End If
End Sub

Note that this examples uses ADO.NET data access code to the database and execute a query to determine the UnitPrice of the product about to be deleted. This product's ProductID value is retrieved from the e.Command object's Parameters collection. (This parameter retrieval code is specific to the type of data source control being used; in other words, the code seen here would differ if using an ObjectDataSource control.) If the UnitPrice value is not a database NULL and exceeds $50.00, then the deletion is cancelled and a Label control (CannotDeleteMessage) is displayed, explaining to the user that they cannot delete the specified product due to its price.

The following screen shot shows what happens when attempting to delete "Carnarvon Tigers," a product whose price exceeds $50.00.

Carnarvon Tigers canont be deleted because its price exceeds $50.00.


In this article we looked at how to use the SqlDataSource to delete data from a database. We worked through three examples: manually deleting data by programmatically calling the data source control's Delete() method; deleting data using the built-in deleting capabilities of a GridView; and cancelling a delete via the SqlDataSource's Deleting event handler. In all three cases, the SqlDataSource control encapsulates many of the data access tasks, such as connecting to the database, creating the command object, and executing the parameterized query. In future installments of this article series we will take a similar in-depth look at updating.

Until then... Happy Programming!

  • By Scott Mitchell


  • Download the code used in this article

    Further Readings:

  • Tutorials on Editing, Inserting, and Deleting Data (using the ObjectDataSource)
  • Inserting, Updating, and Deleteing Data with the SqlDataSource (VB Version) (C# version)
  • Adding Client-Side Confirmation When Deleting (VB Version) (C# version)
  • Article Information
    Article Title: ASP.NET.Accessing and Updating Data in ASP.NET: Deleting Data
    Article Author: Scott Mitchell
    Published Date: June 20, 2007
    Article URL:

    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers