Is this possible or are there any ways for this?

I need to insert records in two tables using SQL. I believe that having two sqlcommands must have different name so if there would be two of it, how could I execute both.

This is my code

string insertbook = "insert into BooksTest values(@book)";
            SqlCommand cmd = new SqlCommand(insertbook, con);
            cmd.Parameters.Add("@book", SqlDbType.NVarChar).Value = textBox1.Text;

            for (int i = 0; i < checkedListBox1.Items.Count; i++)
            {
                if (checkedListBox1.GetItemChecked(i) == true)
                {
                    string insert = "insert into BATest select BooksTest.Book_ID, Author.Author_ID from BooksTest CROSS JOIN Author where Author.Author_Name = @check AND BooksTest.Book_ID = @@identity";
                    SqlCommand cmd1 = new SqlCommand(insert, con);
                    //string query = "insert into Author values(@check)";
                    //SqlCommand cmd1 = new SqlCommand(query, con);
                    cmd1.Parameters.Add("@check", SqlDbType.NVarChar).Value = checkedListBox1.Items[i];
                    con.Open();
                    cmd1.ExecuteNonQuery();
                    con.Close();
                }
            }
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

Execute one, make sure it worked, then execute the other. One problem you might have is if the first works and the second doesn't. That would place your database into an error state as some data would be missing. To deal with that you use the SqlTransaction class. It allows you to perform multiple add/delete/update statements then when you are all done, determine if they should be committed or not.

Hey, You can use roll back statement in your code.
Due to roll back if one statement failed then other statement not affect the database.

This article has been dead for over six months. Start a new discussion instead.