How to Confirm Delete Box in DataGrid Delete Button

Scenarion I


To use confirm Message box before delete operation uses JavaScript for confirmation box. You have to add JavaScript on button.


Page_Load

if(!IsPostBack) btnConfirm.Attributes.Add("onclick","return confirm('Do you want to Delete');");


Once clicked Ok button on Confirm Box it executes Button_Click server event else doesn’t do anything.


btnConfirm_Click

//Your server code to delete



Scenario II


The DataList and DataGrid controls easily allow to add a "Delete" button/hyperlink in your template. When clicked, this button/link raises a DeleteCommand event that you can handle to delete the data item.

DataGrid’s Template Delete Button raises DeleteCommand event after clicking Delete Button of DataGrid. We needs to write javascript confirm method to this template button. This should be write in DataGrid’s ItemCreated event because it fires when datagrid’s item has been created.

private void dg_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{

//Check for Item and Alternate Items if(e.Item.ItemType==ListItemType.AlternatingItem || e.Item.ItemType==ListItemType.Item)
{
Button btn=(Button)e.Item.Cells[0].Controls[0];
btn.Attributes.Add("onclick","return confirm(‘Do you want to continue?’);");
}

}

ASP.NET Data Grid Control using Stored Procedure

In this demo, I will be using VS.NET (Visual Studio.NET) and SQL Server as my Database.

First we need to add the following controls on the form.

1. Drag and Drop A Data Grid Control and a Label Control on to the form.
2. Then we can Auto Format our Data Grid.
3. The final design should look like in Figure



1. Next we need to connect to our database using a SQLData Adapter.
2. The wizard starts as shown in Figure
3. Click Next and Proceed.



? Next choose your Database Connection



We next need to decide whether we need SQL statements or use existing stored procedures.

Since we are calling stored procedures, we should choose option 3.



Choose the stored procedure you want to use. For this Demo , I have stuck to the Ten Most Expensive Products Stored Procedure.



In our Final screen click finish.



In our next section we need to generate our Dataset, in order to bind our DataGrid.



In our next screen we see how to name our DataSet.



Next we Bind our DataSet with our DataGrid as shown in the next screen



In the next screen , we should see the final look , before, we start coding our WebApp.



In the last part of this tutorial is the code. Type this in the Page Load Event.

SqlDataAdapter1.Fill(DsStoredProc1)
Me.DataBind()
DataGrid1.DataBind()

[Please note: If you are trying to access a remote server, please provide both username and password, for the application to work smooth. And for those of you who are still not sure, whether your System.Data and System.Data.SqlClient namespaces are not loaded, please check before doing any of the above]

How XML Data to Datagrid

The following code extracts data from the xml (EmpData) and displays in a Datagrid. The xml data resides in a separate directory. The Mappath method is used to map xml data correctly to the application. The DataBind method of the DataGrid (dgEmployee) is used to display the data from the dataset dsEmp.

The following shows the xml content in xmldata/Empdata.xml. Here, xmldata is the directory within the virtual path and Empdata.xml is the xml data file.

E00001
STEVE
ROBERTS
HUMAN RESOURCE


E00002
JOHN
GRAY
PROJECTS
The following code will display the xml data into the datagrid:


DataSet dsEmp = new DataSet();
dsEmp.ReadXml(MapPath("xmldata/Empdata.xml"));
dgEmployee.DataSource=dsEmp;
dgEmployee.DataBind();

Dynamically Show or Hide datagrid columns

We might come across situation where we want to dynamically show few columns of datagrid and to hide few columns based on some conditions

One way to acheive this is to is to create them at design time, and then to hide or show them as needed. we can do this by setting datagrid column's Visible property.

The following code shows how to toggle the visibility of the second column of the DataGrid.

DataGrid1.Columns(1).Visible = Not (DataGrid1.Columns(1).Visible)

How to Exporting DataGrid to Excel in c#

Exporting DataGrid to Excel might not need any excel object model in asp.net application with datagrid. Below is the complete code to export the Datagrid value to Excel in C#.


Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";

System.IO.StringWriter swtext = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hwtext = new HtmlTextWriter(swtext);

DGexcel.RenderControl(hwtext);
Response.Write(swtext.ToString());
Response.End();

Response.AddHeader is to know that we are exporting to a file which is named FileName.xls.

Response.ContentType is the type of the file being exported.

DGexcel.RenderControl(hwtext) writes the data to the HtmlTextWriter.

