ADO.Net Tutorial: Grid View Tutorial Series II

GridView insert update delete in asp.net - Part 23

In this video we will discuss about performing an insert, update and delete on asp.net gridview control using sqldatasource. We will be using tblEmployee table for this demo. SQL script to create and populate this table with sample data is available in Part 13 of asp.net gridview tutorial.

Create an asp.net web application. Drag and drop a gridview and a sqldatasource control on WebForm1.aspx.

Configure "SqlDataSource1" control
1. Right click on "SqlDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. On "Configure the Select Statement" screen, select "tblEmployee" table from dropdownlist.
6. Click on "Advanced" button
7. Select "Generate INSERT, UPDATE and DELETE statements" checkbox and click OK
8. Click Next and Finish

Drag and drop gridview control on WebForm1.aspx. Now let us associate "SqlDataSource1" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag"
2. Select "SqlDataSource1" from "Choose Data Source" dropdownlist
3. Select "Enable Deleting" and "Enable Editing" checkboxes. At this point "Delete" and "Edit" buttons should appear on the gridview control.

We will be using gridview control's footer for inserting a new record. Set "ShowFooter" property of the GridView1 control to "true". This can be done from the properties window, or in the HTML.

By default GridView control has generated bound fields to display EmployeeId, Name, Gender and City columns.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1"
ShowFooter="true">
  <Columns>
  <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
  <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
InsertVisible="False" ReadOnly="True"
SortExpression="EmployeeId" />
  <asp:BoundField DataField="Name" HeaderText="Name"
SortExpression="Name" />
  <asp:BoundField DataField="Gender" HeaderText="Gender"
SortExpression="Gender" />
  <asp:BoundField DataField="City" HeaderText="City"
SortExpression="City" />
  </Columns>
</asp:GridView>

We need to convert these bound fields into template fields. This can be done very easily using the designer.
1. On the "GridView Tasks" pane click on "Edit Columns" link button.
2. Select "EmployeeId" from "Selected Fields" section and click on "Convert this field into a template field" link
3. Do the same for Name, Gender and City
Convert boundfield to templatefield in gridview

Convert boundfield to templatefield in gridview

Now modify the template fields in the HTML, as shown below. Please note
1. In every TemplateField, along with EditItemTemplate and ItemTemplate, we also need FooterTemplate.
2. A dropdownlist is used in EditItemTemplate and FooterTemplate of "Gender" template field, instead of a textbox control.
3. To validate data during edit and insert operations, notice that, we are using validation controls, in EditItemTemplate and FooterTemplate.
4. A linkbutton is used in the footer template of "EmployeeId" template field, to enable the user to insert a new employee row
5. We have used ValidationGroup="Insert" for all the validation controls in FooterTemplates. LinkButton's ValidationGroup is aslo set to "Insert", so that all the validation controls in in FooterTemplates are fired only on Insert LinkButton click.
6. We have set LinkButton's OnClick="lbInsert_Click".
7. After the closing tag of GridView, notice that we are using 2 validationsummary controls, to display all the validation messages in one place. ValidationSummary1 control's ValidationGroup is set to "Insert". ValidationSummary1 control displays all insert related validation errors, and edit related validation errors are displayed using ValidationSummary2 control.

Here is the complete HTML
<div style="font-family: Arial">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1"
ShowFooter="True" BackColor="#DEBA84" BorderColor="#DEBA84"
BorderStyle="None" BorderWidth="1px" CellPadding="3"
CellSpacing="2">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:TemplateField HeaderText="EmployeeId" InsertVisible="False"
SortExpression="EmployeeId">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server"
Text='<%# Eval("EmployeeId") %>'>
</asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server"
Text='<%# Bind("EmployeeId") %>'>
</asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="lbInsert" ValidationGroup="Insert"
runat="server" OnClick="lbInsert_Click">Insert
</asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name" SortExpression="Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server"
Text='<%# Bind("Name") %>'>
</asp:TextBox>
<asp:RequiredFieldValidator ID="rfvEditName" runat="server"
ErrorMessage="Name is a required field"
ControlToValidate="TextBox1" Text="*" ForeColor="Red">
</asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server"
Text='<%# Bind("Name") %>'>
</asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvInsertName" runat="server"
ErrorMessage="Name is a required field"
ControlToValidate="txtName" Text="*" ForeColor="Red"
ValidationGroup="Insert">
</asp:RequiredFieldValidator>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender" SortExpression="Gender">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server"
SelectedValue='<%# Bind("Gender") %>'>
<asp:ListItem>Select Gender</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="rfvEditGender" runat="server"
ErrorMessage="Gender is a required field" Text="*"
ControlToValidate="DropDownList1" ForeColor="Red"
InitialValue="Select Gender">
</asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server"
Text='<%# Bind("Gender") %>'>
</asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlInsertGender" runat="server">
<asp:ListItem>Select Gender</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="rfvInsertGender" runat="server"
ErrorMessage="Gender is a required field" Text="*"
ControlToValidate="ddlInsertGender" ForeColor="Red"
InitialValue="Select Gender" ValidationGroup="Insert">
</asp:RequiredFieldValidator>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City" SortExpression="City">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server"
Text='<%# Bind("City") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvEditCity" runat="server"
ErrorMessage="City is a required field" Text="*"
ControlToValidate="TextBox3" ForeColor="Red">
</asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server"
Text='<%# Bind("City") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvInsertCity" runat="server"
ErrorMessage="City is a required field" Text="*"
ControlToValidate="txtCity" ForeColor="Red"
ValidationGroup="Insert">
</asp:RequiredFieldValidator>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:ValidationSummary ID="ValidationSummary1" ValidationGroup="Insert"
ForeColor="Red" runat="server" />
<asp:ValidationSummary ID="ValidationSummary2" ForeColor="Red"
runat="server" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>"
DeleteCommand="DELETE FROM [tblEmployee] WHERE [EmployeeId] = @EmployeeId"
InsertCommand="INSERT INTO [tblEmployee] ([Name], [Gender], [City])
VALUES (@Name, @Gender, @City)"
SelectCommand="SELECT * FROM [tblEmployee]"
UpdateCommand="UPDATE [tblEmployee] SET [Name] = @Name, [Gender] = @Gender,
[City] = @City WHERE [EmployeeId] = @EmployeeId">
<DeleteParameters>
<asp:Parameter Name="EmployeeId" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="City" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="EmployeeId" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
</div>

