无法连续插入数据库

Question

我正试图对两个不同的表连续执行两次SQL插入。

第一个表插入操作正常。它使用SELECT SCOPE_IDENTITY()来提取索引号，然后将其存储在变量Registree_Index中。这还可以。

然后，我尝试将Registree_Index和其他一些变量插入到第二个表中。这根本不会写入第二个表。也没有错误消息。

起初，我认为这个错误与重用旧的查询和连接字符串变量有关，所以我创建了新的。这也于事无补。

有人对此有想法吗？代码如下。。。

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

我看不到您在任何地方调用cmd2.ExecuteScalar()

Answer 2

这个怎么样。。。

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 3

这是因为您从未执行过cmd2。由于insertQuery2只是插入到event_registration表中，所以可以通过调用cmd2.ExecuteNonQuery()来执行cmd2。

附带说明一下，您应该考虑使用using语句来确保connection和connection2在执行查询后关闭。以下是使用语句修改后的代码

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