CODEWITHSUNDEEP
c#sqlasp.netsql-server
Highi
Highi

Reputation: 25

Insert multiple checkbox selections into one database column

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?

Upvotes: 0

Views: 1801

Answers (2)

Ernesto
Ernesto

Reputation: 1592

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.

Upvotes: 1

Rahul
Rahul

Reputation: 77846

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.

Upvotes: 1

Related Questions