Finally copy and paste the following event handler method in WebForm1.aspx.cs
protected void lbInsert_Click(object sender, EventArgs e)
{
SqlDataSource1.InsertParameters["Name"].DefaultValue =
((TextBox)GridView1.FooterRow.FindControl("txtName")).Text;
SqlDataSource1.InsertParameters["Gender"].DefaultValue =
((DropDownList)GridView1.FooterRow.FindControl("ddlInsertGender")).SelectedValue;
SqlDataSource1.InsertParameters["City"].DefaultValue =
((TextBox)GridView1.FooterRow.FindControl("txtCity")).Text;
SqlDataSource1.Insert();
}

GridView insert update delete in asp.net using objectdatasource - Part 24

In this video we will discuss about performing an insert, update and delete on asp.net gridview control using objectdatasource. Please watch Part 23 of asp.net gridview tutorial, before proceeding. In Part 23, we discussed about doing exactly the same thing using sqldatasource control. So we will be modifying the same example to use objectdatasource control instead of sqldatasource control.

Add a class file with name EmployeeDataAccessLayer.cs to your project. Copy and paste the following code.
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Demo
{
public class Employee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public string City { get; set; }
}

public class EmployeeDataAccessLayer
{
// Select Method for ObjectDataSource control
public static List<Employee> GetAllEmployees()
{
List<Employee> listEmployees = new List<Employee>();

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
employee.Name = rdr["Name"].ToString();
employee.Gender = rdr["Gender"].ToString();
employee.City = rdr["City"].ToString();

listEmployees.Add(employee);
}
}

return listEmployees;
}

// Delete Method for ObjectDataSource control
public static void DeleteEmployee(int EmployeeId)
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand
("Delete from tblEmployee where EmployeeId = @EmployeeId", con);
SqlParameter param = new SqlParameter("@EmployeeId", EmployeeId);
cmd.Parameters.Add(param);
con.Open();
cmd.ExecuteNonQuery();
}
}

// Update Method for ObjectDataSource control
public static int UpdateEmployee(int EmployeeId, string Name, string Gender, string City)
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
string updateQuery = "Update tblEmployee SET Name = @Name, " +
"Gender = @Gender, City = @City WHERE EmployeeId = @EmployeeId";
SqlCommand cmd = new SqlCommand(updateQuery, con);
SqlParameter paramOriginalEmployeeId = new
SqlParameter("@EmployeeId", EmployeeId);
cmd.Parameters.Add(paramOriginalEmployeeId);
SqlParameter paramName = new SqlParameter("@Name", Name);
cmd.Parameters.Add(paramName);
SqlParameter paramGender = new SqlParameter("@Gender", Gender);
cmd.Parameters.Add(paramGender);
SqlParameter paramCity = new SqlParameter("@City", City);
cmd.Parameters.Add(paramCity);
con.Open();
return cmd.ExecuteNonQuery();
}
}

// Insert Method for ObjectDataSource control
public static int InsertEmployee(string Name, string Gender, string City)
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
string updateQuery = "Insert into tblEmployee (Name, Gender, City)" +
" values (@Name, @Gender, @City)";
SqlCommand cmd = new SqlCommand(updateQuery, con);
SqlParameter paramName = new SqlParameter("@Name", Name);
cmd.Parameters.Add(paramName);
SqlParameter paramGender = new SqlParameter("@Gender", Gender);
cmd.Parameters.Add(paramGender);
SqlParameter paramCity = new SqlParameter("@City", City);
cmd.Parameters.Add(paramCity);
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
}

1. Compile your project.
2. On WebForm1.aspx delete "SqlDataSource1" control.
3. Drag and drop ObjectDataSource control onto the webform.
4. Now configure ObjectDataSource control to use
GetAllEmployees() as "Select" Method
DeleteEmployee(int EmployeeId) as "Delete" method
UpdateEmployee(int EmployeeId, string Name, string Gender, string City) as "Update" method
InsertEmployee(string Name, string Gender, string City) as "Insert" method

The HTML of ObjectDataSource1 control, should be as shown below
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
DeleteMethod="DeleteEmployee" InsertMethod="InsertEmployee"
SelectMethod="GetAllEmployees" TypeName="Demo.EmployeeDataAccessLayer"
UpdateMethod="UpdateEmployee">
<DeleteParameters>
<asp:Parameter Name="EmployeeId" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="City" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="EmployeeId" Type="Int32" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="City" Type="String" />
</UpdateParameters>
</asp:ObjectDataSource>

