CheckBoxList selection from SQL in ASP.NET

Question

0.00/5 (No votes)

See more:

, +

Dear All,
I'm stuck with my asp.net webform using SQL. I have (let me simplify it for you) 2 tables:

table1 has 1 column, named (ID) and data type of 'int'. It has now 61 rows with values of 1...61. Anytime I want to increase the possible values, I have to add it in SQL and not in ASP.NET code. It's simple. Table1 looks like this now:

ID |
1 |
2 |
.
.
.
60 |
61 |

table2 has 2 columns, named (Customer and Code), both of them have data type of 'int'. I use only INSERT commands here, later will be described why.

I have a CheckBoxList, that has as many CheckBoxes as many rows has the table1 (in this case it is 61). I get these checkboxes via SqlDataReader. It is working, I can see all the 61 checkboxes.

The problematic part is now coming up:
I make an INSERT command into table2, I have to use any value between 1 and 61 in the Code column. So now I have these kind of data in table2:

Customer | Code
155 | 13
160 | 61
155 | 48

When I choose Customer 155 on my webform, I would like to show all the 61 CheckBoxes in the CheckBoxList but only those 2 CheckBoxes should be checked (13 and 48), the rest should be stay unchecked.

I didn't find any solution for this. I found only that one, when only those 2 CheckBoxes would be seen. But it is not good for me. I have to see the unchecked boxes for every customer I may choose.

Thanks for your help.

Attila

Posted 19-Aug-15 5:07am

DoomMaker79

Add a Solution

Comments

DoomMaker79 19-Aug-15 11:25am

Here's my C# part of querying the Table2:

string str = "SELECT Code from Table2 where Customer = '" + CustomerID.Text + "' GROUP BY Code";
cmd = new SqlCommand(str, myConnection);
dr = cmd.ExecuteReader();

while (dr.Read())
{
CheckBoxList.SelectedValue = dr["Code"].ToString();
}

dr.Close();

This results that only the last found value will be checked in the CheckBoxList, in this case the number 48 will be.

The GROUP part in the query is needed to exclude any possible errors if a Customer has more rows in the SQL with the same Code.

DoomMaker79 19-Aug-15 11:40am

And here's my C# part of showing as many CheckBoxes as many rows has the Table1

string str = "SELECT ID from Table1";
SqlCommand cmd = new SqlCommand(str, myConnection);
SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
ListItem newItem = new ListItem();
newItem.Text = dr[0].ToString();
CheckBoxList.Items.Add(newItem);
}

2 solutions

Solution 1

Make a query to the second table by passing the customer id selected.

So you will get two rows. Read the code values one by one by reader. Inside reader, use CheckBoxListId.FindByValue(code) to get the checkbox with that code. Then select it.

Posted 19-Aug-15 7:04am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

DoomMaker79 · Accepted Answer · 2015-08-19T23:03:00

Ok, thanks for your reply, it led me to the solution. It's very simple, I don't know why I couldn't resolve it earlier...

So, after querying Table2 I changed the while(dr.Read()) part to this:

C#

while (dr.Read())
            {
                ListItem li = hibakodCheckBoxList.Items.FindByValue(dr[0].ToString());
                if (li != null)
                {
                    li.Selected = true;
                }

            }