Response.Write(swtext.ToString()) sends the request to the response stream.

How to Create a DropDownList in DataGrid

How to place a control in DataGrid

1. Place DataGrid
2. rightclick select "Propery Builder"
3. select "Columns" from lift panel
4. select "Temlate Column" in "Available Columns"
5. click on ">" (so that "Template Column" Appear in "Selected Column")
6. change the "Header Text", "Footer Text" etc.. as your wish
7. Click "OK"
8. rightClick on the grid and select "Edit Template" ---> "Columns[0]" (you have to select the column where you want to place the control)
9. place the control in one of the section
a. Item Template or
b. EditItem Template etc.. according to your requirement

Note: in our sample i place a dropdowlist in Item Template

Placeing a control in a DataGrid is very simple. If you place a Check box or Radio button and handling this control is very easy even DropDownList also. but when we face the dificulty? if you place a control (like check box, raido button, or DropDownList) then want to hanlding the control's event it give some difficulty. how to overcome this?

How to Handle the Event of that Control


To handle the event of that control, you have to write a procedure which going to fire when the event is fire. in our sample the procedure is "ddlGroup_SelectedIndexChanged"

In our sample code i have table called "DDLSample" and i have some datas, the script to generate the table and insert the data is


create table DDLSample (GroupName varchar(15), Items varchar(15))

insert into DDLSample values ('Fruit', 'Mango')
insert into DDLSample values ('Fruit', 'Orange')
insert into DDLSample values ('Fruit', 'Grape')

insert into DDLSample values ('Color', 'Red')
insert into DDLSample values ('Color', 'Green')
insert into DDLSample values ('Color', 'Blue')
insert into DDLSample values ('Color', 'Yellow')

insert into DDLSample values ('Animal', 'Dog')
insert into DDLSample values ('Animal', 'Cat')
insert into DDLSample values ('Animal', 'Lion')


open a vb.net or C#.Net ASP.Net web Application

place a Grid and AutoGenerateColumns to false

place two DropDownList in two columns and set the column Heading as "Group" and "Item" repectively

change dropdownlist's ID to "ddlGroup" and "ddlItem" respectively

in webform1's HTML editor in ddlGroup's propertiy add the following line
OnSelectedIndexChanged="ddlGroup_SelectedIndexChanged"

so that the ddlGroup's code should be like



AutoPostBack="True">


VB.Net


Imports System.Data.SqlClient



Declare the vriables as


Dim sqlConnection As sqlConnection
Dim sqlCommand As sqlCommand
Dim dsGroup As DataSet = New DataSet
Dim dsItem As DataSet = New DataSet
Dim daGroup As SqlDataAdapter
Dim daItem As SqlDataAdapter = New SqlDataAdapter



in page load



If Not IsPostBack Then
sqlConnection = New SqlConnection("initial catalog=[DBName];user id=sa;password=;server=(local)")
sqlConnection.Open()
sqlCommand = New SqlCommand("select distinct GroupName from ddlsample", sqlConnection)
sqlCommand.CommandType = CommandType.Text
daGroup = New SqlDataAdapter(sqlCommand)
daGroup.Fill(dsGroup, "dtGroup")
DataGrid1.DataSource = dsGroup
DataGrid1.DataBind()
End If



In DataGrid1_ItemDataBound


Dim dr As DataRowView = CType(e.Item.DataItem, DataRowView)
If ((e.Item.ItemType = ListItemType.Item) _
OrElse (e.Item.ItemType = ListItemType.AlternatingItem)) Then
Dim listGroup As DropDownList = CType(e.Item.FindControl("ddlGroup"), DropDownList)
listGroup.DataSource = dsGroup
listGroup.DataValueField = "GroupName"
listGroup.DataTextField = "GroupName"
listGroup.DataBind()
listGroup.Items.Insert(0, New ListItem("Select One", "Select One"))
End If


Our Own Procedure to fire the event


