How to get CheckedListBox values from DataBase?

Mayank Jani

Dear All,

Greetings of the day..

Please take a look at my problem. I want to get the CheckedListBox items Checked according to the database when I select an option from a DataGridView.

In a WinForm, I have a DataGridView contains User names (XYZ, ABC etc.) and a CheckedListBox contains different rights (Billing, Purchase, Stores, Edit, Delete etc.) to the user. a user (e.g. XYZ) rights saved (e.g. Purchase, Billing etc.) to MS Access database in a table...

UserName UserRights

XYZ Billing

XYZ Purchase

XYZ EditBills

Now, i want to see all the rights given to XYZ in the CheckedListBox when I select name XYZ from the DataGridView.

I got almost result but there is something missing that compares the db to CheckedListBox.

Please see the screen shots attached to get it more clearly. in this code (The Code.jpg) I only get first few items checked regardless, the actual rights.

Thank You.

Mayank Jani

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta8y ago
Accepted answer
You need to use 2 loops here, something like below, might you got the catch
for (int x=0; x< dt.Rows.Count; x++)
{
   var item = dt.Rows[x]["MenuName"].ToString();
   for (int y =0; y < checklistbox.Items.count;y++)
   {
       if(checklistbox.Items[y].Text == item)
        {
              checklistbox.Items[y]. Checked =true;
               break;
         }
   }
}
Change control naming accordingly.
+3
Prashanth Akula8y ago
Thank You
0
Mayank Jani8y ago
Hiii Amit,

Thank you so much once again....
you code works perfect. there are two minor changes that I have to done as I didn't get those options.

for (int x = 0; x < dsShowUserRights.Tables[0].Rows.Count; x++)
{
var item = dsShowUserRights.Tables[0].Rows[x]["MenuName"].ToString();
for (int y = 0; y < chklbxUserRights.Items.Count; y++)
{
if (chklbxUserRights.Items[y].ToString() == item)
{
chklbxUserRights.SetItemChecked(y, true);
break;
}
}

Thank You so much...

Mayank Jani.
+2
Mayank Jani8y ago
Hiii
Still waiting...
+1
Mayank Jani8y ago
Ok, let me describe the problem other way.

For Example...
in a winform, there is a DataGridView and a CheckedListBox and both got filled at the time of FormLoad. the DataGridView (dgv) bound with a table 'Players' and the CheckedListBox (chklist) bound with a table 'Sports'.

now I click on dgv to select player name like ABC and then I select some of the check boxes like Chess, Tennis, Football etc. and click on Save button to save the details. the details saved in a table called, 'Hobbies'. the table Hobbies keeps data of name of players and sports.

now, I want that when I re-open the form and click on ABC, I get the three items checked i.e. Chess, Tennis, Football.

please note that I do not want to populate the CheckedListBox from database.

this is all I can explain....

Thank You.

Mayank Jani.
+2
Mayank Jani8y ago
Hi Suraj,
thank you for your reply. your code is quite difficult to understand for me as, i think, i use simple or basic code yet.
please take a look at my code. i have added comments how i want to get it done.

private void dgvUsers_CellClick(object sender, DataGridViewCellEventArgs e)
{
//query for select user rights from the Access db. rights can be 5, 20, 25.
OleDbDataAdapter ShowUserRights = new OleDbDataAdapter("Select MenuName From UserRights Where UserName='" + dgvUsers.SelectedCells[0].Value + "'", MyConnDoc);

//suppose XYZ has 10 different menu rights, so this query will fetch 10 rows from the Access db.
DataSet dsShowUserRights = new DataSet();
ShowUserRights.Fill(dsShowUserRights);

if (dsShowUserRights.Tables[0].Rows.Count > 0)
{
//executing rows one by one.
for (int i = 0; i < dsShowUserRights.Tables[0].Rows.Count; i++)
{
//I get problem here, I want to compare the dataset rows with checkedlistitems as
//they are exactly same strings.
if(dsShowUserRights.Tables[0].Rows[0].ItemArray[0].Equals(chklbxUserRights.Items.ToString()))
{
//when match found, the checkedlistitem get checked
chklbxUserRights.SetItemChecked(i, true);
}
}
}

Dear, i save the rights in Access db with two fields both are Strings (text), User Name as it is from DataGridView selected cell and selected checked items from check list box.

Hope i described proper and enough.

Thank you for your patience

Mayank Jani.
+1
Suraj Kumar8y ago
You can declare your gridview as attached in source code...At the time of select in sql the value should 1 from checked and 0 for not checked or having no right.
+3