How to insert value into a table and also update a value in another table using one button?

Question

I have two tables called payment and appointment with relationship with each other using appointmentid. For my current codes, it only insert value into payment table. What I would like to achieve is that if I key in values in the form including the appointmentid and then click submit, the appointmentid that i have just entered, will also updates the record of that appointment table aStatus either to completed or waiting. For my aStatus combobox, i populate it with waiting and completed in the item property.Currently my codes can only insert into the payment table. aStatus is in at another table which is appointment table.

This is my insert of dropdownlist code for aStatus. I want it to update aStatus of appointment table thought. So how do I combine this code with my bottom codes in one button? this code will update aStatus in appointment table and the codes at bottom will insert value at payment table.

string value = cbaStatus.SelectedItem == null ? "waiting" : cbaStatus.SelectedItem.ToString();
updateCmd.Parameters.AddWithValue("@cbaStatus", value);

My form

My tables and relationship

Error following steve codes

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

Answer 1

You have two options, pass the parameters to a Stored Procedure that insert the record in the payment table and updates the Appointement table, or execute the two commands from your code.

Either way you need to provide a Transaction to avoid storing the payment record (in case of errors) and fail to update the appropriate appointment record.

Let's try the code version (please test it because I have written here on the fly)

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