locked
Selecting All-status to show in Grid view RRS feed

  • Question

  • User-1578974752 posted

    I have a drop down list with few status

    Pending ,Draft, Submitted etc. Once each one is clicked the selected status record will show in the gridview. Now How can I include ALL. If I select All then all status must show in the gridview.

    Appreciate the help

    Tuesday, April 2, 2019 12:26 PM

Answers

  • User-1716253493 posted

    Add to the dropdown items, dropdown item text="All" value="%"

    Use LIKE operator in the query

    SELET * FROM TBL WHERE status LIKE @status

    or

    dropdown item text="All" value="All"

    Then

    SELET * FROM TBL WHERE (status = @status or @status='all')

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 3, 2019 6:17 AM
  • User288213138 posted

    Hi shsu:
      According to your description, I Submitted a demo. In the dropdownlist control, I selected All,Draft and Submitted etc.The status will show in the gridview.

     string strcon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            protected void Page_Load(object sender, EventArgs e)
            {
                if(!IsPostBack)
                {
                    BindLocation();
                    BindGridview();
                }
            }
            protected void BindLocation()
            {
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(strcon))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Select Distinct status from UserDetails", con);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    con.Close();
                    ddlLocation.DataSource = dt;
                    ddlLocation.DataTextField = "status";
                    ddlLocation.DataValueField = "status";
                    ddlLocation.DataBind();
                    ddlLocation.Items.Insert(0, new ListItem("All", ""));
                }
            }
    
            protected void BindGridview()
            {
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(strcon))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("select * from userdetails", con);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    con.Close();
                    gvDetails.DataSource = dt;
                    gvDetails.DataBind();
                }
            }
    
            protected void ddlLocation_SelectedIndexChanged(object sender, EventArgs e)
            {
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(strcon))
                {
                    con.Open();
                    if (ddlLocation.SelectedValue != "")
                    {
                        SqlCommand cmd = new SqlCommand("select * from userdetails where status =@status", con);
                        cmd.Parameters.AddWithValue("@status", ddlLocation.SelectedValue);
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                    }
                    else
                    {
                        SqlCommand cmd = new SqlCommand("select * from userdetails", con);
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                    }
                    con.Close();
                    gvDetails.DataSource = dt;
                    gvDetails.DataBind();
                }
            }

    The Result:

      
    Best Regard
    Sam.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 3, 2019 8:52 AM

All replies

  • User-1716253493 posted

    Add to the dropdown items, dropdown item text="All" value="%"

    Use LIKE operator in the query

    SELET * FROM TBL WHERE status LIKE @status

    or

    dropdown item text="All" value="All"

    Then

    SELET * FROM TBL WHERE (status = @status or @status='all')

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 3, 2019 6:17 AM
  • User288213138 posted

    Hi shsu:
      According to your description, I Submitted a demo. In the dropdownlist control, I selected All,Draft and Submitted etc.The status will show in the gridview.

     string strcon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            protected void Page_Load(object sender, EventArgs e)
            {
                if(!IsPostBack)
                {
                    BindLocation();
                    BindGridview();
                }
            }
            protected void BindLocation()
            {
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(strcon))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Select Distinct status from UserDetails", con);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    con.Close();
                    ddlLocation.DataSource = dt;
                    ddlLocation.DataTextField = "status";
                    ddlLocation.DataValueField = "status";
                    ddlLocation.DataBind();
                    ddlLocation.Items.Insert(0, new ListItem("All", ""));
                }
            }
    
            protected void BindGridview()
            {
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(strcon))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("select * from userdetails", con);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    con.Close();
                    gvDetails.DataSource = dt;
                    gvDetails.DataBind();
                }
            }
    
            protected void ddlLocation_SelectedIndexChanged(object sender, EventArgs e)
            {
                DataTable dt = new DataTable();
                using (SqlConnection con = new SqlConnection(strcon))
                {
                    con.Open();
                    if (ddlLocation.SelectedValue != "")
                    {
                        SqlCommand cmd = new SqlCommand("select * from userdetails where status =@status", con);
                        cmd.Parameters.AddWithValue("@status", ddlLocation.SelectedValue);
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                    }
                    else
                    {
                        SqlCommand cmd = new SqlCommand("select * from userdetails", con);
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                    }
                    con.Close();
                    gvDetails.DataSource = dt;
                    gvDetails.DataBind();
                }
            }

    The Result:

      
    Best Regard
    Sam.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 3, 2019 8:52 AM