Value changes while filtering two dropdownlist in gridview

Question

1.00/5 (1 vote)

See more:

, +

Hi
The filtering value changes while filtering two dropdownlist.Actually I have two dropdown List 1.Age and 2.Salary ...If I select Age say (20-30) and Salary (40000-50000) the result should come at filtering both.

What I have tried:

C#

<pre>protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
               // bind();
                BindGrid();
                BindAge();
                BindSalary();
            }
        }
        protected void bind()
        {
            cn.Open();
            SqlCommand cmd = new SqlCommand("gvempdetails", cn);
            cmd.Parameters.AddWithValue("@Action", "SELECT");
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cn.Close();
            if (ds.Tables[0].Rows.Count > 0)
            {
                gvDetails.DataSource = ds;
                gvDetails.DataBind();
            }
            else
            {
                ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
                gvDetails.DataSource = ds;
                gvDetails.DataBind();
                int columncount = gvDetails.Rows[0].Cells.Count;
                gvDetails.Rows[0].Cells.Clear();
                gvDetails.Rows[0].Cells.Add(new TableCell());
                gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
                gvDetails.Rows[0].Cells[0].Text = "Enter the details";
            }
        }
        protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName.Equals("Add"))
            {

                TextBox txtEmpId = (TextBox)gvDetails.FooterRow.FindControl("txtempid");
                TextBox txtEmpName = (TextBox)gvDetails.FooterRow.FindControl("txtempname1");
                TextBox txtEmpDep = (TextBox)gvDetails.FooterRow.FindControl("txtdep1");
                TextBox txtAge = (TextBox)gvDetails.FooterRow.FindControl("txtage1");
                TextBox txtSal = (TextBox)gvDetails.FooterRow.FindControl("txtsal1");

                string Id = txtEmpId.Text;
                string name = txtEmpName.Text;
                string department = txtEmpDep.Text;
                string age = txtAge.Text;
                string salary = txtSal.Text;

                INSERTEmployee(Id, name, department, age, salary);
                gvDetails.EditIndex = -1;
                bind();

            }
        }
        protected void INSERTEmployee(string Id, string name, string department, string age, string salary)
        {

            SqlCommand cmd = new SqlCommand();
            cn.Open();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "gvempdetails";

            cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEEID", int.Parse(age.Trim())));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEENAME", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@DEPARTMENT", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@AGE", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@SALARY", int.Parse(salary.Trim())));

            cmd.Parameters["@Action"].Value = "INSERT";
            cmd.Parameters["@EMPLOYEEID"].Value = Id;
            cmd.Parameters["@EMPLOYEENAME"].Value = name;
            cmd.Parameters["@DEPARTMENT"].Value = department;
            cmd.Parameters["@AGE"].Value = age;
            cmd.Parameters["@SALARY"].Value = salary;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            gvDetails.DataSource = dt;
            gvDetails.DataBind();
            cn.Close();


        }
        protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            Label EmpId = (Label)gvDetails.Rows[e.RowIndex].FindControl("lblempid");

            TextBox txtEmpName = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtempname");
            TextBox txtEmpDep = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtdep");
            TextBox txtAge = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtage");
            TextBox txtSal = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtsal");

            string Id = EmpId.Text;
            string name = txtEmpName.Text;
            string department = txtEmpDep.Text;
            string age = txtAge.Text;
            string salary = txtSal.Text;

            UpdateEmployee(Id, name, department, age, salary);

            gvDetails.EditIndex = -1;
            bind();
        }
        protected void UpdateEmployee(string Id, string name, string department, string age, string salary)
        {
            SqlCommand cmd = new SqlCommand();
            cn.Open();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "gvempdetails";

            cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEEID", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEENAME", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@DEPARTMENT", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@AGE", SqlDbType.VarChar, 30));
            cmd.Parameters.Add(new SqlParameter("@SALARY", SqlDbType.Int));

            cmd.Parameters["@Action"].Value = "UPDATE";

            cmd.Parameters["@EMPLOYEEID"].Value = Convert.ToInt32(Id.ToString());
            cmd.Parameters["@EMPLOYEENAME"].Value = name;
            cmd.Parameters["@DEPARTMENT"].Value = department;
            cmd.Parameters["@AGE"].Value = age;
            cmd.Parameters["@SALARY"].Value = Convert.ToInt32(salary.ToString());
            //cmd.Parameters["@SALARY"].Value = Convert.ToInt32(salary.ToString());

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            gvDetails.DataSource = dt;
            gvDetails.DataBind();

            cn.Close();
        }

       










        protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
        {

            gvDetails.EditIndex = e.NewEditIndex;
            bind();
        }
        protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {

            gvDetails.EditIndex = -1;
            bind();
        }
        protected void DeleteEmployee(string id)
        {
            SqlCommand cmd = new SqlCommand();
            cn.Open();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "gvempdetails";

            cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@EMPLOYEEID", int.Parse(id.Trim())));
            cmd.Parameters["@Action"].Value = "DELETE";
            cmd.Parameters["@EMPLOYEEID"].Value = id;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            gvDetails.DataSource = dt;
            gvDetails.DataBind();
            cn.Close();
        }
        protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            Label EmpId = (Label)gvDetails.Rows[e.RowIndex].FindControl("lblempid");
            string id = EmpId.Text;

            DeleteEmployee(id);
            gvDetails.EditIndex = -1;
            bind();
        }

        protected void OnSelectedIndexChanged(object sender, EventArgs e)
        {

        }
        protected void OnPaging(object sender, GridViewPageEventArgs e)
        {
            gvDetails.PageIndex = e.NewPageIndex;
            this.bind();

        }

        protected void BindSalary()
        {
            DataTable dt = new DataTable();
            cn.Open();
            SqlCommand cmd = new SqlCommand("Select * from Salary1", cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //DataSet ds = new DataSet();
            da.Fill(dt);
            cn.Close();
            ddlAddSalary1.DataSource = dt;
            ddlAddSalary1.DataTextField = "Salary";
            ddlAddSalary1.DataValueField = "Salary";
            ddlAddSalary1.DataBind();
            ddlAddSalary1.Items.Insert(0, new ListItem("--Select--"));

        }

        protected void ddlAddSalary1_SelectedIndexChanged1(object sender, EventArgs e)
        {
            
            BindGrid();
        }

        protected void BindAge()
        {
            DataTable dt = new DataTable();
            cn.Open();
            SqlCommand cmd = new SqlCommand("Select * from Age", cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //DataSet ds = new DataSet();
            da.Fill(dt);
            cn.Close();
            ddlAge.DataSource = dt;
            ddlAge.DataTextField = "Age";
            ddlAge.DataValueField = "Age";
            ddlAge.DataBind();
            ddlAge.Items.Insert(0, new ListItem("--Select--"));

        }
        protected void ddlAge_SelectedIndexChanged(object sender, EventArgs e)
        {
           

            BindGrid();
        }
        protected void gvDetails_OnRowDataBound(object sender, GridViewRowEventArgs e)
        {

        }
        private void BindGrid()
        {
            string query = " select * from gvdetails17 ";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            if (ddlAddSalary1.SelectedValue != "")
            {
                string[] sal = ddlAddSalary1.SelectedValue.Split('-');
                string from = sal[0];
                string to = sal[1];


                query += " and  Sal between  @fromsal and @tosal ";
                cmd.Parameters.AddWithValue("@fromsal", from);
                cmd.Parameters.AddWithValue("@tosal", to);

            }
            if (ddlAge.SelectedValue != "")
            {
                string[] ages = ddlAge.SelectedValue.Split('-');
                string from = ages[0];
                string to = ages[1];

                query += " and  Sal between  @fromage and @toage ";
                cmd.Parameters.AddWithValue("@fromage", from);
                cmd.Parameters.AddWithValue("@toage", to);
            }
            cmd.CommandText = query;
            DataTable dt = new DataTable();
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            gvDetails.DataSource = dt;
            gvDetails.DataBind();

        }


        
    }
}