Protected Sub ddlGroup_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim ddllist As DropDownList = CType(sender, DropDownList)
Dim cell As TableCell = CType(ddllist.Parent, TableCell)
Dim item As DataGridItem = CType(cell.Parent, DataGridItem)
Dim content As String = item.Cells(0).Text
Dim ddlType As DropDownList = CType(item.Cells(0).FindControl("ddlGroup"), DropDownList)
sqlConnection = New SqlConnection("initial catalog=SathyaSample;user id=sa;password=sa;server=(local)")
sqlConnection.Open()
sqlCommand = New SqlCommand(("select Items from ddlsample where GroupName = '" _
+ (ddlType.SelectedItem.Text + "'")), sqlConnection)
sqlCommand.CommandType = CommandType.Text
daItem = New SqlDataAdapter(sqlCommand)
daItem.Fill(dsItem, "dtItem")
Dim ddlItem As DropDownList = CType(item.Cells(1).FindControl("ddlItem"), DropDownList)
If (ddlType.SelectedItem.Text = "Select One") Then
ddlItem.Items.Clear()
Else
ddlItem.Items.Clear()
ddlItem.DataSource = dsItem
ddlItem.DataValueField = "Items"
ddlItem.DataTextField = "Items"
ddlItem.DataBind()
ddlItem.Items.Insert(0, New ListItem("Select One", "Select One"))
End If
End Sub

C#.Net


using System.Data.SqlClient;
Declare the vriables as
SqlConnection sqlConnection;
SqlCommand sqlCommand;
DataSet dsGroup=new DataSet();
DataSet dsItem=new DataSet();
SqlDataAdapter daGroup;
SqlDataAdapter daItem=new SqlDataAdapter();

in page load

if(!IsPostBack)
{
sqlConnection = new SqlConnection(@"initial catalog=SathyaSample;user id=sa;password=sa;server=(local)");
sqlConnection.Open();
sqlCommand = new SqlCommand("select distinct GroupName from ddlsample",sqlConnection);
sqlCommand.CommandType=CommandType.Text;
daGroup=new SqlDataAdapter(sqlCommand);
daGroup.Fill(dsGroup,"dtGroup");
DataGrid1.DataSource=dsGroup;
DataGrid1.DataBind();
}

In DataGrid1_ItemDataBound


DataRowView dr = (DataRowView) e.Item.DataItem;
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType==ListItemType.AlternatingItem)
{
DropDownList listGroup = (DropDownList)e.Item.FindControl("ddlGroup");
listGroup.DataSource = dsGroup;
listGroup.DataValueField = "GroupName";
listGroup.DataTextField = "GroupName";
listGroup.DataBind();
listGroup.Items.Insert(0,new ListItem("Select One","Select One"));
}



Our Own Procedure to fire the event


protected void ddlGroup_SelectedIndexChanged(object sender, EventArgs e)
{
DropDownList ddllist = (DropDownList)sender;
TableCell cell = ddllist.Parent as TableCell;
DataGridItem item = cell.Parent as DataGridItem;
string content = item.Cells[0].Text;
DropDownList ddlType=(DropDownList)item.Cells[0].FindControl("ddlGroup");
sqlConnection = new SqlConnection(@"initial catalog=SathyaSample;user id=sa;password=sa;server=(local)");
sqlConnection.Open();
sqlCommand = new SqlCommand("select Items from ddlsample where GroupName = '" + ddlType.SelectedItem.Text +"'",sqlConnection);
sqlCommand.CommandType=CommandType.Text;
daItem=new SqlDataAdapter(sqlCommand);
daItem.Fill(dsItem,"dtItem");
DropDownList ddlItem=(DropDownList)item.Cells[1].FindControl("ddlItem");
if (ddlType.SelectedItem.Text=="Select One")
{
ddlItem.Items.Clear();
}
else
{
ddlItem.Items.Clear();
ddlItem.DataSource=dsItem;
ddlItem.DataValueField="Items";
ddlItem.DataTextField="Items";
ddlItem.DataBind();
ddlItem.Items.Insert(0,new ListItem("Select One","Select One"));
}
}

How to Create a Scrollable DataGrid

Scrollable DataGrid

You can add a vertical scrollbar to a DataGrid by placing the DataGrid within a div tag with a fixed height and Overflow property. Overflow property is to manage the content of the object when the content exceeds the height or width of the object. So when the rows exceed your specified height a scrollbar will appear. The overflow attribute should be set either to auto or scroll. Auto displays a scrollbar only if the content is clipped (that is, the DataGrid extends beyond the specified height). With scroll, the scrollbar is displayed regardless of whether the DataGrid's height exceeds the specified height. For more details on Overflow property,

http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/properties/overflow.asp

