OK - I'm looking for the best approach on how to do this.
I have a form page where the user can edit their "Profile" (data) which is
in SQL Server. It's your basic company information - address, phone
numbers, etc. I also have on the form a CheckListBox which is bound to
another table in SQL Server - let's assume it the USA States so there's 50
checkboxes dynamically written to the page. When the user "adds" their
data, they check all the states that their company operates in and submits
the form. Easy enough. They might check 1 state - they might check all 50.
It just depends. Then I have a function that saves all data to two tables.
The basic company data is saved in the Company table (duh!). The states
selections are saved in a many to many table which is designed like this:
ID | CompanyID | StateID
=======================
1 | 1 | 1
2 | 1 | 7
etc...
I simply loop thru all the the CheckBox items and if one is check, INSERT a
record and repeat.
For Each itmStates In chkStates.Items
If itmStates .Selected Then
strSQL = "INSERT INTO ........"
cmdInsert = New SqlCommand(strSQL, conSQLServer)
cmdInsert.ExecuteNonQuery()
cmdInsert = Nothing
End If
Next
This all works fine.
Now here's where it gets tricky. I've copied the form and started making
modifications to make it an EDIT form - where the user can EDIT their
existing data. When the form loads, I simply preload all the TextBoxes with
their data. But how can I do this for the section of the CheckBoxList which
is bound to a SqlDataReader? How can I determine if each and every state as
it's being created in the CheckBoxList if it exists for that user in the
Many-to-may table? Make sense?
After thinking about it, I'm assuming I need to create some kind of Function
to run a check to look for each state as it's being bound and "select" the
checkbox if it's found in the DataReader. Correct? Maybe?
I see that there's a OnDataBinding attribute for the CBL. Would I use this
to call a function?
Here's kind of what I'm picturing. Am I close? Thanks in advance for
anyone who reads this.
I actually did this a couple of years ago with classic ASP but it's pretty
much spaghetti code and I'm redoing it in .NET.
<asp:CheckBoxList id="chkStates" runat="server" OnDataBinding="<%#
MyFunction(CompanyID) %>" />
and then my pseudo logic...
MyFunction(intCompanyID As Integer) As Boolean(?) String? ListItem??
'Create DataReader from M-T-M table based upon Company ID
' SELECT * FROM TABLE WHERE COMPANYID = intCompanyID
'DO ...loop thru rows
' If current row is matches the current State checkbox, then set the
checkbox item to "selected" (how?)
' End If
' LOOP WHILE
End Function