Posted 24-Feb-17 0:58am

Member 12605293

Updated 24-Feb-17 2:21am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-02-24T01:51:00

Solution 1

private void BindGrid( )
      {
          string query = " select * from gvdetails17 WHERE 1=1 ";
          SqlCommand cmd = new SqlCommand();
          cmd.Connection = cn;
          if (ddlAddSalary1.SelectedValue != "")
          {
              string[] sal = ddlAddSalary1.SelectedValue.Split('-');
              string from = sal[0];
              string to = sal[1];


              query += " and  Sal between  @fromsal and @tosal ";
              cmd.Parameters.AddWithValue("@fromsal", from);
              cmd.Parameters.AddWithValue("@tosal", to);

          }
          if (ddlAge.SelectedValue != "")
          {
              string[] ages = ddlAge.SelectedValue.Split('-');
              string from = ages[0];
              string to = ages[1];

              query += " and  Age between  @fromage and @toage ";
              cmd.Parameters.AddWithValue("@fromage", from);
              cmd.Parameters.AddWithValue("@toage", to);
          }
          cmd.CommandText = query;
          cmd.CommandType = CommandType.Text;
          SqlDataAdapter da = new SqlDataAdapter(cmd);
           DataTable dt = new DataTable();
          da.Fill(dt);
          gvDetails.DataSource = dt;
          gvDetails.DataBind();

      }


      protected void ddlAddSalary1_SelectedIndexChanged1(object sender, EventArgs e)
      {
          BindGrid();
      }


      protected void ddlAge_SelectedIndexChanged(object sender, EventArgs e)
      {
          BindGrid();
      }

