how retrieve data from checkedlistbox selected and show data in datagrideview?

mahmoud mousaa

New member
Joined
Aug 28, 2020
Messages
1
Programming Experience
5-10
I use these code to retrieve data from checkedlistbox selected and show data in datagrideview

problem query retrieve data one checkedlistbox selected not multi checkedlistbox selected I want to retrieve data in datagrideview with multi checkedlistbox selected

C#:
string s = "";
int x;
for (x = 0; x <= checkedListBox1.CheckedItems.Count - 1; x++)
{

    s = "'" + checkedListBox1.CheckedItems[x].ToString() + "',";

}
s = s.Substring(0, s.Length - 1);




con = new SqlConnection(cs.DBConn);
con.Open();


cmd = new SqlCommand("select id,'',raqam,name_sender,country1,country2,tel1,tel2,title,detalis,n_number,price,ship,total,name from pos where country2 IN("+s+") order by id asc ", con);

rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

bunifuCustomDataGrid1.Rows.Clear();
while (rdr.Read() == true)
{


    bunifuCustomDataGrid1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7], rdr[8], rdr[9], rdr[10], rdr[11], rdr[12], rdr[13], rdr[14]);
    bunifuCustomDataGrid1.Rows.OfType<DataGridViewRow>().Last().Selected = true;
    bunifuCustomDataGrid1.CurrentCell = bunifuCustomDataGrid1.Rows[bunifuCustomDataGrid1.Rows.Count - 1].Cells[1];

}
con.Close();

Mod Edit, please don't bold your text, it's an eyesore.
 
Last edited by a moderator:

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
Welcome to the forums.

Firstly learn to use parameterised queries and don't concatenate your commands query string.

You can find a link in my signature to using parameterised queries.

I'd also recommend you shorten your code usage by removing a lot of that code and make it inline code instead.

Use of using blocks on your connections, commands and readers would also be a valued change; doing so will also close and dispose of the resources used by those objects.

I also prefer to check if reader.HasRows first, before committing to reading. And it wouldn't hurt to wrap your code in try catch blocks to catch any possible errors which may occur.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,409
Location
Chesapeake, VA
Programming Experience
10+
I would also recommend learning how to bind to the DataSource property instead of adding the rows to the [il]DataGridView[/il] programmatically.

Marking the selected row/cell should really be done outside the loop. In fact, if you use data binding, you won't even have a loop (for adding the rows).
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,448
Location
Sydney, Australia
Programming Experience
10+
I don't usually do this but the others have already explained most of what's required and I posted most of this code elsewhere a long time ago, so I'm happy to just post code without much explanation. You would need to change some of the names a little but this code demonstrates the principle of building a dynamic filter:
C#:
using (var connection = new SqlConnection("connection string here"))
using (var command = new SqlCommand { Connection = connection })
using (var adapter = new SqlDataAdapter(command))
{
    var query = new StringBuilder("SELECT * FROM MyTable");

    switch (checkedListBox1.CheckedItems.Count)
    {
        case 0:
            // No filter criteria.
            break;
        case 1:
            // A single filter criterion.
            query.Append(" WHERE MyColumn = @MyColumn");
            command.Parameters.AddWithValue("@MyColumn", checkedListBox1.CheckedItems[0]);

            break;
        default:
            // Multiple filter criteria.
            query.Append(" WHERE MyColumn IN (");

            for (var i = 0; i < checkedListBox1.CheckedItems.Count; i++)
            {
                var paramName = "@MyColumn" + i;

                if (i > 0)
                {
                    query.Append(", ");
                }

                query.Append(paramName);
                command.Parameters.AddWithValue(paramName, checkedListBox1.CheckedItems[i]);
            }

            query.Append(")");

            break;
    }

    command.CommandText = query.ToString();

    var table = new DataTable();

    // Retrieve the data.
    adapter.Fill(table);

    // Bind the data to the grid.
    bindingSource1.DataSource = table;
    dataGridView1.DataSource = bindingSource1;
}
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,448
Location
Sydney, Australia
Programming Experience
10+
I also prefer to check if reader.HasRows first, before committing to reading.
Personally, given that the first call to Read will return the same value as HasRows, I only test HasRows under two specific circumstances:
  1. I care only whether there is data and not what that data is.
  2. I want to do something specific if there is no data.
For instance, if I was going to read the data to populate a grid, I would generally just use while (myDataReader.Read()). If I wanted to provide a specific notification when there was no data though, then I would use this:
C#:
if (myDataReader.HasRows)
{
    while (myDataReader.Read())
    {
        // ...
    }
}
else
{
    // Notify user of no data.
}
That makes for neater code. That said, Read does do more work internally than HasRows, so it is less efficient. The difference is negligible in most cases though, and I tend to prioritise readability/maintainability over performance in such cases. The readability difference is fairly small too though, so I wouldn't castigate someone for using HasRows too harshly. ;)
 

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
You raise good points. However, I personally prefer to call HasRows as it does less work under-the-hood than actually committing to read itself. Actually your code example is why i said :
I also prefer to check if reader.HasRows first, before committing to reading.
It was kinda what I was hinting to do.
 

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
I never actually blindly read, without actually checking HasRows. It's kinda how I always wrote my reading blocks as you've demonstrated.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,448
Location
Sydney, Australia
Programming Experience
10+
I never actually blindly read, without actually checking HasRows. It's kinda how I always wrote my reading blocks as you've demonstrated.
I guess the question is 'why" though. If there is an else block then I would do it that way too, as I suggested. If there isn't, why get HasRows when Read will produce the same result? You are saving the work that Read does if there is no data, sure, but that takes milliseconds. Do we always try to save milliseconds that way, even if it means more complex code? For me, the answer is "no". I go with the most readable code unless the performance difference is significant. That said, one extra if is not exactly a big increase in complexity in the code either, so I certainly wouldn't consider suggesting that your approach is wrong.
 

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
one extra if is not exactly a big increase in complexity in the code either
Indeed it's not.
If there is an else block then I would do it that way too, as I suggested.
Maybe you don't pay attention to how I write a lot of my conditional logic. But I consider myself overzealous with conditional logic at times as that's just the way I'm wired to write statements. And I generally go the avenue of covering all eventualities when dealing with conditional logic. So unless I was not returning the condition of no data been found. I would likely just read. But, I; nine times out of ten, never do that because it doesn't make sense to me to not have an else condition on HasRows. :unsure:
 

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,877
Location
UK
Programming Experience
10+
Don't take that first statement as me being repugnant. What I meant by that, was; If you ever watch when I write if conditional logic, I am often meticulous to all eventualities, and I often try to cover all possibilities, relevant or not. Some people are wired to write like that. I consider myself to be one of those people. :)

I'd never use HasRows without an alternate condition.
 
Last edited:
Top Bottom