Now change DataSourceID property of GridView1 control from "SqlDataSource1" to "ObjectDataSource1" and Save. You will get a message asking, if you want to "Refresh Fields and Keys for GridView1". Click No.

Finally in code-behind file change the implementation of "lbInsert_Click" as shown below.
protected void lbInsert_Click(object sender, EventArgs e)
{
ObjectDataSource1.InsertParameters["Name"].DefaultValue =
((TextBox)GridView1.FooterRow.FindControl("txtName")).Text;
ObjectDataSource1.InsertParameters["Gender"].DefaultValue =
((DropDownList)GridView1.FooterRow.FindControl("ddlInsertGender")).SelectedValue;
ObjectDataSource1.InsertParameters["City"].DefaultValue =
((TextBox)GridView1.FooterRow.FindControl("txtCity")).Text;
ObjectDataSource1.Insert();
}

GridView insert update delete without using datasource controls - Part 25

Suggested Videos
Part 22 - Using validation controls with asp.net gridview when editing data
Part 23 - GridView insert update delete in asp.net
Part 24 - GridView insert update delete in asp.net using objectdatasource

We discussed about inserting, updating and deleting data from gridview, using sqldatasource control in Part 23. In Part 24, we discussed about achievieng the same, using ObjectDataSource control. It is also possible to perform insert, update and delete on gridview, without using datasource controls at all, and that's what we will discuss in this video. Please watch Part 23 and Part 24, before proceeding with this video. We will be modifying the example, that we used in Part 24.

1. As we don't want to use datasource controls. Please delete "ObjectDataSource1" control from the webform.

2. Delete DataSourceID="ObjectDataSource1" from GridView1. This should remove the dependency of GridVIew1 on ObjectDataSource1 control.

3. From the code behind file, delete lbInsert_Click() event handler method.

4. In the "FooterTemplate" of "EmployeeId" TemplateField, please delete OnClick="lbInsert_Click", as we no longer have this event handler method.

5. Delete "CommandField" column from GridView1
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

6. Now, include a template field in the place of CommandField. This template field is used to display Edit, Update, Cancel and Delete link buttons. We don't want delete and cancel buttons to cause validation, so set CausesValidaion property of these buttons to false.
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lbEdit" CommandArgument='<%# Eval("EmployeeId") %>' CommandName="EditRow" ForeColor="#8C4510" runat="server">Edit</asp:LinkButton>
<asp:LinkButton ID="lbDelete" CommandArgument='<%# Eval("EmployeeId") %>' CommandName="DeleteRow" ForeColor="#8C4510" runat="server" CausesValidation="false">Delete</asp:LinkButton>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="lbUpdate" CommandArgument='<%# Eval("EmployeeId") %>' CommandName="UpdateRow" ForeColor="#8C4510" runat="server">Update</asp:LinkButton>
<asp:LinkButton ID="lbCancel" CommandArgument='<%# Eval("EmployeeId") %>' CommandName="CancelUpdate" ForeColor="#8C4510" runat="server" CausesValidation="false">Cancel</asp:LinkButton>
</EditItemTemplate>
</asp:TemplateField>

7. Copy and paste the following private method. This method binds employee data with gridview1 control.
private void BindGridViewData()
{
GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees();
GridView1.DataBind();
}

8. Call BindGridViewData() in Page_Load() event.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridViewData();
}
}

9. Finally generate GridView1_RowCommand() event handler method. Copy and Paste the following code.
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "EditRow")
{
int rowIndex = ((GridViewRow)((LinkButton)e.CommandSource).NamingContainer).RowIndex;
GridView1.EditIndex = rowIndex;
BindGridViewData();
}
else if (e.CommandName == "DeleteRow")
{
EmployeeDataAccessLayer.DeleteEmployee(Convert.ToInt32(e.CommandArgument));
BindGridViewData();
}
else if (e.CommandName == "CancelUpdate")
{
GridView1.EditIndex = -1;
BindGridViewData();
}
else if (e.CommandName == "UpdateRow")
{
int rowIndex = ((GridViewRow)((LinkButton)e.CommandSource).NamingContainer).RowIndex;

int employeeId = Convert.ToInt32(e.CommandArgument);
string name = ((TextBox)GridView1.Rows[rowIndex].FindControl("TextBox1")).Text;
string gender = ((DropDownList)GridView1.Rows[rowIndex].FindControl("DropDownList1")).SelectedValue;
string city = ((TextBox)GridView1.Rows[rowIndex].FindControl("TextBox3")).Text;

EmployeeDataAccessLayer.UpdateEmployee(employeeId, name, gender, city);

GridView1.EditIndex = -1;
BindGridViewData();
}
else if (e.CommandName == "InsertRow")
{
string name = ((TextBox)GridView1.FooterRow.FindControl("txtName")).Text;
string gender = ((DropDownList)GridView1.FooterRow.FindControl("ddlGender")).SelectedValue;
string city = ((TextBox)GridView1.FooterRow.FindControl("txtCity")).Text;

EmployeeDataAccessLayer.InsertEmployee(name, gender, city);

BindGridViewData();
}
}

10. In the FooterTemplate of EmployeeId TemplateField, set CommandName property lbInsert link button to "InsertRow"
<asp:LinkButton ID="lbInsert" CommandName="InsertRow" ForeColor="#8C4510" ValidationGroup="Insert" runat="server">Insert</asp:LinkButton>

