Unable to do consecutive database inserts

Question

I'm attempting to do 2 SQL inserts consecutively to 2 different tables.

The first table insert works fine. It uses SELECT SCOPE_IDENTITY() to pull the index number which I then store in the variable Registree_Index. This works OK.

I then try to insert Registree_Index and some other variables into a second table. This does not write to the second table at all. No error message either.

Initially I thought the error had something to do with reusing my old query and connection string variables, so I created new ones. This has not helped.

Does anyone have thoughts on this? Code follows...

private void WriteToDatabase()
{
    Guid newGuid = Guid.NewGuid();

    string yearstring = DateTime.Now.Year.ToString();
    string twodigityear = yearstring.Substring(yearstring.Length-2);
    string dateAndGuid = twodigityear + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + "-"  + DateTime.Now.Hour.ToString() + "-"  + DateTime.Now.Minute.ToString() + "-"  + DateTime.Now.Second.ToString() + "-" + newGuid;

    string connectionString = GetConnectionString();

    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = connectionString;
    connection.Open();

    string insertQuery = "INSERT INTO registrees (UIDindex, Submission_Number, Homecoming_Form, HC_form, NewRecord, First_Name, Last_Name, Billing_Phone, Addresses_Same, Email) VALUES (@UIDindex, @Submission_Number, @Homecoming_Form, @HC_form, @NewRecord, @First_Name, @Last_Name, @Billing_Phone, @Addresses_Same, @Email)               SELECT SCOPE_IDENTITY()";

    SqlCommand cmd = new SqlCommand(insertQuery, connection);

    cmd.Parameters.AddWithValue("@UIDindex", dateAndGuid);
    cmd.Parameters.AddWithValue("@Submission_Number", 1);
    cmd.Parameters.AddWithValue("@Homecoming_Form", 1);
    cmd.Parameters.AddWithValue("@HC_form", "platform");
    cmd.Parameters.AddWithValue("@NewRecord", 1);

    cmd.Parameters.AddWithValue("@First_Name", First_Name.Text);
    cmd.Parameters.AddWithValue("@Last_Name", Last_Name.Text);

    cmd.Parameters.AddWithValue("@Billing_Phone", Phone.Text);
    cmd.Parameters.AddWithValue("@Addresses_Same", 1); 
    cmd.Parameters.AddWithValue("@Email", Email.Text);

    ///get index from scope identity
    int Registree_Index = Convert.ToInt32(cmd.ExecuteScalar());



    ///SO FAR EVERYTHING WORKS GREAT! BUT THE REST OF THIS CODE FAILS SOMEHOW.
    connection.Close();
    connection = null;
    insertQuery = null;
    cmd = null;

    string connectionString2 = GetConnectionString();
    SqlConnection connection2 = new SqlConnection();
    connection2.ConnectionString = connectionString2;
    connection2.Open();

    string insertQuery2 = "INSERT INTO event_registration (Registree_Index, UIDindex, Submission_Number) VALUES (@Registree_Index, @UIDindex, @Submission_Number)";

    SqlCommand cmd2 = new SqlCommand(insertQuery2, connection2);

    cmd2.Parameters.AddWithValue("@Registree_Index", Registree_Index);
    cmd2.Parameters.AddWithValue("@UIDindex", dateAndGuid);
    cmd2.Parameters.AddWithValue("@Submission_Number", 1);

}

Answer 1

How about this...

private void WriteToDatabase()
    {
        Guid newGuid = Guid.NewGuid();

        string yearstring = DateTime.Now.Year.ToString();
        string twodigityear = yearstring.Substring(yearstring.Length - 2);
        string dateAndGuid = twodigityear + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Hour.ToString() + "-" + DateTime.Now.Minute.ToString() + "-" + DateTime.Now.Second.ToString() + "-" + newGuid;
        int Registree_Index;

        using (SqlConnection connection = new SqlConnection(GetConnectionString()))
        {
            connection.Open();

            string insertQuery = "INSERT INTO registrees (UIDindex, Submission_Number, Homecoming_Form, HC_form, NewRecord, First_Name, Last_Name, Billing_Phone, Addresses_Same, Email) VALUES (@UIDindex, @Submission_Number, @Homecoming_Form, @HC_form, @NewRecord, @First_Name, @Last_Name, @Billing_Phone, @Addresses_Same, @Email)               SELECT SCOPE_IDENTITY()";

            using (SqlCommand cmd = new SqlCommand(insertQuery, connection))
            {

                cmd.Parameters.AddWithValue("@UIDindex", dateAndGuid);
                cmd.Parameters.AddWithValue("@Submission_Number", 1);
                cmd.Parameters.AddWithValue("@Homecoming_Form", 1);
                cmd.Parameters.AddWithValue("@HC_form", "platform");
                cmd.Parameters.AddWithValue("@NewRecord", 1);

                cmd.Parameters.AddWithValue("@First_Name", First_Name.Text);
                cmd.Parameters.AddWithValue("@Last_Name", Last_Name.Text);

                cmd.Parameters.AddWithValue("@Billing_Phone", Phone.Text);
                cmd.Parameters.AddWithValue("@Addresses_Same", 1);
                cmd.Parameters.AddWithValue("@Email", Email.Text);

                ///get index from scope identity
                Registree_Index = Convert.ToInt32(cmd.ExecuteScalar());
            }

            string insertQuery2 = "INSERT INTO event_registration (Registree_Index, UIDindex, Submission_Number) VALUES (@Registree_Index, @UIDindex, @Submission_Number)";

            using (SqlCommand cmd = new SqlCommand(insertQuery2, connection))
            {
                cmd.Parameters.AddWithValue("@Registree_Index", Registree_Index);
                cmd.Parameters.AddWithValue("@UIDindex", dateAndGuid);
                cmd.Parameters.AddWithValue("@Submission_Number", 1);

                cmd.ExecuteNonQuery();
            }
        }

    }