ASP.NET 101 Home » Code Samples » Delete from Database Using a DataGrid

Delete from Database Using a DataGrid

01. Nov, 2007 0 Comments by admin

This sample shows how to add a ‘Delete’ button to a datagrid in order to delete table records and uses a replica of the same table in our Insert and Update/Edit samples (SQL Server Managed Provider). It simply contains an autonumbering ID field, a Firstname field and a Lastname field.

To change this for MS Access useage, merely change the SQL references to OleDb references. And, as always, to use this on your own system, copy the code below to your own, new page, and just change the database references to match your system.

Insert Sample | Update/Edit with DataGrid | Update/Edit with Form

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<!-- Created: 11/9/2002 12:55:10 PM -->
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.1">
	<title>Deleting from DataBase using a DataGrid</title>
<script language="VB" runat="server">
Sub MyDataGrid_DeleteCommand(s As Object, e As DataGridCommandEventArgs )
	Dim strConn as String = "server=YourServer;uid=YourUID;pwd=YourPWD;database=YourDB"
	Dim DeleteCmd As String = "DELETE from safetable Where id = @Id"
	Dim MyConn as New SQLConnection(strConn)
	Dim Cmd as New SqlCommand(DeleteCmd, MyConn)
	Cmd.Parameters.Add(New SqlParameter("@ID", MyDataGrid.DataKeys(CInt(e.Item.ItemIndex))))
	MyConn.Open()
	Cmd.ExecuteNonQuery()
	MyConn.Close()
	BindData
End Sub

Sub Page_Change(sender As Object, e As DataGridPageChangedEventArgs)
	MyDataGrid.CurrentPageIndex = e.NewPageIndex
	BindData
End Sub

Sub Page_Load(Source As Object, E As EventArgs)
	If Not Page.IsPostBack Then
		BindData()
	End If
End Sub

Sub BindData()
	Dim strConn as String = "server=YourServer;uid=YourUID;pwd=YourPWD;database=YourDB"
	Dim MySQL as string = "Select * from safetable Order by id desc"
	Dim MyConn as New SQLConnection(strConn)
	Dim ds as DataSet=New DataSet()
	Dim Cmd as New SQLDataAdapter(MySQL,MyConn)
	Cmd.Fill(ds,"safetable")
	MyDataGrid.Datasource=ds.Tables("safetable").DefaultView
	MyDataGrid.DataBind()
End Sub
</script>
</head>
<body>
<form runat="server" method="post">
<asp:Datagrid runat="server"
	Id="MyDataGrid"
	GridLines="Both"
	cellpadding="0"
	cellspacing="0"
	Headerstyle-BackColor="#8080C0"
	Headerstyle-Font-Name="Arial"
	Headerstyle-Font-Bold="True"
	Headerstyle-Font-Size="14"
	BackColor="#8080FF"
	Font-Name="Arial"
	Font-Size="11"
	AlternatingItemStyle-BackColor="#C0C0C0"
	AlternatingItemStyle-Font-Name="Arial"
	AlternatingItemStyle-Font-Size="11"
	BorderColor="Black"
	AllowPaging = "True"
	PageSize = "8"
	PagerStyle-Mode = "NumericPages"
	PagerStyle-HorizontalAlign="Center"
	PagerStyle-PageButtonCount = "10"
	OnPageIndexChanged = "Page_Change"
	AutogenerateColumns="False"
	OnDeleteCommand="MyDataGrid_DeleteCommand"
	DataKeyField="id"
	Width="50%">
	<Columns>
		<asp:ButtonColumn Text="Delete" HeaderText="Delete" CommandName="Delete"></asp:ButtonColumn>
		<asp:BoundColumn DataField="fname" HeaderText="fname"></asp:BoundColumn>
		<asp:BoundColumn DataField="lname" HeaderText="lname"></asp:BoundColumn>
	</Columns>
</asp:DataGrid>
</form>

For MS Access, just change the two data related subs to:
Sub MyDataGrid_DeleteCommand(s As Object, e As DataGridCommandEventArgs )
	 Dim strConn as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\pathAndNameToYourDB.mdb") & ";"
	 Dim DeleteCmd As String = "DELETE from safetable Where id = @Id"
	 Dim MyConn as New OleDbConnection(strConn)
	 Dim Cmd as New OleDbCommand(DeleteCmd, MyConn)
	 Cmd.Parameters.Add(New OleDbParameter("@ID", MyDataGrid.DataKeys(CInt(e.Item.ItemIndex))))
	 MyConn.Open()
	 Cmd.ExecuteNonQuery()
	 MyConn.Close()
	 BindData
End Sub

Sub BindData()
	Dim strConn as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\pathAndNameToYourDB.mdb") & ";"
	Dim MySQL as string = "Select * from safetable Order by id desc"
	Dim MyConn as New OleDbConnection(strConn)
	Dim ds as DataSet=New DataSet()
	Dim Cmd as New OleDbDataAdapter(MySQL,MyConn)
	Cmd.Fill(ds,"safetable")
	MyDataGrid.Datasource=ds.Tables("safetable").DefaultView
	MyDataGrid.DataBind()
End Sub