Filter GridView based on two DropDownList using C# in ASP.Net

Awais

SOLVED CLOSED ACTIVE ANSWERED
User: Awais
Posted: on Aug 06, 2016 01:08 AM
Forum: Data Controls
Answer: 1
Views: 3824
Sample Code: Download

I want to filter grid with 2 Drop downs which are outside the gridview scope. First one is Class Dropdown & Second One is Exam DropDown. I want to get filtered record after selecting class and then exam so that i can get the filtered record of exam schedule of that class. My BackEnd Code is:

  protected void BindExamdropdown()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Final Year Project"].ToString());
            con.Open();
            SqlCommand cmd = new SqlCommand("Select * from Exam", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Close();
            ddlexamid.DataSource = dt;
            ddlexamid.DataTextField = "ExamName";
            ddlexamid.DataValueField = "ExamID";
            ddlexamid.DataBind();
            ddlexamid.Items.Insert(0, new ListItem("--Select--", "0"));
        }
        protected void BindClassdropdown()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Final Year Project"].ToString());
            con.Open();
            SqlCommand cmd = new SqlCommand("Select * from Class", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Close();
            ddlclassid.DataSource = dt;
            ddlclassid.DataTextField = "StudentClassName";
            ddlclassid.DataValueField = "StudentClassID";
            ddlclassid.DataBind();
            ddlclassid.Items.Insert(0, new ListItem("--Select--", "0"));
        }

 protected void ddlclassid_SelectedIndexChanged(object sender, EventArgs e)
        {
            string StudentClassName = Convert.ToString(ddlclassid.SelectedItem);
            string ExamName = Convert.ToString(ddlexamid.SelectedItem);
            SqlConnection oconn = new SqlConnection(ConfigurationManager.ConnectionStrings["Final Year Project"].ToString());
            oconn.Open();
            SqlCommand ocmd = new SqlCommand("Select SubjectExamSchedule.SubjectExamID,Section.StudentSectionName,Class.StudentClassName,Subject.SubjectName,Exam.ExamName,SubjectExamSchedule.ExamDate from Student Inner join Section On Section.StudentSectionID = Student.StudentSectionID Inner join Class On Class.StudentClassID = Section.StudentClassID Inner join Subject On Subject.StudentClassID = Class.StudentClassID Inner Join SubjectExamSchedule On SubjectExamSchedule.SubjectID = Subject.SubjectID Inner Join Exam On Exam.ExamID = SubjectExamSchedule.ExamID where Class.StudentClassName='" + StudentClassName + "'", oconn);
            SqlDataAdapter oda = new SqlDataAdapter(ocmd);
            SqlCommandBuilder builder = new SqlCommandBuilder(oda);
            DataSet ds = new DataSet();
            oda.Fill(ds);
            oconn.Close();
            gvexamscheduleshow.DataSource = ds;
            gvexamscheduleshow.DataBind();
        }

        protected void ddlexamid_SelectedIndexChanged(object sender, EventArgs e)
        {
            string ExamName = Convert.ToString(ddlexamid.SelectedItem);
            SqlConnection oconn = new SqlConnection(ConfigurationManager.ConnectionStrings["Final Year Project"].ToString());
            oconn.Open();
            SqlCommand ocmd = new SqlCommand("Select SubjectExamSchedule.SubjectExamID,Section.StudentSectionName,Class.StudentClassName,Subject.SubjectName,Exam.ExamName,SubjectExamSchedule.ExamDate from Student Inner join Section On Section.StudentSectionID = Student.StudentSectionID Inner join Class On Class.StudentClassID = Section.StudentClassID Inner join Subject On Subject.StudentClassID = Class.StudentClassID Inner Join SubjectExamSchedule On SubjectExamSchedule.SubjectID = Subject.SubjectID Inner Join Exam On Exam.ExamID = SubjectExamSchedule.ExamID where Class.StudentClassName='" + ExamName + "'", oconn);
            SqlDataAdapter oda = new SqlDataAdapter(ocmd);
            SqlCommandBuilder builder = new SqlCommandBuilder(oda);
            DataSet ds = new DataSet();
            oda.Fill(ds);
            oconn.Close();
            gvexamscheduleshow.DataSource = ds;
            gvexamscheduleshow.DataBind();
        }

My Html Code is: <asp:DropDownList ID="ddlclassid" runat="server" class='form-control' OnSelectedIndexChanged="ddlclassid_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList> <asp:DropDownList ID="ddlexamid" runat="server" class='form-control' OnSelectedIndexChanged="ddlclassid_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList>

Download FREE API for Word, Excel and PDF in ASP.Net

AnandM

0 3

Reply
ANSWER
User: AnandM
Replied: on Aug 08, 2016 08:58 AM
Report

Hi Awais,

I have created a sample which full fill your requirement

SQL

CREATE PROCEDURE TestGetDetails
	 @CustomerId VARCHAR(20) = NULL
	,@CustomerName VARCHAR(60) = NULL
AS
BEGIN 
	SELECT CustomerID, CompanyName,ContactName,Address,City
	FROM Customers 
	WHERE (CustomerID = @CustomerId OR @CustomerId IS NULL)
	AND (ContactName = @CustomerName OR @CustomerName IS NULL)
END

HTML

<div>
    Customer ID:-<asp:DropDownList ID="ddlCustomerID" OnSelectedIndexChanged="ddl_SelectedIndexChanged"
        AutoPostBack="true" runat="server">
    </asp:DropDownList>
    <br />
    Customer Name:-<asp:DropDownList ID="ddlCustomerName" OnSelectedIndexChanged="ddl_SelectedIndexChanged"
        AutoPostBack="true" runat="server">
    </asp:DropDownList>
    <br />
    <asp:GridView ID="gvCustomers" runat="server" />
</div>

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GetCustomersID();
        GetCustomersName();
    }
}

protected void ddl_SelectedIndexChanged(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(constr))
    {
        string customerId = ddlCustomerID.SelectedItem.Value;
        string customerName = ddlCustomerName.SelectedItem.Value;
        using (SqlCommand cmd = new SqlCommand("TestGetDetails"))
        {
            if (!string.IsNullOrEmpty(customerName))
            {
                cmd.Parameters.AddWithValue("@CustomerName", customerName);
            }
            if (!string.IsNullOrEmpty(customerId))
            {
                cmd.Parameters.AddWithValue("@CustomerId", customerId);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            gvCustomers.DataSource = dt;
            gvCustomers.DataBind();
        }
    }
}

public void GetCustomersID()
{
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "SELECT DISTINCT CustomerID FROM Customers";
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            this.ddlCustomerID.DataSource = dt;
            this.ddlCustomerID.DataTextField = "CustomerID";
            this.ddlCustomerID.DataValueField = "CustomerID";
            this.ddlCustomerID.DataBind();
            this.ddlCustomerID.Items.Insert(0, new ListItem("Select", ""));
        }
    }
}

public void GetCustomersName()
{
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "SELECT DISTINCT ContactName FROM Customers";
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            this.ddlCustomerName.DataSource = dt;
            this.ddlCustomerName.DataTextField = "ContactName";
            this.ddlCustomerName.DataValueField = "ContactName";
            this.ddlCustomerName.DataBind();
            this.ddlCustomerName.Items.Insert(0, new ListItem("Select", ""));
        }
    }
}

ScreenShot

Hope this works for you

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Filter GridView based on two DropDownList using C# in ASP.Net

Company

Explore

Follow us

Contact Us