PagerStyle-Visible="False" PageSize="10" ItemStyle-CssClass="tableItem" HeaderStyle-CssClass="tableHeader" HeaderStyle-BackColor="#aaaadd" BorderColor="black" AutoGenerateColumns="False" DataKeyField="CustomerID" ShowHeader=True >




<%# Container.ItemIndex+1 %>


DataField="CompanyName" HeaderText="Company Name" >

DataField="ContactName" HeaderText="Contact Name" >

DataField="Address" HeaderText="Address" >

Now we know how to create a scrollable DataGrid in a web page. However, when scrolling through the DataGrid, the header columns would not be visible, since they will also scroll along with other rows. Ideally, when displaying a scrollable DataGrid we want to have the header columns fixed, and only the body of the DataGrid scrolling.

Scrollable DataGrid with Fixed Header

Scrollable DataGrid with fixed header can be created in the same way like Scrollable DataGrid. This can be accomplished by having DataGrid within Div tag whose overflow attribute is set to auto or scroll. However to have fixed headers, what we can do is hide the Scrollable DataGrid’s headers. This can be done by setting “ShowHeader” property of DataGrid to false. Then create an HTML table above the Scrollable DataGrid’s Div tab which displays the column. In the HTML table, we will have only one row which will be the header for Scrollable DataGrid. Code for Scrollable DataGrid with fixed header is,

Row Number

Company Name

Contact Name

Address

Visible="False" PageSize="10" ItemStyle-CssClass="tableItem" HeaderStyle-
CssClass="tableHeader" HeaderStyle-BackColor = "#aaaadd" BorderColor="black"
AutoGenerateColumns="False" DataKeyField="CustomerID" ShowHeader="False">





<%# Container.ItemIndex+1 %>

Unfortunately, if you will try the above code, header doesn't line up perfectly with the DataGrid. This is because the displayed scrollbar pushes over the content of the DataGrid just a bit. If you remove the scrollbar from the DataGrid, then everything is perfect. This type of Scrollable DataGrid wont look good in a web page.

Only way to overcome this problem is by playing around the style properties of DataGrid. Mainly we need to change the width of the DataGrid to include the Scroll bar size. For example in this case, we need to increase the DataGrid width by 13 pixels which is the size of the Scroll bar. Then everything will be fine. Code for Scrollable DataGrid now is,

Row Number

Company Name

Contact Name

Address

Visible="False" PageSize="10" ItemStyle-CssClass="tableItem" HeaderStyle-
CssClass="tableHeader" HeaderStyle-BackColor="#aaaadd" BorderColor="black"
AutoGenerateColumns="False" DataKeyField="CustomerID" ShowHeader="False">





<%# Container.ItemIndex+1 %>

Overview of DataGrid

1) What is Datagrid?
1) Datagrid is a used to display formatted data from database
2) Data Grid is the standard control for viewing data in .Net environment. A data grid control is represented in .Net by System.Windows.Forms.DataGrid class. The data grid control can display the data from a number of data sources e.g. data table, dataset, data view and array.
3) Datagrid having types of columns that will help you to display formatted data and also you can able to edit & update that data within datagrid

2) Datagrid Columns
1) Bound Column - Bound Column used to bound database value directly to that column
2) Button Column - There are three types of buttons in datagrid
1) Select Button - used to select particular row of datagrid - Datagrid_SelectCommand () is associated with this functionality
2) Edit/Update/Cancel Button - used to edit/update/cancel record within Datagrid - No need to use external coding for edit and update record. - Datagrid_EditCommand function associated with this functionality
3) Delete Button - Used to delete particular row/record from datagrid - Datagrid_DeleteCommand function associated with this functionality.

IMP NOTE: If you want to use any external button or image in datagrid and you want to perform one of the above datagrid button functionality than just set the command property of that button to particular command e.g. for Edit Records - use edit , for Delete Records - use Delete etc.

3) Template Column - This column is very useful when you want to use another server control within Datagrid

Template Column Creation Steps:

1) Right click on datagrid and click on Property builder option it will open new small property window.
2) Now select Column tab than Add new Template Column and give header text to it.
3) close that window and again right click on datagrid and select Edit Template Columns option
4) Select your added column you will find Template Column with ITEM TEMPLATE heading
5) Drag & Drop one Server Control in ITEM TEMPLATE e.g. DropDownList
6) Now your Template column is ready to use.

Coding for Template Column:

