Member Avatar for 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.

Edited by AngelicOne because: n/a
  • 3 Contributors
  • 10 Replies
  • 260 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by AngelicOne

Dani AI

Generated

Short answer: use a normalized schema (Books, Authors, BookAuthors) and insert one mapping row per checked author. That keeps Book_ID as a single identity value and stores one Author_ID per row in the junction table — which is what was advising. The conversion error seen earlier comes from attempting to shove a collection or a display string into an integer parameter (for example, calling CheckedItems.ToString()).

Implementation outline and practical tips:

  • Insert the Book row first and retrieve its identity safely (SQL Server: SCOPE_IDENTITY() or OUTPUT INSERTED.Book_ID; MySQL: LAST_INSERT_ID()). Avoid @@IDENTITY.
  • Iterate the CheckedListBox.CheckedItems collection (not CheckedItems.ToString()); extract the Author_ID from each checked item. If the list is data-bound the items will usually be DataRowView (use drv["AuthorID"]) or a custom object (cast and read its Id property). Match the parameter type (Int32 vs Int64) to the column type — that fixes the "failed to convert parameter value" error.
  • Open the DB connection once, use a transaction, reuse a prepared command for the junction inserts, and call ExecuteNonQuery for each author. Don’t open/close the connection inside the loop.

Example pattern (C# / ADO.NET — adapted to the OP’s SqlCommand usage):

long bookId;
using (var conn = new SqlConnection(connString))
{
    conn.Open();
    using (var tx = conn.BeginTransaction())
    {
        // insert book and get id
        using (var cmd = new SqlCommand("INSERT INTO Book (Book_Title) VALUES (@t); SELECT CAST(SCOPE_IDENTITY() AS bigint);", conn, tx))
        {
            cmd.Parameters.Add("@t", SqlDbType.NVarChar).Value = bookTitle.Text;
            bookId = Convert.ToInt64(cmd.ExecuteScalar());
        }

        // prepare mapping insert once, then reuse
        using (var cmd = new SqlCommand("INSERT INTO Book_Author (Book_ID, Author_ID) VALUES (@b, @a)", conn, tx))
        {
            cmd.Parameters.Add("@b", SqlDbType.BigInt).Value = bookId;
            var p = cmd.Parameters.Add("@a", SqlDbType.BigInt);
            foreach (var item in authorClb.CheckedItems)
            {
                long authorId = (item is DataRowView drv) ? Convert.ToInt64(drv["AuthorID"]) : Convert.ToInt64(/*extract id from item*/);
                p.Value = authorId;
                cmd.ExecuteNonQuery();
            }
        }
        tx.Commit();
    }
}

Notes and troubleshooting:

  • Inspect item.GetType() in the debugger to know how to extract the ID.
  • If Book_ID is the primary key of Books, do not try to insert duplicate Book_ID values into that table; the mapping table stores repeated Book_IDs by design.
  • Credit to for the normalization suggestion and to for the ID question — the mapping-table approach answers both.

Recommended Answers

All 10 Replies

Member Avatar for 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?

Member Avatar for 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

Member Avatar for AngelicOne

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

,

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

Edited by AngelicOne because: n/a
Member Avatar for 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.
        }
    }
Edited by hyperion because: half cooked
Member Avatar for AngelicOne

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

Member Avatar for AngelicOne

really need some help.

Member Avatar for 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();
                }
Member Avatar for 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();
                }
Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.