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();
            }
        }

    }

Answer 2

I don't see anywhere you are calling cmd2.ExecuteScalar()

Answer 3

That's because you never execute cmd2. Since insertQuery2 is only inserting to event_registration table, you can execute cmd2 by calling cmd2.ExecuteNonQuery().

On a side note, you should consider to use using statement to make sure that connection and connection2 are closed after the queries are executed. Below is the modified code with using statement

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();

    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()";
    int Registree_Index = 0;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        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);

            connection.Open();

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

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

    using (SqlConnection connection2 = new SqlConnection(connectionString2))
    {
        using (SqlCommand cmd2 = new SqlCommand(insertQuery2, connection2))
        {
            cmd2.Parameters.AddWithValue("@Registree_Index", Registree_Index);
            cmd2.Parameters.AddWithValue("@UIDindex", dateAndGuid);
            cmd2.Parameters.AddWithValue("@Submission_Number", 1);

            connection2.Open();

            cmd2.ExecuteNonQuery();
        }
    }
}