Click here to Skip to main content
14,981,768 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi everyone,,
i have stucked in this steps so it will be so appreciated if you could give hand,
i want to retrieve data from sql to dataGridView with multi selection
for example i want dataGridView show data where name is ('sam','jole')
and im using checkedListBox for selecting but it only retrieve the first selection
and this is my code
C#
private void button1_Click(object sender, EventArgs e)
        {
            List<string> oranad = new List<string>() ;
            foreach (string sm in checkedListBox1.Items)
            {
                

                cn.Open();
                string select = "select * from productvw where firstname in(@firstname)";

                SqlDataAdapter dataAdapter = new SqlDataAdapter(select, cn);
               
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
               dataAdapter.SelectCommand.Parameters.Add("@firstname",sm);
                DataTable ds = new DataTable();
                dataAdapter.Fill(ds);
                dataGridView1.ReadOnly = true;
                dataGridView1.DataSource = ds;
                cn.Close();

those code only retrieve the first selection to datagridview .
Posted
Updated 29-Sep-15 23:29pm
v3

Try like below:
C#
private void button1_Click(object sender, EventArgs e)
{
	List<string> oranad = new List<string>() ;
	StringBuilder sb = new StringBuilder(string.Empty);
	
	foreach (ListItem item in checkedListBox1.Items)
	{		
		if (item.Selected)
		 {
			sb.Append(item.Value + ",");
		 }			
	}
	
	cn.Open();
	string select = "select * from productvw where firstname in(@firstname)";

	SqlDataAdapter dataAdapter = new SqlDataAdapter(select, cn);
   
	SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
	dataAdapter.SelectCommand.Parameters.Add("@firstname",sb.ToString().TrimEnd(','));
	DataTable ds = new DataTable();
	dataAdapter.Fill(ds);
	dataGridView1.ReadOnly = true;
	dataGridView1.DataSource = ds;
	cn.Close();			
}

Here it is looping over all selected items and generating names in StringBuilder. Instead of executing the SQL Connection, I used once only.
   
v3
Comments
Richard Deeming 30-Sep-15 10:51am
   
If the intention is to retrieve records with any of the selected names, that won't work. It will only return records where the firstname column is exactly equal to the comma-separated list of selected names.

To return records with any of the selected names, you either need to use multiple parameters, or split the parameter string within the SQL query.
jame01 30-Sep-15 15:54pm
   
thanks guys for your answer its really appreciated a lot..
dear ManasKumarM
i tried with your code but it give this error
in [if (item.Select)]
("cannot convert method group to non-delegate type bool did you
intend to invoke method")
and it give error in [ sb.Append(item.Value + ",");]
saying ("string does not contain definition for value ")
[no name] 30-Sep-15 21:37pm
   
Updated answer. Please check again...
If you want to retrieve the records where the firstname column is equal to any of the selected values, then you need to pass multiple parameters to the query.

There are various ways to pass multiple values[^] to a single parameter. Alternatively, you could use multiple parameters:
C#
private void button1_Click(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE"))
    using (SqlCommand command = new SqlCommand(string.Empty, connection))
    {
        StringBuilder query = new StringBuilder("SELECT * FROM productvw");
        foreach (ListItem item in checkedListBox1.Items)
        {
            if (item.Selected)
            {
                if (command.Parameters.Count == 0)
                {
                    query.Append("WHERE firstname IN (");
                }
                else
                {
                    query.Append(", ");
                }
                
                string name = "@p" + command.Parameters.Count;
                command.Parameters.AddWithValue(name, item.Value);
                query.Append(name);
            }
        }
        if (command.Parameters.Count != 0)
        {
            query.Append(')');
        }
        
        command.CommandText = query.ToString();
        
        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
        DataTable ds = new DataTable();
        dataAdapter.Fill(ds);
        
        dataGridView1.ReadOnly = true;
        dataGridView1.DataSource = ds;
    }
}
   
Comments
jame01 30-Sep-15 16:02pm
   
mr.Richard Deeming thanks for you answer
but i couldn't understand this statement
foreach ((ListItem)//this ListItem is present what ?
sorry that i didn't get it
Richard Deeming 30-Sep-15 16:05pm
   
It's a basic foreach loop:
foreach, in (C# Reference)[^]

If you don't understand the basics of the language, then maybe you need to start with a simpler project?
jame01 30-Sep-15 16:13pm
   
hello,
i can understand foreach and i know it take string and int ,
but i didn't understand what is "ListItem" ...
and your answer is helped me to get new idea so thanks
hi every one ...
so thanks for you answer guys you really helped and made me learn new thing thanks
after trying and trying
founded the solution
C#
private void button1_Click(object sender, EventArgs e)
        {
        
            using (SqlCommand command = new SqlCommand(string.Empty,cn))
            {
                bool first = true;
                StringBuilder query = new StringBuilder("SELECT * FROM productvw ");
                foreach (string item in checkedListBox1.CheckedItems)
                {
                    if (first)
                    {
                        if (command.Parameters.Count == 0)
                        {
                            query.Append("WHERE firstname IN (");
                        }
                        else
                        {
                            query.Append(", ");
                        }

                        string name = "@p" + command.Parameters.Count;
                        command.Parameters.AddWithValue(name, item.ToString());
                        query.Append(name);
                    }
                }
                if (command.Parameters.Count != 0)
                {
                    query.Append(')');
                }

                command.CommandText = query.ToString();

                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable ds = new DataTable();
                dataAdapter.Fill(ds);

                dataGridView1.ReadOnly = true;
                dataGridView1.DataSource = ds;
            }
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900