Hi Sabhya,
I have created a sample which full fill your requirement you need to modify the code according to your need.
The Database structure i have used is from below article
SQL
CREATE PROCEDURE GetCustomers
@CountryValue VARCHAR(50) = NULL
,@CityValue VARCHAR(50) = NULL
,@ContactNameValue VARCHAR(50) = null
,@PostalCodeValue VARCHAR(50) = null
AS
BEGIN
SET NOCOUNT ON;
SELECT ContactName
,City
,Country
,PostalCode
FROM Customers
WHERE (Country = @CountryValue OR @CountryValue IS NULL)
AND (City = @CityValue OR @CityValue IS NULL)
AND (ContactName = @ContactNameValue OR @ContactNameValue IS NULL)
AND (PostalCode = @PostalCodeValue OR @PostalCodeValue IS NULL)
END
GO
HTML
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
PageSize="5" OnPageIndexChanging="OnPaging">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblContactName" Text="ContactName" runat="server" />:
<asp:DropDownList ID="ddlContactName" CssClass="form-control" runat="server" OnSelectedIndexChanged="DropDownChange"
AutoPostBack="true">
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<asp:HiddenField ID="hfContactName" runat="server" Value='<%# Eval("ContactName") %>' />
<%# Eval("ContactName") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblCity" Text="City" runat="server" />:
<asp:DropDownList ID="ddlCity" runat="server" OnSelectedIndexChanged="DropDownChange"
AutoPostBack="true">
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<%# Eval("City") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblCountry" Text="Country" runat="server" />:
<asp:DropDownList ID="ddlCountry" runat="server" OnSelectedIndexChanged="DropDownChange"
AutoPostBack="true">
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<%# Eval("Country") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblPostalCode" Text="PostalCode" runat="server" />:
<asp:DropDownList ID="ddlPostalCode" runat="server" OnSelectedIndexChanged="DropDownChange"
AutoPostBack="true">
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<%# Eval("PostalCode")%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<table id="emptyTable" runat="server" visible="false">
<tr>
<td colspan="4">
No Records Found!
</td>
</tr>
</table>
</div>
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void BindDropDownList()
{
TableCell cells = GridView1.HeaderRow.Cells[0];
PopulateDropDown((cells.FindControl("ddlContactName") as DropDownList), (cells.FindControl("lblContactName") as Label).Text);
PopulateDropDown((cells.FindControl("ddlCity") as DropDownList), (cells.FindControl("lblCity") as Label).Text);
PopulateDropDown((cells.FindControl("ddlCountry") as DropDownList), (cells.FindControl("lblCountry") as Label).Text);
PopulateDropDown((cells.FindControl("ddlPostalCode") as DropDownList), (cells.FindControl("lblPostalCode") as Label).Text);
}
private void PopulateDropDown(DropDownList ddl, string columnName)
{
ddl.DataSource = BindDropDown(columnName);
ddl.DataTextField = columnName;
ddl.DataValueField = columnName;
ddl.DataBind();
ddl.Items.Insert(0, new ListItem("Please Select", "0"));
}
private void BindGrid()
{
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("GetCustomers");
cmd.CommandType = CommandType.StoredProcedure;
if (ViewState["ContactName"] != null && ViewState["ContactName"].ToString() != "0")
{
cmd.Parameters.AddWithValue("@ContactNameValue", ViewState["ContactName"].ToString());
}
if (ViewState["City"] != null && ViewState["City"].ToString() != "0")
{
cmd.Parameters.AddWithValue("@CityValue", ViewState["City"].ToString());
}
if (ViewState["Country"] != null && ViewState["Country"].ToString() != "0")
{
cmd.Parameters.AddWithValue("@CountryValue", ViewState["Country"].ToString());
}
if (ViewState["PostalCode"] != null && ViewState["PostalCode"].ToString() != "0")
{
cmd.Parameters.AddWithValue("@PostalCodeValue", ViewState["PostalCode"].ToString());
}
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
if (dt.Rows.Count == 0)
{
dt.Rows.Add("", "", "", "");
}
GridView1.DataSource = dt;
GridView1.DataBind();
if (string.IsNullOrEmpty((GridView1.Rows[0].FindControl("hfContactName") as HiddenField).Value))
{
GridView1.Rows[0].Visible = false;
emptyTable.Visible = true;
}
else
{
emptyTable.Visible = false;
}
this.BindDropDownList();
TableCell cell = GridView1.HeaderRow.Cells[0];
setDropdownselectedItem(ViewState["ContactName"] != null ? (string)ViewState["ContactName"] : string.Empty, cell.FindControl("ddlContactName") as DropDownList);
setDropdownselectedItem(ViewState["City"] != null ? (string)ViewState["City"] : string.Empty, cell.FindControl("ddlCity") as DropDownList);
setDropdownselectedItem(ViewState["Country"] != null ? (string)ViewState["Country"] : string.Empty, cell.FindControl("ddlCountry") as DropDownList);
setDropdownselectedItem(ViewState["PostalCode"] != null ? (string)ViewState["PostalCode"] : string.Empty, cell.FindControl("ddlPostalCode") as DropDownList);
}
private void setDropdownselectedItem(string selectedvalue, DropDownList ddl)
{
if (!string.IsNullOrEmpty(selectedvalue))
{
ddl.Items.FindByValue(selectedvalue).Selected = true;
}
}
protected void DropDownChange(object sender, EventArgs e)
{
DropDownList dropdown = (DropDownList)sender;
string selectedValue = dropdown.SelectedItem.Value;
switch (dropdown.ID.ToLower())
{
case "ddlcontactname":
ViewState["ContactName"] = selectedValue;
break;
case "ddlcity":
ViewState["City"] = selectedValue;
break;
case "ddlcountry":
ViewState["Country"] = selectedValue;
break;
case "ddlpostalcode":
ViewState["PostalCode"] = selectedValue;
break;
}
this.BindGrid();
}
private DataTable BindDropDown(string columnName)
{
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand("SELECT DISTINCT " + columnName + " FROM CUSTOMERS WHERE " + columnName + " IS NOT NULL", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.BindGrid();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGrid()
End If
End Sub
Private Sub BindDropDownList()
Dim cells As TableCell = GridView1.HeaderRow.Cells(0)
PopulateDropDown(TryCast(cells.FindControl("ddlContactName"), DropDownList), TryCast(cells.FindControl("lblContactName"), Label).Text)
PopulateDropDown(TryCast(cells.FindControl("ddlCity"), DropDownList), TryCast(cells.FindControl("lblCity"), Label).Text)
PopulateDropDown(TryCast(cells.FindControl("ddlCountry"), DropDownList), TryCast(cells.FindControl("lblCountry"), Label).Text)
PopulateDropDown(TryCast(cells.FindControl("ddlPostalCode"), DropDownList), TryCast(cells.FindControl("lblPostalCode"), Label).Text)
End Sub
Private Sub PopulateDropDown(ddl As DropDownList, columnName As String)
ddl.DataSource = BindDropDown(columnName)
ddl.DataTextField = columnName
ddl.DataValueField = columnName
ddl.DataBind()
ddl.Items.Insert(0, New ListItem("Please Select", "0"))
End Sub
Private Sub BindGrid()
Dim dt As New DataTable()
Dim strConnString As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
Dim cmd As New SqlCommand("GetCustomers")
cmd.CommandType = CommandType.StoredProcedure
If ViewState("ContactName") IsNot Nothing AndAlso ViewState("ContactName").ToString() <> "0" Then
cmd.Parameters.AddWithValue("@ContactNameValue", ViewState("ContactName").ToString())
End If
If ViewState("City") IsNot Nothing AndAlso ViewState("City").ToString() <> "0" Then
cmd.Parameters.AddWithValue("@CityValue", ViewState("City").ToString())
End If
If ViewState("Country") IsNot Nothing AndAlso ViewState("Country").ToString() <> "0" Then
cmd.Parameters.AddWithValue("@CountryValue", ViewState("Country").ToString())
End If
If ViewState("PostalCode") IsNot Nothing AndAlso ViewState("PostalCode").ToString() <> "0" Then
cmd.Parameters.AddWithValue("@PostalCodeValue", ViewState("PostalCode").ToString())
End If
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(dt)
If dt.Rows.Count = 0 Then
dt.Rows.Add("", "", "", "")
End If
GridView1.DataSource = dt
GridView1.DataBind()
If String.IsNullOrEmpty(TryCast(GridView1.Rows(0).FindControl("hfContactName"), HiddenField).Value) Then
GridView1.Rows(0).Visible = False
emptyTable.Visible = True
Else
emptyTable.Visible = False
End If
Me.BindDropDownList()
Dim cell As TableCell = GridView1.HeaderRow.Cells(0)
setDropdownselectedItem(If(ViewState("ContactName") IsNot Nothing, DirectCast(ViewState("ContactName"), String), String.Empty), TryCast(cell.FindControl("ddlContactName"), DropDownList))
setDropdownselectedItem(If(ViewState("City") IsNot Nothing, DirectCast(ViewState("City"), String), String.Empty), TryCast(cell.FindControl("ddlCity"), DropDownList))
setDropdownselectedItem(If(ViewState("Country") IsNot Nothing, DirectCast(ViewState("Country"), String), String.Empty), TryCast(cell.FindControl("ddlCountry"), DropDownList))
setDropdownselectedItem(If(ViewState("PostalCode") IsNot Nothing, DirectCast(ViewState("PostalCode"), String), String.Empty), TryCast(cell.FindControl("ddlPostalCode"), DropDownList))
End Sub
Private Sub setDropdownselectedItem(selectedvalue As String, ddl As DropDownList)
If Not String.IsNullOrEmpty(selectedvalue) Then
ddl.Items.FindByValue(selectedvalue).Selected = True
End If
End Sub
Protected Sub DropDownChange(sender As Object, e As EventArgs)
Dim dropdown As DropDownList = DirectCast(sender, DropDownList)
Dim selectedValue As String = dropdown.SelectedItem.Value
Select Case dropdown.ID.ToLower()
Case "ddlcontactname"
ViewState("ContactName") = selectedValue
Exit Select
Case "ddlcity"
ViewState("City") = selectedValue
Exit Select
Case "ddlcountry"
ViewState("Country") = selectedValue
Exit Select
Case "ddlpostalcode"
ViewState("PostalCode") = selectedValue
Exit Select
End Select
Me.BindGrid()
End Sub
Private Function BindDropDown(columnName As String) As DataTable
Dim strConnString As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand((Convert.ToString((Convert.ToString("SELECT DISTINCT ") & columnName) + " FROM CUSTOMERS WHERE ") & columnName) + " IS NOT NULL", con)
Dim sda As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
Return dt
End Function
Protected Sub OnPaging(sender As Object, e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
Me.BindGrid()
End Sub
ScreenShot