checkedlistbox save checked items in sql (not concatenate)

[AngelicOne]

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

I have a checkedlistbox 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.

[mshauny]

We'll i'm not quite sure about your ID, do you want ID's to be the same also? because It's unique. isnt?

[hyperion]

I am completely unable to understand what "new" you are looking for here?
As I see, you already have all your code in place.
Book Id is the only thing that you might want to take care of, if it is a unique key.
For that, you can normalise the tables as follows:

"Books" Table:
Id as Int and Name as Varchar(400)

"Authors" Table:
Id as Int and Name as Varchar(100)

"BookAuthorsMap" Table:
BookId as Int and AuthorId as Int

[AngelicOne]

I thought it will look like that especially it has the same book title

@hyperion,

I am only able to get one checked item even it's more than one.

[hyperion]

Please the check the following link on help with CheckedListBox Class

for (int i=0; i<authorcbl.Items.Count; i++)
    {
        if (authorcbl.GetItemChecked(i) == true)
        {
            //Here we know that item "i" is checked in checked list box.
        }
    }

[AngelicOne]

Then what would I do inside the if statement? Could you give some sample? Sorry but i'm really new to this checkedlistbox

[AngelicOne]

really need some help.

[AngelicOne]

Tried this but getting an error failed to convert parameter value from a string to a int64.

for (int i = 0; i < authorclb.Items.Count; i++)
                {
                    string query = "insert into Book_Author (Book_ID, Author_ID) values(@@identity, @check)";
                    cmd = new SqlCommand(query, con);
                    cmd.Parameters.Add("@check", SqlDbType.BigInt).Value = authorclb.CheckedItems.ToString();
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }

[AngelicOne]

Tried this but getting an error failed to convert parameter value from a string to a int64.

for (int i = 0; i < authorclb.Items.Count; i++)
                {
                    string query = "insert into Book_Author (Book_ID, Author_ID) values(@@identity, @check)";
                    cmd = new SqlCommand(query, con);
                    cmd.Parameters.Add("@check", SqlDbType.BigInt).Value = authorclb.CheckedItems.ToString();
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }

[hyperion]

for (int i=0; i<authorcbl.Items.Count; i++)
{
	con.Open();
	if (authorcbl.GetItemChecked(i) == true)
	{
		string query = "insert into Book_Author (Book_ID, Author_ID) values(@@identity, @check)";
		cmd = new SqlCommand(query, con);
		cmd.Parameters.Add("@check", SqlDbType.BigInt).Value = authorclb.Items[i];
		cmd.ExecuteNonQuery();
	}
	con.Close();
}

This solution largely depends upon what all items were added to authorclb checkedlistbox.
You might want to play around with authorclb properties to find actual values.

[AngelicOne]

ok i'll try that. I'm wrong about the bookauthor id because it contains the same values as it is a primary key and identity field.