Home » Asp.netRSS

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;
con.Open();
cmd.ExecuteNonQUery();
con.Close();


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;
cmd.Parameters.Add("@author"SqlDbType.NVarChar);
con.Open();
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;
        cmd.ExecuteNonQuery();
    }
}
con.Close();


 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter