Checkedlistbox values, how do we get them into a database?

[simpleonline]

Hey guys I've never really attempted to do this so I search around a bit but haven't really found anything useful on this topic.

I have a checkedlistbox with about 1000 checkedboxes inside. The user will select which checkedboxes they wish to have assigned to their profile and hit the save button.

What I'm trying to do is to get those values saved into the database. I understand that the type for checkedlistedboxes are boolean so the value would be true or false and the database value would be "bit?" which is 0 = false and 1 = true.

Let's say that one of the checkedboxes is called "Dinner" and it's going to be checked.

How do I enter this true value into my database so when I open that profile down the road I can see that they chose to have Dinner as one of their options?

Thanks

 

[Menthos]

How I personally do this is assign a numeric value to each 'option' then have a binary field on the user record that uses simple bitwise logic to set the bit that corresponds to the 'option' - at runtime it's a reverse of this process to check if a user has access to that 'option'.

The only downside to this is that you have to maintain a lookup of the various options and their corresponding values, plus it's a pain to add an option in between others - not impossible, just a pain.

 

[AndrewdAzotus]

Although I am new to Visual Studio and VB, I had to do something similar with a list box populated from a database. I wanted to be able to sort the display in the list box but maintain the Primary Key index with the displayed fields. If this doesn't make sense then:

Table: People
Idx - Int Primary Key
FirstName - VarChar(25)
LastName - VarChar(25)

then I wanted to display the names in the listbox sorted by either first or last name as required by the user.

When the user made their choice, the way I did it was to load the records from the table into a SortedList keyed by with 'First Last' or 'Last, First' with Idx as the second field.

then load the SortedList into the List box on the form.

you could use a similar method for your checkedlistboxes which:

a) makes maintaining the list easier - just add a new one on the end.
b) allows the user to dynamically update the list (if that's ok with the program, programmer or designer - or you could add this as a restricted function to only administrators)
c) means that the location of the bit in the bit string is easily determined since the Idx will be the pointer into the bit string.

something like:
Table CheckListOptions
Idx - Int Primary Key
Description - VarChar(25)

then
dim CheckList as SortedList (Of String, Int)

then
Checklist.add(Description, Idx)

get the idea?

 

[AndrewdAzotus]

Oh, and I've only done this with a list of about 20 table rows so far, so I have no idea how performance will be once I get to a row count of thousands or tens of thousands...