Insert multiple checkbox selections into one database column

Question

I have an asp.net form with C# code behind and on the form I have a CheckBoxList. Ideally users can use this CheckBoxList to select multiple items that apply to them and have all the selections inserted into a single column in an SQL database, with each selection separated by commas. Below is my current code for the CheckBoxList INSERT but when I query the SQL table on SSMS, the CheckBoxList column shows simply as 'NULL'.

Pyramid is the name of the database table. CheckBoxListFruits is the name of the CheckBoxList control, Favorite Fruits is the name of the column I'm trying to insert the information into.

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = "INSERT INTO Pyramid set IsSelected = @IsSelected" + " WHERE Favorite Fruits=@Fruits";
    foreach (ListItem item in CheckBoxListFruits.Items)
    {
        cmd.Parameters.AddWithValue("@IsSelected", item.Selected);
        cmd.Parameters.AddWithValue("@Fruits", item.Value);
    }
}

What is wrong with my code and how can I fix it?

Did you debug your code? Are you sure your values ar not null? You don't need + in your CommandText as well. — Soner Gönül
– Soner Gönül, Commented Oct 29, 2014 at 18:39
"all the selections inserted into a single column in an SQL database, with each selection separated by commas." Please, for the love all things, do not do this...READ ABOUT NORMALIZATION!!!! — mituw16
– mituw16, Commented Oct 29, 2014 at 18:40
Specifically, read about 1st Normal Form. You don't have to memorize the normal forms, but recognizing the principles behind them is incredibly important. — mason
– mason, Commented Oct 29, 2014 at 18:56

Rahul · Accepted Answer · 2014-10-29 18:49:15Z

1

That's a wrong INSERT syntax. Proper INSERT command syntax is

INSERT INTO table_name(col1,col2,col3,...,coln) VALUES(val1,val2,val3,...,valn);

You mean to have a UPDATE command like below

UPDATE Pyramid set IsSelected = @IsSelected WHERE [Favorite Fruits] = @Fruits;

Few more points to note:

Column Favorite Fruits have space in it and so you should escape it using [ ].

No need of + in your command text as @SonerGonul mentioned.

@mituw16 have pointed something very important; don't store comma separated values in your column. That's not only against Normalization concept but also will put you in trouble when you want to select/compare data for that column.

edited Oct 29, 2014 at 18:49

answered Oct 29, 2014 at 18:39

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ernesto · Accepted Answer · 2014-10-29 18:56:53Z

1

I recommend to have a separate table for favorite fruits, not a column, then insert or remove accordingly. So a table for the user (userid,...), one for the fruits(id, name) and one for the ones the user likes like userfavoritefruits(userid, fruitid)

The syntax of your insert statement is wrong, but the problem you are getting yourself into is bigger than that with such a denormalized structure.

Good luck.

answered Oct 29, 2014 at 18:56

Ernesto

1,5921 gold badge15 silver badges33 bronze badges

1 Comment

SRQ Coder Over a year ago

Spot on. Ernesto builds on mituw16's comment above re normalization. SQL is supremely robust in selecting data from tables - but no where near as efficient when it comes to parsing arrayed data within a field.

Collectives™ on Stack Overflow

Insert multiple checkbox selections into one database column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related