Parameterized SQL Query in MS Access

OleDbConnection Connection = new OleDbConnection(loan.pConnectionString);
            OleDbCommand cmdCommand = new OleDbCommand(); cmdCommand.Connection = Connection;
            OleDbTransaction transaction = null;
            try
            {
                Connection.Open();
                transaction = Connection.BeginTransaction();
                cmdCommand.Transaction = transaction;

                String sqlInsert = “INSERT INTO Loan(DebtorId,DebtorName,LoanAmount,OpeningBalance,Installment,PaidAmount,EntryDate, Remarks) VALUES(@DebtorId,@DebtorName,@LoanAmount,@OpeningBalance,@Installment,@PaidAmount, @EntryDate, @Remarks)”;

                cmdCommand.CommandText = sqlInsert;
                cmdCommand.Parameters.AddWithValue(“@DebtorId”, loan.pDebtorId);
                cmdCommand.Parameters.AddWithValue(“@DebtorName”,loan.pDebtorName);
                cmdCommand.Parameters.AddWithValue(“@LoanAmount”,loan.pLoanAmount);
                cmdCommand.Parameters.AddWithValue(“@OpeningBalance”,loan.pLoanAmount);
                cmdCommand.Parameters.AddWithValue(“@Installment”,loan.pInstallment);
                cmdCommand.Parameters.AddWithValue(“@PaidAmount”,0);
                cmdCommand.Parameters.AddWithValue(“@EntryDate”, loan.pEntryDate);
                cmdCommand.Parameters.AddWithValue(“@Remarks”,loan.pRemarks);
                cmdCommand.ExecuteNonQuery();

                cmdCommand.CommandText = “SELECT @@IDENTITY”;
                loan.pLoanId = Convert.ToInt32(cmdCommand.ExecuteScalar());

                transaction.Commit();
                Connection.Close();
   
                loan.MarkOld();
                return loan;
            }
            catch (ExceptionWithoutControl WithoutControlException) { if (transaction != null) { transaction.Rollback(); } throw WithoutControlException; }
            catch (ExceptionWithControl WithControlException) { if (transaction != null) { transaction.Rollback(); } throw WithControlException; }
            catch (Exception ex)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw ex;
            }

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s