11. If you want to show a confirmation dialog box, before a row is deleted, include javascript confirm() function, using "OnClientClick" attribute of LinkButton "lbDelete".
<asp:LinkButton ID="lbDelete" CommandArgument='<%# Eval("EmployeeId") %>' CommandName="DeleteRow" ForeColor="#8C4510" runat="server" CausesValidation="false" OnClientClick="return confirm('Are you sure you want to delete this row');">Delete</asp:LinkButton>

Displaying summary data in asp.net gridview footer - Part 26

Suggested Videos
Part 23 - GridView insert update delete in asp.net
Part 24 - GridView insert update delete in asp.net using objectdatasource
Part 25 - GridView insert update delete without using datasource controls

We will be using tblProduct table for this demo. Please use the SQL script below to create and populate this table.
Create table tblProduct
(
ProdcutId int identity primary key,
Name nvarchar(50),
UnitPrice int,
QuantitySold int
)

Insert into tblProduct values ('LCD TV', 250, 120)
Insert into tblProduct values ('iPhone', 410, 220)
Insert into tblProduct values ('iPad', 175, 320)
Insert into tblProduct values ('HP Laptop', 275, 346)
Insert into tblProduct values ('Dell Desktop', 212, 314)
Insert into tblProduct values ('Blade Server', 460, 211)

We want to display total UntiPrice and QuantitySold in the footer of gridview. To achieve this
1. Create tblProducts table using the sql script
2. Drag and drop a gridview and a sqldatasource control on the webform.
3. By default the footer of a gridview control is not shown. Set "ShowFooter" property of GridView1 control to true, so that the footer will become visible.
4. Configure sqldatasource control to retrieve data from tblProducts table.
5. Associate SqlDataSource1 control with GridView1 control using the DataSourceID property of gridview control.
6. Generate event handler method for rowdatabound event of GridView1 control.
7. Copy and paste the following code in the code behind file. The code is well commented and self explanatory.

// Declare variables to hold total unit price and quantity sold

int totalUnitPrice = 0;
int totalQuanitySold = 0;

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
// Loop thru each data row and compute total unit price and quantity sold
if (e.Row.RowType == DataControlRowType.DataRow)
{
totalUnitPrice += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "UnitPrice"));
totalQuanitySold += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "QuantitySold"));
}
// Display totals in the gridview footer
else if (e.Row.RowType == DataControlRowType.Footer)
{
e.Row.Cells[1].Text = "Grand Total";
e.Row.Cells[1].Font.Bold = true;

e.Row.Cells[2].Text = totalUnitPrice.ToString();
e.Row.Cells[2].Font.Bold = true;

e.Row.Cells[3].Text = totalQuanitySold.ToString();
e.Row.Cells[3].Font.Bold = true;
}
}

Run the application and notice that the totals are displayed in the footer of the gridview control as expected.

EmptyDataText and EmptyDataTemplate properties of asp.net gridview control - Part 27

Suggested Videos
Part 24 - GridView insert update delete in asp.net using objectdatasource
Part 25 - GridView insert update delete without using datasource controls
Part 26 - Displaying summary data in asp.net gridview footer

By default an asp.net gridview will not display anything on the webform, if there are no rows in the datasource to which the gridview is bound. For example, let us say we are binding a gridview control to tblProduct table. If there are no rows in this table, then the gridview does not render any thing on the webform. If there are no rows, I want the gridview to display a message stating - "There are no products to display".

1. Set EmptyDataText property of the gridview control as shown below
<asp:GridView ID="GridView1" EmptyDataText="There are no products to display" ...
</asp:GridView>

OR

2. Use EmptyDataTemplate as shown below
<EmptyDataTemplate>
There are no products to display
</EmptyDataTemplate>

EmptyDataRowStyle Property can be used to set the display styles of the empty data row.

If you set both, EmptyDataTemplate is used instead of EmptyDataText. The difference, between the 2 is that, if you want to have greater on what you want to display, then use EmptyDataTemplate.

For example, I want the gridview to render an HTML table with a header and a message as shown below.

To achieve this I would use the following EmptyDataTemplate
<EmptyDataTemplate>
<table cellspacing="2" cellpadding="3" rules="all" id="GridView1" style="background-color: #DEBA84;
border-color: #DEBA84; border-width: 1px; border-style: None;">
<tr style="color: White; background-color: #A55129; font-weight: bold;">
<td scope="col">
ProdcutId
</td>
<td scope="col">
Name
</td>
<td scope="col">
UnitPrice
</td>
<td scope="col">
QuantitySold
</td>
</tr>
<tr style="color: #8C4510; background-color: #FFF7E7;">
<td colspan="4">
There are no products to display
</td>
</tr>
</table>
</EmptyDataTemplate>

Delete multiple rows from asp.net gridview - Part 28

Suggested Videos
Part 25 - GridView insert update delete without using datasource controls
Part 26 - Displaying summary data in asp.net gridview footer
Part 27 - EmptyDataText and EmptyDataTemplate properties of asp.net gridview control

In this video we will discuss about deleting multiple rows from gridview control using checkbox.

We will be using tblEmployee table for this demo. For SQL script to create and populate this table, please refer to, Part 13 - Deleting data from gridview using sqldatasource control.

Step 1: Create an asp.net web application. Right click on the web application project and add a class file with name EmployeeDataAccessLayer.cs. Copy and paste the following code in EmployeeDataAccessLayer.cs file.
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;