1) Go to Datagrid Properties and click on Datagrid_ItemDatabound () Event.
2) Why this ItemDatabound () function? Let me Explain
Explanation: After Successfully Bind each row of datagrid this function will fired every time so its called ItemDataBound not ItemDataBinding.
3) Now we are going to bind our DropDownList in this function. Please refer following code for that
Code Language: C#

private void Datagrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
// We need to find DropDownList from our ItemTemplate Column and so there is no need to check
// header, footer and Pager Columns so this loop done that job

if ( e.Item.ItemType == ListItemType.AlternatingItem
|| e.Item.ItemType == ListItemType.Item
|| e.Item.ItemType == ListItemType.SelectedItem )
{
DropDownList drp = (DropDownList)e.Item.FindControl("DropDownList1"); //Type Casting
// Another way to write above code
DropDownList drp = e.Item.FindControl("DropDownList1") as DropDownList; //Type Casting

// Now Bind or fill that DropDownList
Dataset ds = new Dataset();
// your select query
// Fill Dataset using Adapter
// Suppose you bind Employee Table that having a Employeeid(PK) and EmployeeName Columns

DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "EmployeeName"; // to be displayed in dropdownlist
DropDownList1.DataValueField = "Employeeid" ;//Hidden Value
DropDownList1.DataBind();

// This code successfully bound dropdownlist control and using this code you are able to use any server control in Datagrid. You can also able to create Nested Datagrid means Datagrid inside Datagrid using same functionality.

}
}



4) Hyperlink Column
Hyperlink column very easy to use. You just need to set properties like NavigateURL, Querystring etc. so I think there is no need to explain this column functionality in details

3) Datagrid Events List:
1) Datagrid_ItemDatabound() – Explain above in detail
2) Datagrid_ItemCommand() – If you are using any external image in button column and give that button command property like remove than whenever that button click you need to check in this function and do whatever you want.
e.g.

private Datagrid_ItemCommand()
{
if(e.CommandName == “remove”)
{
// Do your code
}
}
Generally we are using this functionality to develop ADD TO CART button in E-Commerce application.
3) Datagrid_EditCommand() – already explained above
4) Datagrid_DeleteCommand() – already explained above
5) Datagrid_SelectCommand() – already explained above
6) Datagrid_PageIndexChanged() – Use to create Paging functionality in datagrid
7) Datagrid_SortCommand() – Use to create Sort functionality in datagrid. For this you need to assign SortExpression property of every columns.
8) Datagrid_UpdateCommand() – Use to create inbuilt functionality of Update records from datagrid.

How to Add checkox in datagrid

Code below shows a datagrid which contains a checkbox column and a checkbox column is added to the datagrid within the TemplateColumn tag and precisely in the ItemTemplate tag of the datagrid. It is added as a normal checkbox server control.

<form id="DG_Controls_Example" method="post" runat="server">
<asp:DataGrid id="DGSample" runat="server" AllowPaging="True" AutoGenerateColumns="False">
<Columns>
<asp:BoundColumn DataField="Contactname" HeaderText="Customer Name">
<asp:BoundColumn DataField="CompanyName" HeaderText="Company Name">
<asp:BoundColumn DataField="ContactTitle" HeaderText="Title">
<asp:TemplateColumn>
<ItemTemplate>
<asp:CheckBox ID="chkbox" Runat="server">




<asp:Button id="Button1" runat="server" Text="Button">


In this example we have added a button to read all the records which have been selected by checking the respective boxes in each record and display the checked records.

In the code behind, we bind the data to the datagrid as shown below:


private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
adp = new SqlDataAdapter("Select * from customers",con);
ds= new DataSet();
adp.Fill(ds);
DGSample.DataSource=ds;
DGSample.DataBind();
}
}

And in the button click event for the button, we write the code for processing the records having checked boxes.

In a loop for each item present in the datagrid, we find the check box control which we have added in the ItemTemplate tag and assign to an instance of a checkbox variable. For each of this checkbox we check whether the box is checked and if so we perform the required action on the checked records.


private void Button1_Click(object sender, System.EventArgs e)
{
CheckBox chkbox = new CheckBox();
TextBox txtbox= new TextBox();
foreach(DataGridItem item in DGSample.Items )
{
chkbox=(CheckBox)item.FindControl("chkbox");
txtbox=(TextBox)item.FindControl("txtbox");
if(chkbox.Checked)
{
Response.Write(item.Cells[0].Text);
// write the required code for processing the checked
// records depending on the requirement.
}

}
}