1 out of 2 rated this helpful - Rate this topic
Deleting Data in a SQL Database

.NET Framework 1.1
The following code example presents a page with a column on the left side of the DataGrid that contains a link titled Delete Author in each row. Clicking on the link deletes that row of data from the database.
To see a similar example executed, run the DataGrid10.aspx sample in the ASP.NET QuickStart.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
   Dim myConnection As SqlConnection
   Sub Page_Load(Src As Object, E As EventArgs) 
      ' Create a connection to the "pubs" SQL database located on 
      ' the local computer.
      myConnection = New SqlConnection ("server=localhost;" _
         & "database=pubs;Trusted_Connection=Yes")
      ' Determine  whether the page is a postback. If it is not a
      ' postback, call BindGrid.
      if Not IsPostBack Then 
         BindGrid()
      End If
   End Sub
   ' When the Delete Author link is clicked, set up a SQL DELETE
   ' statement, connect to the database, delete the indicated row, and 
   ' rebind the DataGrid to display the updated database.
   Sub MyDataGrid_Delete(sender As Object, E As DataGridCommandEventArgs)
      Dim deleteCmd As String = "DELETE FROM Authors WHERE au_id = @Id;"
      Dim myCommand As SqlCommand = New SqlCommand(deleteCmd, _
         myConnection)
      myCommand.Parameters.Add(New SqlParameter("@Id", _
         SqlDbType.VarChar, 11))
      ' Initialize the SqlCommand "@Id" parameter to the ID of the row
      ' that was clicked.
      myCommand.Parameters("@Id").Value = _
         MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
      ' Connect to the database and delete the specified row.
      myCommand.Connection.Open()
      ' Test whether the delete was accomplished, and display the 
      ' appropriate message to the user.
      Try 
         myCommand.ExecuteNonQuery()
         Message.InnerHtml = "<b>Record Deleted</b><br>"
      Catch ex As SqlException
         Message.InnerHtml = "ERROR: Could not delete record"
         Message.Style("color") = "red"
      End Try

      ' Close the connection.
      myCommand.Connection.Close()
      ' Rebind the DataGrid to show the updated information.
      BindGrid()
   End Sub
   ' The BindGrid procedure connects to the database and implements
   ' a SQL SELECT query to get all the data in "Authors" table.
   public Sub BindGrid() 
      Dim  myCommand As SqlDataAdapter = New SqlDataAdapter("SELECT *" _
        & " FROM authors", myConnection)
      Dim ds As DataSet = New DataSet()
      myCommand.Fill(ds)
      MyDataGrid.DataSource=ds
      MyDataGrid.DataBind()
   End Sub
</script>

<%-- Display the data in a DataGrid control. --%>
<body style="font: 10pt verdana">
   <form runat="server">
      <h3><font face="Verdana">Deleting a Row of Data</font></h3>
      <span id="Message" EnableViewState="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"
      >
         <Columns>
            <ASP:ButtonColumn Text="Delete Author"
               CommandName="Delete"/>
         </Columns>
      </ASP:DataGrid>
   </form>
</body>
</html>

[C#]
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
   SqlConnection myConnection;
   protected void Page_Load(Object Src, EventArgs E) 
   {
      // Create a connection to the "pubs" SQL database located on the 
      // local computer.
      myConnection = new SqlConnection(   
         "server=localhost;database=pubs;Trusted_Connection=Yes");
      // Determine whether this page is a postback.  If it is not a
      // postback, call BindGrid.
      if (!IsPostBack) BindGrid();
   }
   // When the Delete link is clicked, create a SQL DELETE statement, 
   // connect to the database, delete the indicated row, and rebind the 
   // DataGrid to display the updated database.
   public void MyDataGrid_Delete(Object sender, 
      DataGridCommandEventArgs E) 
   {
      String deleteCmd = "DELETE FROM Authors WHERE au_id = @Id";
      SqlCommand myCommand = new SqlCommand(deleteCmd, myConnection);
      myCommand.Parameters.Add(new SqlParameter("@Id", 
         SqlDbType.VarChar, 11));
      // Initialize the SqlCommand "@Id" parameter to the ID of the row
      // that was clicked.
      myCommand.Parameters["@Id"].Value = 
         MyDataGrid.DataKeys[(int)E.Item.ItemIndex];
      // Connect to the database and delete the specified row.
      myCommand.Connection.Open();
      // Test whether the delete was accomplished, and display the 
      // appropriate message to the user.
      try 
      {
         myCommand.ExecuteNonQuery();
         Message.InnerHtml = "<b>Record Deleted</b><br>";
      }
      catch (SqlException) 
      {
         Message.InnerHtml = "ERROR: Could not delete record";
         Message.Style["color"] = "red";
      }
      // Close the connection.
      myCommand.Connection.Close();
      // Rebind the DataGrid to show the updated information.
      BindGrid();
   }
   // The BindGrid procedure connects to the database and implements
   // a SQL SELECT query to get all the data in the "Authors" database.
   public void BindGrid() 
   {
      SqlConnection myConnection = new SqlConnection(
         "server=localhost;database=pubs;Trusted_Connection=Yes");
      SqlDataAdapter myCommand = new SqlDataAdapter("SELECT *" +
         " FROM authors", myConnection);
      DataSet ds = new DataSet();
      myCommand.Fill(ds);
      MyDataGrid.DataSource=ds;
      MyDataGrid.DataBind();
   }
</script>

<%-- Display the data in a DataGrid control. --%>
<body style="font: 10pt verdana">
   <form runat="server">
      <h3><font face="Verdana">Deleting a Row of Data</font></h3>
      <span id="Message" EnableViewState="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"
      >
         <Columns>
            <ASP:ButtonColumn Text="Delete Author"
               CommandName="Delete"/>
         </Columns>
      </ASP:DataGrid>
   </form>
</body>
</html>
Deleting Data in a SQL Database

See Also