namespace Demo
{
public class Employee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public string City { get; set; }
}

public class EmployeeDataAccessLayer
{
public static List<Employee> GetAllEmployees()
{
List<Employee> listEmployees = new List<Employee>();

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
employee.Name = rdr["Name"].ToString();
employee.Gender = rdr["Gender"].ToString();
employee.City = rdr["City"].ToString();

listEmployees.Add(employee);
}
}

return listEmployees;
}

public static void DeleteEmployees(List<string> EmployeeIds)
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
List<string> parameters = EmployeeIds.Select((s, i) => "@Parameter" + i.ToString() ).ToList();
string inClause = string.Join(",", parameters);
string deleteCommandText = "Delete from tblEmployee where EmployeeId IN (" + inClause + ")";
SqlCommand cmd = new SqlCommand(deleteCommandText, con);

for (int i = 0; i < parameters.Count; i++)
{
cmd.Parameters.AddWithValue(parameters[i], EmployeeIds[i]);
}

con.Open();
cmd.ExecuteNonQuery();
}
}
}
}

Step 2: Copy and paste the following HTML in WebForm1.aspx.
<div style="font-family:Arial">
<asp:Button ID="btnDelete" runat="server" Text="Delete"
onclick="btnDelete_Click" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None"
BorderWidth="1px" CellPadding="3" CellSpacing="2">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="cbDeleteHeader" runat="server"
AutoPostBack="True"
oncheckedchanged="cbDeleteHeader_CheckedChanged" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="cbDelete" runat="server"
AutoPostBack="true"
oncheckedchanged="cbDelete_CheckedChanged" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Id">
<ItemTemplate>
<asp:Label ID="lblEmployeeId" runat="server"
Text='<%# Bind("EmployeeId") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Gender" HeaderText="Gender" />
<asp:BoundField DataField="City" HeaderText="City" />
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:Label ID="lblMessage" runat="server" Font-Bold="true">
</asp:Label>
</div>

Step 3: Copy and paste the following code in WebForm1.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// Load data on initial get request
if (!IsPostBack)
{
GetData();
}
}

// Method to retrieve data and bind to gridview
public void GetData()
{
GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees();
GridView1.DataBind();
}

// Eventhandler method to select or deselect all rows depending
// on if the checkbox in header row is selected or deselected
protected void cbDeleteHeader_CheckedChanged(object sender, EventArgs e)
{
foreach (GridViewRow gridViewRow in GridView1.Rows)
{
((CheckBox)gridViewRow.FindControl("cbDelete")).Checked = ((CheckBox)sender).Checked;
}
}

// Select or deselect the checkbox in the header row, when the selection
// of checkboxes in gridview data rows change
protected void cbDelete_CheckedChanged(object sender, EventArgs e)
{
CheckBox headerCheckBox =
(CheckBox)GridView1.HeaderRow.FindControl("cbDeleteHeader");
if (headerCheckBox.Checked)
{
headerCheckBox.Checked = ((CheckBox)sender).Checked;
}
else
{
bool allCheckBoxesChecked = true;
foreach (GridViewRow gridViewRow in GridView1.Rows)
{
if (!((CheckBox)gridViewRow.FindControl("cbDelete")).Checked)
{
allCheckBoxesChecked = false;
break;
}
}
headerCheckBox.Checked = allCheckBoxesChecked;
}
}

// Method to delete selected employee rows in gridview
protected void btnDelete_Click(object sender, EventArgs e)
{
List<string> lstEmployeeIdsToDelete = new List<string>();
foreach (GridViewRow gridViewRow in GridView1.Rows)
{
if(((CheckBox)gridViewRow.FindControl("cbDelete")).Checked)
{
string employeeId =
((Label)gridViewRow.FindControl("lblEmployeeId")).Text;
lstEmployeeIdsToDelete.Add(employeeId);
}
}
if (lstEmployeeIdsToDelete.Count > 0)
{
EmployeeDataAccessLayer.DeleteEmployees(lstEmployeeIdsToDelete);
GetData();
lblMessage.ForeColor = System.Drawing.Color.Navy;
lblMessage.Text = lstEmployeeIdsToDelete.Count.ToString() +
" row(s) deleted";
}
else
{
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text = "No rows selected to delete";
}
}
}
}

Enhancing gridview using jquery when deleting mulitple rows - Part 29

Suggested Videos
Part 26 - Displaying summary data in asp.net gridview footer
Part 27 - EmptyDataText and EmptyDataTemplate properties of asp.net gridview control
Part 28 - Delete mulitple rows from asp.net gridview

Please watch Part 28 - Delete mulitple rows from asp.net gridview from gridview tutorial before proceeding with this video.

Let us use Javascript to toggle the selection of checkboxes in gridview, instead of using server side code. This improves performance and user experience.

When the user clicks the delete button, without selecting any rows, we should get a javascript alert box as shown below.
no rows selected in gridview to delete

When I click delete after selecting a few rows, we should get a javascript confirmation dialog box as shown below.
using jquery to delete from gridview

Delete "AutoPostBack" and "oncheckedchanged" attributes from "HeaderTemplate" and "ItemTemplate"
Enhancing gridview using jquery when deleting mulitple rows

Enhancing gridview using jquery when deleting mulitple rows

Delete the following 2 methods from code-behind file

