saving multiple checkedlistbox values in one single row database using c#

Question

Is it possible to store multiple values in one cell in database? Based on the picture below on column famcon, only one value from a checkedlistbox can be inserted even if the user chose multiple values.

this is my query for this table:

foreach (DataRowView item in this.checkedListBox1.CheckedItems)
            {
                NpgsqlCommand cmd = new NpgsqlCommand("Insert into name (eid, name, famcon) SELECT @eid, @name, @famcon", conn);
                cmd.Parameters.AddWithValue("@eid", textBox1.Text);
                cmd.Parameters.AddWithValue("@name", textBox2.Text);
                string[] value = item.Row[0].ToString().Split(',');
                cmd.Parameters.AddWithValue("@famcon", value);

                cmd.ExecuteNonQuery();
            }

You have 2 solutions : **1)** store all CheckedListBox Checked values as a string (or an integer if the items count is under 32). **2)** create a secondary table to store one value per record with a foreign key refereing to the table pimary key (eid). — Graffito, Aug 04 '15 at 12:53
The SQL statement to get the AutoIncrement value allocated by the database server varies. What DB are you using ? — Graffito, Aug 04 '15 at 13:11
i tried your second solution, however i got stuck because the 'eid' value wont appear on the table. I tried the INSERT..SELECT query — newbie, Aug 04 '15 at 13:23
With Postgresql, refer to [this discussion](http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id) to get the eid following the INSERT. — Graffito, Aug 04 '15 at 13:30
Also replace ExecuteNonQuery() by ExecuteScalar() and cast result to int. — Graffito, Aug 04 '15 at 13:46

score 0 · Answer 1 · answered Aug 04 '15 at 12:51

0

you are not adding the elements of string[] value to your field. try:

string[] value = item.Row[0].ToString().Split(',');
string insert="";
foreach (string s in value)
{
  insert=" "+s;
}    
cmd.Parameters.AddWithValue("@famcon", insert);

answered Aug 04 '15 at 12:51

apomene

14,282
9
46
72

an error appears: duplicate key violates unique constraint "pk_eid". i forgot to mention that 'eid' is a primary key. sorry about that. – newbie Aug 04 '15 at 12:55
If your primary key is autoIncrement, don't include it in the INSERT command, but get it through a SQL statement depending on the DataBase (Oracle, MsAccess, MySQL, SQL Server, ...) – Graffito Aug 04 '15 at 13:02

score 0 · Answer 2 · answered Aug 04 '15 at 12:53

You could use a bitset type, which sets positional flags in a string.

Each bit would represent an individual value, but you would have to maintain the link between bits and values - ie which bit is which.

Alternatively a space or comma separated string works. Each string is a human-readable code that represents a value. You would have to parse the string but its easier to read than bit values.

saving multiple checkedlistbox values in one single row database using c#

2 Answers2