Insert after transaction

Question

Hello.  I would like it so that if I forget to add info to database at time of insertion, I can go back and make the proper selection say in a combo box or text box and inert into the table. How can I do this? Any help would be greatly appreciated. Here's my code:

 private void btnAddRecord_Click(object sender, EventArgs e)
        {
            using (TransactionScope trnScope = new TransactionScope())
            {

                using (SqlConnection connection = new SqlConnection(getConnectionString()))
                {
                    //SqlConnection connection = new SqlConnection(getConnectionString());
                    // SqlTransaction transation;
                    int ArtistID;
                    int AlbumID;
                    int ClassicalID;
                    int CreditsID;
                    int GenreID;
                    int TracksID;

                    connection.Open();
                    //transation = connection.BeginTransaction();

                   try
                    {
                        //----Check if the provided Artist is already exist or not. If exist the use the existing key
                        //----ArtistExist returns follwoing
                        //----ArtistID=-1 if artist does not exist
                        //----ArtistID=value if artist exist
                        ArtistID = ArtistExist(connection, txtArtist.Text.Trim());

                        //----check if artist does not exist then Insert this new Artist in DB
                        if (ArtistID == -1)
                            ArtistID = InsertArtist(connection);

                        //----Insert the album belongs to the selected Artist
                        AlbumID = InsertAlbum(connection, ArtistID);

                        //----Insert the Classical belongs to the selected Album
                        ClassicalID = InsertClassical(connection, AlbumID);

                        //----Insert the Credits belongs to the selected Album
                        CreditsID = InsertCredits(connection, AlbumID);

                        //----Insert the Genre belongs to the selected Album
                        GenreID = InsertGenres(connection, AlbumID);

                        //----Insert the Track belongs to the selected Album
                        TracksID = InsertTracks(connection, AlbumID);

                        if (ClassicalID != -1 && AlbumID != -1 && ClassicalID != -1 && CreditsID != -1 && GenreID != -1 && TracksID != -1)
                        {
                            trnScope.Complete();
                            // transation.Commit();
                            MessageBox.Show("Album record inserted Successfully","Message", MessageBoxButtons.OK ,MessageBoxIcon.Exclamation);
                           
                        }
                        else
                            MessageBox.Show("Error Occured while inserting Album record.");
                     
                    }
                    catch (Exception ex)
                    {
                        // transation.Rollback();
                        
                        MessageBox.Show("Error Occured while inserting Album record.");
                        MessageBox.Show(ex.Message, "Actual Error");
                    }
                    finally
                    {
                        //transation = null;
                        connection.Close();
                    }
                }
            }
        }

        
        

        public int ArtistExist(SqlConnection connection, string ArtistName)
        {
            SqlParameter param = null;
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;

            int ArtistID = -1;

            command.Connection = connection;
            command.CommandText = "SELECT ArtistID FROM Artists WHERE (ArtistName = @ArtistName)";
            command.CommandType = CommandType.Text;

            param = new SqlParameter("@ArtistName", SqlDbType.VarChar, 50);
            param.Value = ArtistName;
            command.Parameters.Add(param);

            reader = command.ExecuteReader();

            if (reader.HasRows)
                if (reader.Read())
                    ArtistID = Convert.ToInt32(reader[0]);

            reader.Close();

            return ArtistID;

        }

        public int InsertArtist(SqlConnection connection)
        {
            SqlParameter param = null;
            SqlCommand command = new SqlCommand();
            int ArtistID = -1;

            command.Connection = connection;
            command.CommandText = "INSERT INTO [Artists] ([ArtistName], [ArtistInfo]) VALUES " +
                                    " (@ArtistName, @ArtistInfo); " +
                                    " Select SCOPE_IDENTITY() as SCOPE_IDENTITY; ";

            command.CommandType = CommandType.Text;
            command.Parameters.Clear();
            param = new SqlParameter("@ArtistName", SqlDbType.VarChar, 50);
            param.Value = txtArtist.Text.Trim();
            command.Parameters.Add(param);

            param = new SqlParameter("@ArtistInfo", SqlDbType.VarChar, 15);
            if (cboArtistInfo.SelectedItem != null)
                param.Value = cboArtistInfo.SelectedItem.ToString();
            else
                param.Value = DBNull.Value;
            command.Parameters.Add(param);

            ArtistID = Convert.ToInt32(command.ExecuteScalar());

            return ArtistID;

---

PCRider

Answer 1

Hi,

If I understood your question correctly, you are trying to insert the data in different tables based of some selection in combobox or text box. For that you just need to create a dynamic INSERT statement, or create a stored procedure which takes the name of the table assuming the fields in all the tables are same.

-Mohammed Ghouse Ibne Barq Kadapavi
http://www.ProgrammersVision.blogspot.com
https://sites.google.com/site/BarqKadapavi
Please mark the post answered your question as the answer, and mark other helpful posts as helpful.

Answer 2

What I want to do is say you added a female artist to the database and forgot to select female in the artist info combo box. I want it so that the user can bring up the form again or just select female from combo box resubmit and have it inserted into correct field in the artists table. It tells me it can't insert into a field. The same for the other tables.

---

PCRider

Answer 3

Hi,

Thank for clarifying.

In this case we need to put a Client side validation for our pages (using JScript or JavaScript). We should not submit the page until user select some of value in Artist ComboBox. This is recommended way, because just for letting the user know that he forgot to enter the Artist field we should not have a round trip till our Database server.

Hope this helps.

-Mohammed Ghouse Ibne Barq Kadapavi
http://www.ProgrammersVision.blogspot.com
https://sites.google.com/site/BarqKadapavi
Please mark the post answered your question as the answer, and mark other helpful posts as helpful.

Answer 4

Hi.

 

Figured it out. Thanks

 

 

PCRider