Deleting Data in a SQL Database
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>
See Also
Accessing Data with ASP.NET | Accessing Data with ADO.NET | System.Web.UI.WebControls Namespace | DataGrid Class