Click here to Skip to main content
13,554,524 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
i have a listbox and gridview,if i made multple selection of item of listbox , data of last item show only ..i want to  show record of all selected item of listbox


What I have tried:

protected void searchname_date()
{

    if (ListBox1.Items.Count > 0)
    {
        for (int i = 0; i < ListBox1.Items.Count; i++)
        {
            if (ListBox1.Items[i].Selected)
            {
                string selectedItem = ListBox1.Items[i].Text;

                SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                string Query = "SELECT *  FROM [dsr_data] where (date_time between '" + TextBox1.Text + "'  and    '" + TextBox4.Text + "') And  session_name='" + selectedItem + "'";
                DataTable dtAdmin = new DataTable();
                SqlDataAdapter da;
                da = new SqlDataAdapter(Query, cnn);
                da.Fill(dtAdmin);
                if (dtAdmin.Rows.Count > 0)
                {
                    GVmydsr.DataSource = dtAdmin;
                    GVmydsr.DataBind();

                }
                else
                {
                    GVmydsr.DataSource = null;
                    GVmydsr.DataBind();
                }


            }

        }
    }
}
Posted 1-Nov-17 20:04pm
Updated 1-Nov-17 20:22pm

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

try

protected void searchname_date()
      {
          List<string> lstSelectedItems = new List<string>();

          if (ListBox1.Items.Count > 0)
              for (int i = 0; i < ListBox1.Items.Count; i++)
                  if (ListBox1.Items[i].Selected)
                      lstSelectedItems.Add(ListBox1.Items[i].Text);


          string inQuery = string.Join("','", lstSelectedItems);
          inQuery = "'" + inQuery + "'";

          SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
          string Query = "SELECT *  FROM [dsr_data] where (date_time between @from  and    @to ) And  session_name in ({0})";
          Query = string.Format(Query, inQuery);
          SqlCommand cmd = new SqlCommand(Query, cnn);
          cmd.Parameters.AddWithValue("@from", TextBox1.Text);
          cmd.Parameters.AddWithValue("@to", TextBox4.Text);

          DataTable dtAdmin = new DataTable();
          SqlDataAdapter da;
          da = new SqlDataAdapter(Query, cnn);
          da.Fill(dtAdmin);
          if (dtAdmin.Rows.Count > 0)
              GVmydsr.DataSource = dtAdmin;
          else
              GVmydsr.DataSource = null;
          GVmydsr.DataBind();


      }



Note:Formatting the sql Query string is vulnerable to SQL Injection[^] attacks
always use Parameterized queries to prevent SQL Injection Attacks in SQL Server[^]
  Permalink  
v2
Comments
ADI-345 2-Nov-17 2:33am
   
still facing same issue, only last record of listbox item shown
karthik2528 2-Nov-17 2:37am
   
what is the value you are getting in "inQuery "
ADI-345 2-Nov-17 2:42am
   
protected void searchname_date()
{
List<string> lstSelectedItems = new List<string>();


if (ListBox1.Items.Count > 0)
{
for (int i = 0; i < ListBox1.Items.Count; i++)
{
if (ListBox1.Items[i].Selected)
{
lstSelectedItems.Add(ListBox1.Items[i].Text);

string inQuery = string.Join("','", lstSelectedItems);
inQuery = "'" + inQuery + "'";

SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
string Query = "SELECT * FROM [dsr_data] where (date_time between '" + TextBox1.Text + "' and '" + TextBox4.Text + "') And session_name='" + inQuery + "'";
Query = string.Format(Query, inQuery);
DataTable dtAdmin = new DataTable();
SqlDataAdapter da;
da = new SqlDataAdapter(Query, cnn);
da.Fill(dtAdmin);
if (dtAdmin.Rows.Count > 0)
{
GVmydsr.DataSource = dtAdmin;
GVmydsr.DataBind();

}
else
{
GVmydsr.DataSource = null;
GVmydsr.DataBind();
}


}

}
}
}



ERROR :-
Deepali is one the name of item of listbox.
Incorrect syntax near 'Deepali'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'Deepali'.
karthik2528 2-Nov-17 2:44am
   
protected void searchname_date()
        {
            List<string> lstSelectedItems = new List<string>();
 
            if (ListBox1.Items.Count > 0)
                for (int i = 0; i < ListBox1.Items.Count; i++)
                    if (ListBox1.Items[i].Selected)
                        lstSelectedItems.Add(ListBox1.Items[i].Text);
 

            string inQuery = string.Join("','", lstSelectedItems);
            inQuery = "'" + inQuery + "'";
 
            SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            string Query = "SELECT *  FROM [dsr_data] where (date_time between @from  and    @to ) And  session_name in ({0})";
            Query = string.Format(Query, inQuery);
            SqlCommand cmd = new SqlCommand(Query, cnn);
            cmd.Parameters.AddWithValue("@from", TextBox1.Text);
            cmd.Parameters.AddWithValue("@to", TextBox4.Text);
 
            DataTable dtAdmin = new DataTable();
            SqlDataAdapter da;
            da = new SqlDataAdapter(Query, cnn);
            da.Fill(dtAdmin);
            if (dtAdmin.Rows.Count > 0)
                GVmydsr.DataSource = dtAdmin;
            else
                GVmydsr.DataSource = null;
            GVmydsr.DataBind();
 

        }
ADI-345 2-Nov-17 2:49am
   
sir actually what happen , when we select two item from listbox , it work fine with one item but with two item
SELECT * FROM [dsr_data] where date_time between '11/1/2017' and '11/30/2017' And session_name='abc','efg'
this query doesnot give any output in sql server..
karthik2528 2-Nov-17 2:51am
   
   SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            string Query = "SELECT *  FROM [dsr_data] where (date_time between @from  and    @to ) And  session_name in ({0})";
            Query = string.Format(Query, inQuery);
            SqlCommand cmd = new SqlCommand(Query, cnn);
            cmd.Parameters.AddWithValue("@from", TextBox1.Text);
            cmd.Parameters.AddWithValue("@to", TextBox4.Text);
karthik2528 2-Nov-17 2:51am
   
ADI-345 2-Nov-17 2:54am
   
Yes i forget to use IN clause ..now it work.. thanks
karthik2528 2-Nov-17 3:06am
   
welcome

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.180515.1 | Last Updated 2 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100