Posted 24-Feb-17 1:51am

Karthik_Mahalingam

Updated 24-Feb-17 2:18am

v2

Comments

Member 12605293 24-Feb-17 8:10am

Hi Karthik
I am getting Incorrect syntax near da.Fill(dt);

Karthik_Mahalingam 24-Feb-17 8:14am

query += " and Sal between @fromage and @toage ";
it should be your age column name.

DataTable dt = new DataTable(); this line was missing

check the updated solution.

Member 12605293 24-Feb-17 8:22am

Yes I added those line still the error comes in the dt..By debugging there is no value passes in dt..Please see my entire code

Karthik_Mahalingam 24-Feb-17 8:23am

exact error message?

Member 12605293 24-Feb-17 8:25am

Incorrect syntax near the keyword 'and'.

Karthik_Mahalingam 24-Feb-17 8:43am

change the query as

query += " and Sal between ( @fromsal and @tosal ) ";
query += " and Age between ( @fromage and @toage ) ";

Member 12605293 24-Feb-17 8:49am

same error is comming
pls check my post back

Karthik_Mahalingam 24-Feb-17 8:51am

how can i check your post back
share the screenshot

Member 12605293 24-Feb-17 8:55am

By debugging both the if condition is skipping

Karthik_Mahalingam 24-Feb-17 8:57am

BindAge();
BindSalary();
BindGrid();

Member 12605293 24-Feb-17 9:02am

what about using bind which has select Asction???

Karthik_Mahalingam 24-Feb-17 8:54am

 
            string query = " select * from gvdetails17 WHERE 1=1 ";

Member 12605293 24-Feb-17 9:01am

Yes still after changing the error exist (Incorrect syntax near the keyword 'and'.)

Karthik_Mahalingam 24-Feb-17 9:04am

what is the query you are getting here
cmd.CommandText = query;

Member 12605293 24-Feb-17 9:06am

select * from gvdetails17 WHERE 1=1

Karthik_Mahalingam 24-Feb-17 9:09am

run this in sql server studio and check

Member 12605293 24-Feb-17 9:10am

Getting all the details from the Table

Karthik_Mahalingam 24-Feb-17 9:14am

then what is the issue in your code.

Member 12605293 24-Feb-17 9:19am

:( it skips from both the if condition

Karthik_Mahalingam 24-Feb-17 9:20am

check what value you are getting here
ddlAddSalary1.SelectedValue

Member 12605293 24-Feb-17 9:22am

if is skipping for both the if condition

Karthik_Mahalingam 24-Feb-17 9:26am

what value you are getting in
ddlAddSalary1.SelectedValue
keep break point and chk

Member 12605293 24-Feb-17 9:40am

Hi. It works fine

Karthik_Mahalingam 24-Feb-17 9:41am

ok.. good
close the post.

Member 12605293 24-Feb-17 9:44am

I mean the grid view visibility is goin

Member 12605293 24-Feb-17 9:41am

But if I select the Age for the first time the page becomes empty since there is not an item selected in salary.It happens initialy

Karthik_Mahalingam 24-Feb-17 9:44am

hmm, it depends on the item selected in the ddl.

Member 12605293 24-Feb-17 9:45am

Is that possible to make the visiblity true??

Karthik_Mahalingam 24-Feb-17 9:45am

ddl.visible = true;

Member 12605293 24-Feb-17 9:52am

Is that possible to set both visible at the select in ddls

Karthik_Mahalingam 24-Feb-17 9:53am

ddlsal.visible = true;
ddlage.visible= true;

Member 12605293 24-Feb-17 9:57am

The grid is not visible

Karthik_Mahalingam 24-Feb-17 9:58am

grid.visible = true