protected void cbDelete_CheckedChanged(object sender, EventArgs e)
protected void cbDeleteHeader_CheckedChanged(object sender, EventArgs e)

Copy and paste the following javascript code in the HEAD section of WebForm1.aspx
<script type="text/javascript" language="javascript">
function toggleSelectionUsingHeaderCheckBox(source) {
$("#GridView1 input[name$='cbDelete']").each(function () {
$(this).attr('checked', source.checked);
});
}

function toggleSelectionOfHeaderCheckBox() {
if ($("#GridView1 input[name$='cbDelete']").length == $("#GridView1 input[name$='cbDelete']:checked").length) {
$("#GridView1 input[name$='cbDeleteHeader']").first().attr('checked', true);
}
else {
$("#GridView1 input[name$='cbDeleteHeader']").first().attr('checked', false);
}
}

$(document).ready(function () {
$("#btnDelete").click(function () {
var rowsSelected = $("#GridView1 input[name$='cbDelete']:checked").length;
if (rowsSelected == 0) {
alert('No rows selected');
return false;
}
else
return confirm(rowsSelected + ' row(s) will be deleted');
});
});
</script>

Finally associate toggleSelectionUsingHeaderCheckBox() method with onclick event of CheckBox - cbDeleteHeader and toggleSelectionOfHeaderCheckBox() method with onclick event of CheckBox - cbDelete.
Associate javascript functions with client side events of controls

Associate javascript functions with client side events of controls

Displaying images in asp.net gridview using templatefield - Part 30

Suggested Videos
Part 27 - EmptyDataText and EmptyDataTemplate properties of asp.net gridview control
Part 28 - Delete mulitple rows from asp.net gridview
Part 29 - Enhancing gridview using jquery when deleting mulitple rows

Displaying images in gridview using templatefield

Displaying images in gridview using templatefield

In this video we will discuss about displaying images in gridview control. There are 2 ways display images in gridview.
1. Using TemplateField
2. Using ImageField

In this video, we will discuss about using TemplateField and in our next vidoe we will discuss about using ImageField.

We will be using tblEmployee table for this demo. Please use the sql script below, to create and populate the table.

Create Table tblEmployee
(
EmployeeId int primary key identity,
Name nvarchar(50),
Gender nvarchar(20),
City nvarchar(50),
PhotoPath nvarchar(100)
)

Insert into tblEmployee values('Mark','Male','London','Images/Mark.png')
Insert into tblEmployee values('John','Male','Chennai','Images/John.png')
Insert into tblEmployee values('Mary','Female','New York','Images/Mary.png')
Insert into tblEmployee values('Mike','Male','Sydeny','Images/Mike.png')

Create an asp.net web application. Add a class file with name EmployeeDataAccessLayer.cs. Copy and paste the following code.
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;

namespace Demo
{
public class Employee
{
public int EmployeeId { get; set; }
  public string Name { get; set; }
  public string Gender { get; set; }
  public string City { get; set; }
public string Photo { get; set; }
}

public class EmployeeDataAccessLayer
{
public static List<Employee> GetAllEmployees()
{
List<Employee> listEmployees = new List<Employee>();

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
employee.Name = rdr["Name"].ToString();
employee.Gender = rdr["Gender"].ToString();
employee.City = rdr["City"].ToString();
employee.Photo = rdr["PhotoPath"].ToString();

listEmployees.Add(employee);
}
}

return listEmployees;
}
}
}

Drag and drop gridview control on WebForm1.aspx. Copy and paste the following code in WebForm1.aspx.cs.
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees();
GridView1.DataBind();
}

Add a folder with Name="Images" to your web application. Copy and paste any 4 images into "Images" folder. The names of the images must match with what we have in the database.
1. Mark.png
2. John.png
3. Mary.png
4. Mike.png

After you add images, the folder structure in solution explorer is as shown below.
Images folder in Solution Explorer

Run the application, and notice that the image path is displayed in the gridview instead of displaying images.

Add 4 BoundFields and 1 TemplateField to GridView control. The HTML is shown below.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
  <Columns>
  <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" />
  <asp:BoundField DataField="Name" HeaderText="Name" />
  <asp:BoundField DataField="Gender" HeaderText="Gender" />
  <asp:BoundField DataField="City" HeaderText="City" />
  <asp:TemplateField HeaderText="Photo">
  <ItemTemplate>
  <asp:Image ID="Image1" runat="server" Height="100px"
Width="100px" ImageUrl='<%# Bind("Photo") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Please note the following
1. The templatefield is used to display photos of employees.
2. In the ItemTemplate we are using Image control.
3. The "ImageUrl" property is set to "Photo" property of "Employee" object.
4. To control the size of the image in GridView control, set the Width and Height properties. We have set them to 100X100 pixels.
5. Finally set GridView1 control's AutoGenerateColumns property to "False"

Displaying images in asp.net gridview using imagefield - Part 31

Suggested Videos
Part 28 - Delete mulitple rows from asp.net gridview
Part 29 - Enhancing gridview using jquery when deleting mulitple rows
Part 30 - Displaying images in asp.net gridview using templatefield

In Part 30 of asp.net gridview tutorial we discussed about displaying images in gridview using templatefiled. Please watch Part 30, before proceeding.

ImageFiled is designed for the purpose of displaying images in databound controls like GridView and DetailsView. So, it is better to use ImageField over TemplateField.

