Deleting a DataGrid Data Based on a Column Value

Introduction:

In this article we'll see how to delete the records of a DataGrid based on a column value in the database table.

Scenario:

Let consider Table which has details about some products (for sample we'll consider the Northwind Database and the Products Table). The data is as below:

The last field is Discontinued which is 0/1. The administrator should be able to delete a record only if the field is 0 (in this case assuming that the Product is no longer used).

There are various ways this can be done. But the simplest way is to disable the Delete button in case the field Discontinued has the value as "0".

So lets see check out solution for this.

Solution:

Design View

<asp:datagrid id="DataGrid1" runat="server" AutoGenerateColumns="False" OnItemCommand="DeleteItem" BorderColor="Tan" BorderWidth="1px" BackColor="LightGoldenrodYellow" CellPadding="2" GridLines="None" ForeColor="Black" Font-Names="Verdana" Font-Size="Smaller">
<SelectedItemStyle ForeColor="GhostWhite" BackColor="DarkSlateBlue">
</SelectedItemStyle>
<AlternatingItemStyle BackColor="PaleGoldenrod"></AlternatingItemStyle>
<HeaderStyle Font-Bold="True" BackColor="Tan"></HeaderStyle>
<FooterStyle BackColor="Tan"></FooterStyle>
<PagerStyle HorizontalAlign="Center" ForeColor="DarkSlateBlue" BackColor="PaleGoldenrod"></PagerStyle><Columns >
<asp:TemplateColumn HeaderText ="Product Information" >
<ItemTemplate >
<asp:Button CommandName="Delete" Text ="Delete" Runat =server
Enabled =<%#CheckStatus(Convert.ToBoolean(DataBinder.Eval(Container.DataItem,"Discontinued")))%> ID="Button1" ></asp:Button>
<asp:Label ID="lblID" Runat=server text=<%#DataBinder.Eval(Container.DataItem , "ProductID")%>></asp:Label>
<asp:Label ID="lblName" Runat=server text=<%#DataBinder.Eval(Container.DataItem , "ProductName")%>></asp:Label>
<asp:CheckBox id="chk1" Runat =server checked=<%#DataBinder.Eval(Container.DataItem,"Discontinued")%>></asp:CheckBox>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
<asp:label id="Label1" style="Z-INDEX: 102; LEFT: 400px; POSITION: absolute; TOP: 15px" runat="server" BackColor="White" ForeColor="Black" Font-Bold="True" Font-Names="Verdana" Font-Size="X-Small" Width="182px" Height="15px">
</asp:label>

C# Version of Code

SqlConnection mycn;
SqlDataAdapter myda;
DataSet ds;
String strConn;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
if (!Page.IsPostBack )
{
BindData();
}
}
void BindData()
{
strConn="Data Source=localhost;uid=sa;pwd=;Initial Catalog=northwind";
mycn = new SqlConnection(strConn);
myda = new SqlDataAdapter ("Select * FROM products where productid <10", mycn);
ds= new DataSet ();
myda.Fill (ds,"Table");
DataGrid1.DataSource =ds;
DataGrid1.DataBind ();
}
//Function which takes a boolean value and returns boolean value
//This is done to Set the Enabled Property of the Delete button to True/False
protected bool CheckStatus(bool prdStatus)
{
//If the Discontinued field is "0"
if (prdStatus==false)
{
//Enable the Delete Button
return true;
}
//If the Discontinued field is "1"
else
{
//Disable the Delete Button
return false;
}
}
//In the ItemCreateEvent Check if the Delete Button is clicked
protected void DeleteItem(Object sender, DataGridCommandEventArgs e )
{
//Check if the CommandName ==Delete
if (e.CommandName == "Delete")
{
//Put Delete Code
//Sql Statement
//string strsql = "Delete from Products where productid=" + ((Label)e.Item.FindControl("label1")).Text ;
//
Label1.Text ="You have Selected ProductID " + ((Label)e.Item.FindControl("lblID")).Text + " to be deleted";
}
}

VB.NET version of code

Dim myconnection As SqlConnection
Dim myda As SqlDataAdapter
Dim ds As DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
BindData()
End If
End Sub
Sub BindData()
myconnection = New SqlConnection
"Server=localhost;uid=sa;password=;database=northwind;")
myda = New SqlDataAdapter("Select * from Products", myconnection)
ds = New DataSet
myda.Fill(ds, "AllTables")
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub
Protected Function CheckStatus(ByVal prdStatus As Boolean) As Boolean
If prdStatus = False Then
Return True
Else
Return False
End If
End Function
Protected Sub DeleteItem(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
If e.CommandName = "Delete" Then
Dim strsql As String = "delete from Products where productid=" + CType(e.Item.FindControl("lblID"), Label).Text
Label1.Text = "You have Selected ProductID " + CType(e.Item.FindControl("lblID"), Label).Text + " to be deleted"
End If
End Sub

Output of webform shown below