private void btnSubmit_Click(object sender, EventArgs e)
{
SqlConnection connection;
SqlCommand command;
SqlCommand command1;
SqlCommand command2, command3;
string sql = null;
SqlDataReader dataReader1;
SqlDataReader dataReader2;
string sql1 = null;
string sql2 = null;
int LoanBalance = 0;
sql = "insert into LoanRepayment(Amount, date) values(" + txtRepaidL.Text + "," + dtpOfRepay.Text + " WHERE memberNumber=" + maskedtxtMeN.Text + ")";
sql1 = "Select firstname, phoneNo from MemberDetails WHERE memberNumber=" + maskedtxtMeN.Text + "";
sql2 = "Select LoanBalance from LoanRepament WHERE memberNumber=" + maskedtxtMeN.Text + "";
string sql3 = "update LoanRepament set LoanBalance= " + LoanBalance + "WHERE memberNumber=" + maskedtxtMeN.Text + "";
connection = new SqlConnection(connectionString);
try
{
connection.Open();
command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
command1 = new SqlCommand(sql1, connection);
dataReader1 = command1.ExecuteReader();
command2 = new SqlCommand(sql2, connection);
dataReader2 = command1.ExecuteReader();
string number = null, message, firstName = null;
while (dataReader1.Read())
{
number = dataReader1.GetValue(0).ToString();
firstName = dataReader1.GetValue(2).ToString();
}
dataReader2 = command.ExecuteReader();
while (dataReader2.Read())
{
LoanBalance = dataReader2.GetInt32(0);
}
LoanBalance = LoanBalance - Convert.ToInt32(txtRepaidL.Text);
command3 = new SqlCommand(sql3, connection);
command3.ExecuteNonQuery();
message = "Dear" + firstName + ",Your loan repayment of Kshs." + txtRepaidL.Text + "" + "has been received. Your outstanding Loan Balance as at " + DateTime.Now.ToString("M - d - yyyy") + "was Kshs." + LoanBalance + "" + "Thank you";
SMS sm = new SMS(cboComPorts.Text);
sm.Opens();
sm.sendSMS(number, message);
command1 = new SqlCommand(sql1, connection);
dataReader1.Close();
dataReader2.Close();
command.Dispose();
command1.Dispose();
command2.Dispose();
connection.Close();
MessageBox.Show(" Records Succcessfully Saved.", "Member Savings", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Failed to Save the records" + ex.Message, "Member Savings", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
}
}