We will be modifying the example we discussed in Part 30, to use ImageField instead of TemplateField. Notice that, in the gridview, TemplateField is replaced with ImageField.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
  <Columns>
  <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" />
  <asp:BoundField DataField="Name" HeaderText="Name" />
  <asp:BoundField DataField="Gender" HeaderText="Gender" />
  <asp:BoundField DataField="City" HeaderText="City" />
  <asp:ImageField HeaderText="Photo" DataImageUrlField="Photo"
ControlStyle-Height="100px" ControlStyle-Width="100px">
</asp:ImageField>
</Columns>
</asp:GridView>

Please note that
1. DataImageUrlField of "ImageField" is set to "Photo" property of the Employee object.
2. To control the width and height of the image in gridview, we are using ControlStyle-Height and ControlStyle-Width properties. Both of them are set to 100 pixels.
3. Use AlternateText property, to specify the text that you want to display, if the image is missing.
4. If you want to have different alternate text for each image displayed, use DataAlternateTextField property.
5. If the image path is null, and if you want to have a different image, then use NullImageUrl property.
6. If you want to display alternate text, when photo path is null, then use NullDisplayText property.

Displaying GridView in a GridView - Part 32

Suggested Videos
Part 29 - Enhancing gridview using jquery when deleting mulitple rows
Part 30 - Displaying images in asp.net gridview using templatefield
Part 31 - Displaying images in asp.net gridview using imagefield

In this video we will discuss about displaying GridView in another GridView. For this example we will be using tblDepartment and tblEmployee tables. Use the sql script below to create and populate these tables with sample data.

Create Table tblDepartment
(
DepartmentId int primary key identity,
Name nvarchar(50)
)

Insert into tblDepartment values('IT')
Insert into tblDepartment values('HR')
Insert into tblDepartment values('Payroll')
Insert into tblDepartment values('Administration')

Create Table tblEmployee
(
EmployeeId int Primary key identity,
Name nvarchar(50),
DeptId int Foreign key references tblDepartment(DepartmentId)
)

Insert into tblEmployee values('Mark',1)
Insert into tblEmployee values('John',1)
Insert into tblEmployee values('Mary',3)
Insert into tblEmployee values('Mike',4)
Insert into tblEmployee values('Pam',3)
Insert into tblEmployee values('David',1)
Insert into tblEmployee values('Tom',2)
Insert into tblEmployee values('Jason',4)
Insert into tblEmployee values('Vicky',3)
Insert into tblEmployee values('Todd',1)
Insert into tblEmployee values('Ben',2)

We want to display Employees gridview, with in Departments GridView as shown below.
Displaying GridView in a GridView

Step1 : Create EmployeeDataAccessLayer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
public class Employee
{
public int EmployeeId { get; set; }
public string EmployeeName { get; set; }
}

public class EmployeeDataAccessLayer
{
public static List<Employee> GetAllEmployees(int DepartmentId)
{
List<Employee> listEmployees = new List<Employee>();

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("Select * from tblEmployee where DeptId = @DepartmentId", con);
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@DepartmentId";
parameter.Value = DepartmentId;
cmd.Parameters.Add(parameter);

con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
employee.EmployeeName = rdr["Name"].ToString();

listEmployees.Add(employee);
}
}

return listEmployees;
}
}
}

Step2 : Create DepartmentDataAccessLayer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
public class Department
{
public int DepartmentId { get; set; }
public string DepartmentName { get; set; }
public List<Employee> Employees
{
get
{
return EmployeeDataAccessLayer.GetAllEmployees(this.DepartmentId);
}
}
}

public class DepartmentDataAccessLayer
{
public static List<Department> GetAllDepartmentsandEmployees()
{
List<Department> listDepartments = new List<Department>();

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("Select * from tblDepartment", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Department department = new Department();
department.DepartmentId = Convert.ToInt32(rdr["DepartmentId"]);
department.DepartmentName = rdr["Name"].ToString();

listDepartments.Add(department);
}
}

return listDepartments;
}
}
}

Step 3: Drag and drop a gridview control on webform1.aspx. Create 2 bound fields and 1 template field. Use another GridView control, in the ItemTemplate of the TemplateField.
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="DepartmentId"
HeaderText="Department Id" />
<asp:BoundField DataField="DepartmentName"
HeaderText="Department Name" />
<asp:TemplateField HeaderText="Employees">
<ItemTemplate>
<asp:GridView ID="GridView2" runat="server"
DataSource='<%# Bind("Employees") %>'>
</asp:GridView>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Step 4: Copy and paste the following code in WebForm1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource =
DepartmentDataAccessLayer.GetAllDepartmentsandEmployees();
GridView1.DataBind();
}

Merging cells in gridview footer row - Part 33

Suggested Videos
Part 30 - Displaying images in asp.net gridview using templatefield
Part 31 - Displaying images in asp.net gridview using imagefield
Part 32 - Displaying GridView in a GridView

In this video we will discuss about merging cells in the gridview footer row. Let us understand this with an example. We want to display total employee count in gridview footer row as shown below. Notice that, all the cells in the footer row are merged.

Merge gridview row cells

Step 1: Drag and drop a gridview and a sqldatasource control on webform1.aspx

Step 2: Configure sqldatasource control

Step 3: Associate sqldatasource control with gridview control

Step 4: Generate gridview, RowDataBound event handler method.

