示例#1
0
        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);
            }
        }