NGWS SDK Documentation  

This is preliminary documentation and subject to change.
To comment on this topic, please send us email at ngwssdk@microsoft.com. Thanks!

Deleting Data in a SQL Database

In this example, when the page is presented, there is an additional column at the left of the table that has a link for each row. Clicking this link deletes that row from the database.

To delete rows from a database

  1. Import the necessary namespaces.
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SQL" %>

    Within the <script language="C#" runat="server"> tag, this example implements three functions: Page_Load, MyDataGrid_Delete, and BindGrid. These functions are described in more detail in the steps that follow.

    <script language="C#" runat="server">
    SQLConnection myConnection;
  2. Implement a Page_Load function that sets up the connection information for a connection to the "pubs" database. It also checks to see if this page is a PostBack, and, if not, calls a custom BindGrid function. For a description of BindGrid, see step 2 of Inserting Data Into a SQL Database.
  3. Implement the MyDataGrid_Delete function. This function sets up a SQL Delete statement, connects to the database, deletes the indicated row, and then rebinds the DataGrid to display the updated database.
    public void MyDataGrid_Delete(Object sender, 
                                                DataGridCommandEventArgs E)
        {
    1. Set up the SQLCommand information.
              String deleteCmd = "DELETE from Authors where au_id = @Id";
              SQLCommand myCommand = new SQLCommand(deleteCmd, myConnection);
              myCommand.Parameters.Add(new SQLParameter("@Id", 
                                                   SQLDataType.VarChar, 11));
    2. Initialize the SQLCommand "@ID" parameter to the row ID that was clicked.
              myCommand.Parameters["@Id"].Value = 
                                  MyDataGrid.DataKeys[(int)E.Item.ItemIndex];
    3. Connect to the database and delete the specified row.
              myCommand.ActiveConnection.Open();
    4. Test to see if the delete was accomplished and present the appropriate message to the user.
              try 
              {
                  int rowsAffected = 0;
                  myCommand.Execute(ref rowsAffected);
                  Message.InnerHtml = "<b>Record Deleted</b><br>" + 
                                                           deleteCmd;
              }
              catch (SQLException) 
              {
                  Message.InnerHtml = "ERROR: Could not delete record";
                  Message.Style["color"] = "red";
              }
    5. Close the connection.
              myCommand.ActiveConnection.Close();
    6. Rebind the DataGrid to show the updated information.
              BindGrid();
          }
  4. Implement a BindGrid function. This function connects to the database and implements the standard "select *" SQL query to get all of the data in the "Authors" table of the database. For the details on BindGrid, see step 4 of Inserting Data Into a SQL Database.
  5. In the <body> of the page, display the data.
    <body style="font: 10pt verdana">
      <form runat="server">
        <h3><font face="Verdana">Deleting a Row of Data</font></h3>
        <span id="Message" MaintainState="false" style="font: arial 11pt;" runat="server"/><p>
        <ASP:DataGrid id="MyDataGrid" runat="server"
          Width="800"
          BackColor="#ccccff" 
          BorderColor="black"
          ShowFooter="false" 
          CellPadding=3 
          CellSpacing="0"
          Font-Name="Verdana"
          Font-Size="8pt"
          HeaderStyle-BackColor="#aaaadd"
          DataKeyField="au_id"
          OnDeleteCommand="MyDataGrid_Delete"
        >
          <property name="Columns">
             <asp:ButtonColumn Text="Delete Author" Command="Delete"/>
          </property>
        </ASP:DataGrid>
      </form>
    </body>

To see this example run, go to the ASP+ Quick Start and run sample DataGrid10.aspx.