Step 5: Copy and paste the following code in code behind
// Variable to hold employee count
int employeeCount = 0;
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
// If the row is Data row
if (e.Row.RowType == DataControlRowType.DataRow)
{
// Increment employee count
employeeCount += 1;
}
// If the row is a footer row
else if (e.Row.RowType == DataControlRowType.Footer)
{
// Clear all the cells in the footer row
e.Row.Cells.Clear();
// Create a new table cell
TableCell tableCell = new TableCell();
// Set the ColumnSpan
tableCell.ColumnSpan = 4;
// Set the Text alignment
tableCell.HorizontalAlign = HorizontalAlign.Center;
// Set the text that you want to display in the footer
tableCell.Text = "Total Employees Count = " + employeeCount.ToString();
// Finally add the cell to the footer row
e.Row.Cells.Add(tableCell);
}
}

Drilldown and display hierarchical data in an asp.net gridview using sqldatasource control - Part 34

Suggested Videos
Part 31 - Displaying images in asp.net gridview using imagefield
Part 32 - Displaying GridView in a GridView
Part 33 - Merging cells in gridview footer row

In this video, we will discuss about drilling down and displaying hierarchical data in gridview controls. We will be using the following 3 database tables for this demo.
1. tblContinents
2. tblCountries
3. tblCities

1. When the page loads, a gridview should display the list of all continents available in tblContinents table
2. When a "Select" button is clicked against a continent, all the countries belonging to the selected continent should be displayed in another gridview.
3. Along the same lines, when a "Select" button is clicked against a country, all the cities belonging to the selected country should be displayed in another gridview.

Drilldown and display hierarchical data in an asp.net gridview

Use the following sql script to create and populate the required tables.

Create Table tblContinents
(
ContinentId int identity primary key,
ContinentName nvarchar(50)
)

Insert into tblContinents values ('Asia')
Insert into tblContinents values ('Europe')
Insert into tblContinents values ('South America')

Create Table tblCountries
(
CountryId int identity primary key,
CountryName nvarchar(50),
ContinentId int foreign key references dbo.tblContinents(ContinentId)
)

Insert into tblCountries values ('India', 1)
Insert into tblCountries values ('Japan', 1)
Insert into tblCountries values ('Malaysia', 1)

Insert into tblCountries values ('United Kingdom', 2)
Insert into tblCountries values ('France', 2)
Insert into tblCountries values ('Germany', 2)

Insert into tblCountries values ('Argentina', 3)
Insert into tblCountries values ('Brazil', 3)
Insert into tblCountries values ('Colombia', 3)

Create Table tblCities
(
CityId int identity primary key,
CityName nvarchar(50),
CountryId int foreign key references dbo.tblCountries(CountryId)
)

Insert into tblCities values ('Bangalore', 1)
Insert into tblCities values ('Chennai', 1)
Insert into tblCities values ('Mumbai', 1)

Insert into tblCities values ('Tokyo', 2)
Insert into tblCities values ('Hiroshima', 2)
Insert into tblCities values ('Saku', 2)

Insert into tblCities values ('Kuala Lumpur', 3)
Insert into tblCities values ('Ipoh', 3)
Insert into tblCities values ('Tawau', 3)

Insert into tblCities values ('London', 4)
Insert into tblCities values ('Manchester', 4)
Insert into tblCities values ('Birmingham', 4)

Insert into tblCities values ('Paris', 5)
Insert into tblCities values ('Cannes', 5)
Insert into tblCities values ('Nice', 5)

Insert into tblCities values ('Frankfurt', 6)
Insert into tblCities values ('Eutin', 6)
Insert into tblCities values ('Alsfeld', 6)

Insert into tblCities values ('Rosario', 7)
Insert into tblCities values ('Salta', 7)
Insert into tblCities values ('Corrientes', 7)

Insert into tblCities values ('Rio de Janeiro', 8)
Insert into tblCities values ('Salvador', 8)
Insert into tblCities values ('Brasília', 8)

Insert into tblCities values ('Cali', 9)
Insert into tblCities values ('Montería', 9)
Insert into tblCities values ('Bello', 9)

Drag and drop 3 gridview controls and 3 Sqldatasource controls on webform1.aspx.

Step 1: Configure SqlDataSource1 control to retrieve data from tblContinents table.

Step 2: Asscociate SqlDataSource1 control with Gridview1 control.

Step 3: From GridView1 - GridView Tasks pane, please make sure "Enable selection" checkbox is checked.

Step 4: Configure SqlDataSource2 control to retrieve data from tblCountries table. Configure the WHERE clause for the select statement, by clicking on "WHERE" button. ContinentId should come fromt the selected row in GridView1 control.
Configuring WHERE clause for sqldatasource control

Configuring WHERE clause for sqldatasource control

Step 5: Asscociate SqlDataSource2 control with Gridview2 control.

Step 6: From GridView2 - GridView Tasks pane, please make sure "Enable selection" checkbox is checked.

Step 7: Configure SqlDataSource3 control to retrieve data from tblCities table. Configure the WHERE clause for the select statement, by clicking on "WHERE" button. CountryId should come fromt the selected row in GridView2 control.

Step 8: Asscociate SqlDataSource3 control with Gridview3 control.

At this stage the HTML of your webform should be as shown below. Please note the following
1. Values for DataKeyNames and DataSourceID properties of all the 3 gridview controls.
2. SelectCommand and SelectParameters of SqlDataSource2 and SqlDataSource3 controls.
Configuring gridview and sqldatasource controls to support drilling down into hierarchical data