我有两个表,称为付款和约会,使用约会ID相互关联。对于我当前的代码,它只会在付款表中插入值。我想要实现的是,如果我以包括约会ID的形式键入值,然后单击“提交”,那么我刚刚输入的约会ID还将把该约会表aStatus的记录更新为已完成或正在等待。对于我的aStatus组合框,我在等待它并在item属性中将其填充。当前,我的代码只能插入付款表中。aStatus在另一个表即约会表中。
这是我插入的aStatus下拉列表代码。我希望它更新约会表思想的状态。那么,如何通过一个按钮将此代码与我的底部代码结合在一起?此代码将更新约会表中的aStatus,底部代码将在付款表中插入值。
string value = cbaStatus.SelectedItem == null ? "waiting" : cbaStatus.SelectedItem.ToString();
updateCmd.Parameters.AddWithValue("@cbaStatus", value);
我的表格
我的表和关系
遵循史蒂夫代码时出错
private void btnSubmit_Click(object sender, EventArgs e)
{
int result = AddPaymentRecord();
if (result > 0)
{
MessageBox.Show("Insert Successful");
txtamount.Clear();
txtamountPaid.Clear();
txtappointmentID.Clear();
txtamount.Focus();
}
else
{
MessageBox.Show("Insert Fail");
txtamount.Clear();
txtamountPaid.Clear();
txtappointmentID.Clear();
txtamount.Focus();
}
}
private int AddPaymentRecord()
{
int result = 0;
string strConnectionString = ConfigurationManager.ConnectionStrings["sacpConnection"].ConnectionString;
SqlConnection myConnect = new SqlConnection(strConnectionString);
String strCommandText = "INSERT PAYMENT(amount, amountPaid, paymentDate, paymentType, appointmentID) "
+ " VALUES (@Newamount, @NewamountPaid,@NewpaymentDate, @NewpaymentType, @NewappointmentID)";
SqlCommand updateCmd = new SqlCommand(strCommandText, myConnect);
updateCmd.Parameters.AddWithValue("@Newamount", txtamount.Text);
updateCmd.Parameters.AddWithValue("@NewamountPaid", txtamountPaid.Text);
updateCmd.Parameters.AddWithValue("@NewpaymentDate", dtppaymentDate.Value);
if (rbCash.Checked)
updateCmd.Parameters.AddWithValue("@NewpaymentType", "Cash");
else
updateCmd.Parameters.AddWithValue("@NewpaymentType", "Credit Card");
updateCmd.Parameters.AddWithValue("@NewappointmentID", txtappointmentID.Text);
myConnect.Open();
result = updateCmd.ExecuteNonQuery();
myConnect.Close();
return result;
}
您有两个选择,将参数传递给存储过程,该存储过程将记录插入付款表并更新约会表,或者从代码中执行两个命令。
无论哪种方式,您都需要提供交易记录来避免存储付款记录(如果发生错误)并且无法更新适当的约会记录。
让我们尝试一下代码版本(请进行测试,因为我已经在此处动态编写了代码)
private int AddPaymentRecord()
{
int result = 0;
// The command text contains two statements separated by a semicolon
String strCommandText = @"INSERT PAYMENT(amount, amountPaid, paymentDate,
paymentType, appointmentID) VALUES (@Newamount,
@NewamountPaid,@NewpaymentDate,@NewpaymentType,
@NewappointmentID);
UPDATE Appointment SET aStatus=@cbaStatus
WHERE appointmentID = @NewappointmentID";
string strConnectionString = ConfigurationManager.ConnectionStrings["sacpConnection"].ConnectionString;
using(SqlConnection myConnect = new SqlConnection(strConnectionString))
{
myConnect.Open();
// Start a transaction to be sure that the two commands are both executed
SqlTransaction tran = myConnect.BeginTransaction();
try
{
using(SqlCommand updateCmd = new SqlCommand(strCommandText, myConnect, tran))
{
updateCmd.Parameters.AddWithValue("@Newamount", txtamount.Text);
updateCmd.Parameters.AddWithValue("@NewamountPaid", txtamountPaid.Text);
updateCmd.Parameters.AddWithValue("@NewpaymentDate", dtppaymentDate.Value);
if (rbCash.Checked)
updateCmd.Parameters.AddWithValue("@NewpaymentType", "Cash");
else
updateCmd.Parameters.AddWithValue("@NewpaymentType", "Credit Card");
updateCmd.Parameters.AddWithValue("@NewappointmentID", txtappointmentID.Text);
string value = cbaStatus.SelectedItem == null ?
"waiting" : cbaStatus.SelectedItem.ToString();
// Add also the parameter required by the second batch statement
updateCmd.Parameters.AddWithValue("@cbaStatus", value);
result = updateCmd.ExecuteNonQuery();
// If we reach this point we have updated both records.
// Commit the changes
tran.Commit();
}
return result;
}
catch
{
// Something wrong. rollback any changes and rethrow the exception
// let the caller code handle this exception.
tran.Rollback();
throw;
}
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句