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

[mahmoud mousaa]

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

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.

 

[NoUserHere]

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]

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]

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:

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]

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:

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.

 

[NoUserHere]

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.

 

[NoUserHere]

I never actually blindly read, without actually checking HasRows. It's kinda how I always wrote my reading blocks as you've demonstrated.

 

[jmcilhinney]

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.

 

[NoUserHere]

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.

 

[NoUserHere]

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.