Loop datatable and select checkboxlist item based on the datatable field values

[beemomo]

I am trying to display value of the field ("UserID") for every row exists in datatable to checkboxlist(ma ke the checkboxlist item selected).
I used for loop, but only the field value from last row of RoleUsers table is selected in the checkboxlist.

Here is my code

   Private Sub DisplayRoleUser()
	Dim conn As SqlConnection
	Dim cmd As SqlCommand 
        Dim drDataReader As SqlDataReader
        Dim dtDataTable As New DataTable

  
        conn = New SqlConnection("Data Source=(local);Initial Catalog=BHPTrackingSystem;User ID=sa;Password=Safetec2011")
        conn.Open()
        cmd = New SqlCommand("SELECT UserID ,RoleID FROM RoleUsers WHERE RoleID=@RID ", conn)
        cmd.Parameters.Add("@RID", SqlDbType.Int)
        cmd.Parameters("@RID").Value = lstRole.SelectedValue

        drDataReader = cmd.ExecuteReader()
        dtDataTable.Load(drDataReader)
        Dim i As Integer
        If dtDataTable.Rows.Count > 0 Then
            For i = 0 To dtDataTable.Rows.Count - 1
                chkUser.SelectedValue = dtDataTable.Rows(i).Item("UserID")
            Next
        End If

        cmd.Dispose()
        conn.Close()

Can you guys please tell me where do i get wrong? Your help is much appreciated. THank you very much !

[snehasismishra1]

Hi Beemomo,

Please find the below code. I guess it will help you.

DataTable table = new DataTable();
table.Columns.A dd("Dosage", typeof(int));
table.Columns.A dd("Drug", typeof(string)) ;
table.Columns.A dd("Patient", typeof(string)) ;
table.Columns.A dd("Date", typeof(DateTime ));

table.Rows.Add( 25, "Indocin", "David", DateTime.Now);
table.Rows.Add( 50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add( 10, "Hydralazin e", "Christoff" , DateTime.Now);
table.Rows.Add( 21, "Combivent" , "Janet", DateTime.Now);
table.Rows.Add( 100, "Dilantin", "Melanie", DateTime.Now);

CheckBoxList1.R epeatColumns = 1;
CheckBoxList1.D ataSource = table;
CheckBoxList1.D ataTextField = "Drug";

CheckBoxList1.D ataBind();

If you have any concern please let me know.

Thanks,
Snehasis

[beemomo]

Thank you for your reply, Snehasis.
I have been away from workstation during last weekend. Sorry for my late reply.

I got my solution for the above post by replacing the for loop using the code below:

For i = 0 To dtDataTable.Rows.Count - 1
  chkUser.Items.FindByValue(dtDataTable.Rows(i).Item  ("UserID")).Selected = True
Next

I appreciate your help alot! Thanks a again!

Regards,
Beemomo

[snehasismishra1]

You are Most Welcome...:)