Home » Asp.net

checkboxlist save check items to SQL (not concatenate)

I search through the web and I only found out how to concatenate.

I have a checkboxlist that is a list of Authors, it is included in my ADD BOOK form. Take note that a book can have many author so I need checkboxlist.

Instead of having this in my SQL table.

Table 1: Book

Book_ID      Book_Title       Book_Author

1                 My Book          Mark, Mike, John  <-- Concatenate

I need this.

Table 1: Book

Book_ID      Book_Title        Book_Author

1                 My Book            Mark

1                 My Book            Mike

1                 My Book            John

It's required that for one row there's only one author but it can have the same book id and title.

My current code

string add = "insert into Book values (@booktitle, @author);
SqlCommand cmd = new SqlCommand(add, con);
cmd.Parameters.Add("@booktitle", SqlDbType.NVarChar).Value = booktitle.Text;
cmd.Parameters.Add("@author", SqlDbType.NVarChar).Value = authorcbl.SelectedItem;

I already remove the code for concatenate as I do not need it. I really need some help.


1 Answer Found


Answer 1

You can use a loop to get all checkbox selected items  and insert row  by row

SqlCommand cmd = new SqlCommand("insert into Book values (@booktitle, @author)", con);
cmd.Parameters.Add("@booktitle"SqlDbType.NVarChar).Value = booktitle.Text;
foreach (ListItem item in authorcbl.Items)
    if (item.Selected)
        //Item value or text
        //item.Text depending on your requirement
        cmd.Parameters["@author"].Value = item.Value;



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure