Microsoft ASP .NET Professional Projects - Premier Press
.pdfSub RunSql(sql as string)
try
Dim mycommand2 As New OleDbCommand(sql,myConnection)
myConnection.Open()
myCommand2.ExecuteNonQuery()
myConnection.Close()
'turn off editing
Grid1.EditItemIndex = -1
Catch ex As OleDbException
' SQL error
Dim errItem As OleDbError
Dim errString As String
For Each errItem In ex.Errors
errString += ex.Message + "<br/>"
Next
Message.Text = "SQL Error.Details follow:<br/><br/>" & errString
Message.Style("color") = "red"
Catch myException as Exception
Response.Write("Exception: " + myException.ToString())
Message.Style("color") = "red"
End try
rebind
response.write(sql)
End Sub
End Class
Chapter 16: Transactions
Overview
The Personal Finance Manager maintains cash, bank, and credit card transactions. Each transaction will always have two effects: a debit entry and a credit entry. Debits and credits are applied according to some basic accounting rules. A detailed discussion of
these rules is beyond the scope of this book. However, I shall outline the rules that are relevant to creating transaction entries in the Personal Finance Manager. The Personal Finance Manager deals with two basic transactions—deposits and withdrawals. The rules for these are summarized in Table 16.1.
Table 16.1 Rules for Deposits and Withdrawals
Transaction |
|
Debit |
|
Credit |
|
|
|
|
|
Deposit |
|
Bank, |
|
Income |
|
|
Cash or |
|
(exam |
|
|
Credit |
|
ple: |
|
|
Card |
|
Salary |
|
|
|
|
or |
|
|
|
|
Interes |
|
|
|
|
t) |
|
|
|
|
|
Withdrawal |
|
Expense |
|
Bank, |
|
|
(exampl |
|
Cash |
|
|
e: Rent |
|
or |
|
|
or |
|
Credit |
|
|
Utilities) |
|
Card |
Inserting and Updating Transactions
Like the masters table, I have a procedure that inserts or updates a transaction's entry in the tr_header and the transactions table. This is the procedure p_trans, the listing of which is as follows:
Stored Procedure p_trans
create procedure p_trans @date datetime ,
@ref varchar(30) = NULL, @dr_amount money = 0, @cr_amount money =0, @posted_to integer,
@id char(3),
@doc_no integer = NULL, @narr varchar(150) = NULL
as /****************************************************************** Author: Hersh Bhasin
This procedure creates or modifies a transaction record.
Each transaction record will have a entry in tr_header and
two records (a debit and a credit record) in the tranasaction table. Usage:
To Insert a record:
call with a null doc_no to insert
example : exec p_trans @date="01/01/2001", @ref="test", @code_value = 1, @dr_amount = 10, @cr_amount=0, @posted_to = "Sales a/c" ,@id="RPT",@doc_no=Null
To modify a record:
call with an existing doc_no:
example : exec p_trans @date="01/01/2001", @ref="test", @code_value = 1, @dr_amount = 10, @cr_amount=0, @posted_to = "Sales a/c" ,@id="RPT",@doc_no=50
******************************************************************/
DECLARE @ll_doc integer DECLARE @ret integer DECLARE @code_value integer /*
Get the selected cash/bank account:
The user makes a selection from selection.aspx and tblselection is updated with the code_value */
Select @code_value = selection from tblSelection
BEGIN TRANSACTION
IF isnull(@doc_no,0) = 0 --INSERT—
BEGIN
—SafeGuard : Check if tranaction with same ref# exists. If so do not insert select @ret = count(*) from tr_header where ref = @ref
if @ret > 0 BEGIN
--raiserror (53000, 1,16) GOTO doerror
END
Select @ll_doc = isnull(max(doc_no),0)+1 from tr_header IF @@ERROR != 0
Begin
GOTO doerror
End
END
ELSE
------UPDATE ------------
BEGIN
SELECT @ll_doc = @doc_no
Delete from transactions where doc_no = @doc_no IF @@ERROR != 0
Begin
GOTO doerror
End
Delete from tr_header where doc_no = @doc_no IF @@ERROR != 0
Begin
GOTO doerror
End
END
BEGIN
INSERT INTO tr_header ( id, date,ref, doc_no ,narr) VALUES
(@id, isnull(@date,getdate()),@ref, @ll_doc, @narr) IF @@ERROR != 0
Begin
GOTO doerror
End
INSERT INTO transactions ( doc_no, dr_amount, cr_amount, code_value, sr_no,posted_to )
VALUES
( @ll_doc, isnull(@dr_amount,0), ISNULL(@cr_amount,0), @code_value, 1 ,@posted_to)
IF @@ERROR != 0
Begin
GOTO doerror
End
INSERT INTO transactions ( doc_no, dr_amount, cr_amount, code_value, sr_no, posted_to )
VALUES
( @ll_doc, ISNULL(@cr_amount,0),ISNULL(@dr_amount,0), @posted_to, 2 ,@code_value)
IF @@ERROR != 0
Begin
GOTO doerror
End
END
COMMIT TRANSACTION
SELECT 0
GOTO doreturn
doerror:
Rollback TRANSACTION
doreturn:
RETURN 0
SELECT -100
go
This procedure gets called from a DataGrid whenever a new transaction is added or an existing transaction is modified. To add (insert) a new record, you will pass an null document number (i.e. doc_no = null) to the stored procedure. In this case, the procedure selects the maximum doc_no, increments it by one and stores it in the variable @ll_doc. A tr_header record, having a doc_no equal to @ll_doc, is created with the passed parameters.
To modify (updat e) an existing transaction, you pass the doc_no of the transaction to be modified to the procedure. The procedure stores the passed document number to the variable @ll_doc. It then deletes the transactions with this doc_no because they will be re-created with the passed parameters.
You might wonder why we have to delete and then reinsert the records instead of using an update statement. The reason for this process is that there are triggers associated with this table, which updates the closing balance field in the masters table (I will discuss the triggers in the next section). If you modify the account to which the transaction is debited or credited (say you want to change the transaction account from Rent to Utilities), you will have to reinstate the Rent account closing balance to its state prior to the transaction. You will also have to update the closing balance of the Utilities account to reflect this transaction. This is simple to accomplish if you delete and reinsert the transaction. The delete trigger on the transactions table will reinstate the Rent account to its original value. The insert trigger on the transactions table will update the closing balance figure of the Utilities account with the transaction amount.
For both the insert and the update modes, the procedure then creates two equal and opposite transactions in the transactions table. The doc_no field for both the transaction records is the number stored in the variable @ll_doc. The two Master accounts affected are specified by the @code_value parameter and the @posted_to parameter. The first transaction is given a sn of 1, the second a sn of 2 (if you remember, the primary key of the transactions table is doc_no + sn. This procedure in effect creates two records that have the same doc_no but sequential sn's, thus creating two unique records). The dr_amount and the cr_amount in the first transaction are switched and made the cr_amount and the dr_amount in the second transaction. In this way, this procedure creates an equal debit and credit transaction.
Updating the Closing Balance Field in the Masters Table
I have an insert, update, and delete trigger on the transactions table. Each time a record is added, deleted, or updated, these triggers update the closing balance field of the appropriate account. The advantage of this technique is that we have the closing balances ready at any time and our reports can be compiled quickly. If I did not follow
this technique, each time I have to display a report, I would have to sum the debits and credits in the transactions table. In a large database, where I have to add hundreds of rows, my session would time out, and such an application would be impossible to implement. The triggers are as follows:
insert_mstr is an insert trigger on the table transactions. Its code is as follows: insert_mstr
CREATE TRIGGER insert_mstr ON transactions for insert as
Declare @sql varchar(200)
DECLARE @mtype char(1)
DECLARE @amount money
SELECT @mtype = masters.type
FROM masters, inserted
WHERE (masters.code_value = inserted.code_value )
SELECT *
into #temp
from inserted
If @mtype = 'A' or @mtype = 'E'
BEGIN
SELECT @amount = ISNULL(#temp.dr_amount,0) - ISNULL(#temp.cr_amount,0)
FROM #temp
END
ELSE
BEGIN
SELECT @amount = ISNULL(#temp.cr_amount,0) - ISNULL(#temp.dr_amount,0)
FROM #temp
END
UPDATE MASTERS
SET closing = closing + @amount
FROM masters, #temp WHERE ( masters.code_value = #temp.code_value )
update_mstr is an update trigger on the table transactions. Its code is as follows: update_mstr
CREATE TRIGGER update_mstr ON transactions for update as
Declare @sql varchar(200)
DECLARE @mtype char(1)
DECLARE @amount money
SELECT @mtype = masters.type
FROM masters, inserted
WHERE ( masters.code_value = inserted.code_value )
SELECT * into #temp from inserted
SELECT * into #t2 from deleted
If @mtype = 'A' or @mtype = 'E'
BEGIN
SELECT @amount = ISNULL(#temp.dr_amount,0)
-ISNULL(#temp.cr_amount,0)
-ISNULL(#t2.dr_amount,0) + isnull(#t2.cr_amount ,0) FROM #temp, #t2
WHERE #temp.code_value = #t2.code_value
END
ELSE
BEGIN
SELECT @amount = ISNULL(#temp.cr_amount,0)
-ISNULL(#temp.dr_amount,0)
-ISNULL(#t2.cr_amount,0) + isnull(#t2.dr_amount ,0) FROM #temp, #t2
WHERE #temp.code_value = #t2.code_value
END
UPDATE Masters
SET Closing = Closing + @amount
FROM masters, #temp WHERE ( masters.code_value = #temp.code_value )
delete_mstr is a delete trigger on the table transactions. Its code is as follows: delete_mstr
CREATE TRIGGER delete_mstr ON transactions for delete as
Declare @sql varchar(200)
DECLARE @mtype char(1)
DECLARE @mmonth char(3)
DECLARE @amount money
DECLARE @mstr_amount money
SELECT *
into #temp from deleted
UPDATE Masters
SET Closing = isnull(Closing,0)- (ISNULL(t.dr_amount,0)-ISNULL(t.cr_amount,0))
FROM masters m, #temp t
WHERE m.code_value = t.code_value
AND m.type in("A","E")
UPDATE Masters
SET Closing = isnull(Closing,0)- (ISNULL(t.cr_amount,0) - ISNULL(t.dr_amount,0)) FROM masters m, #temp t
WHERE m.code_value = t.code_value
AND m.type in("I","L")
Discussion on the Triggers
Microsoft SQL Server maintains an inserted and a deleted table that is used with triggers. An inserted table is a SQL Server table that holds the inserted values in case of an insert statement or the updated values in case of an update statement. A deleted table is a Microsoft SQL Server table that holds the original values in case of an update statement or the deleted value in case of a delete statement. These tables have the same fields as the table it references, which in this case is the transactions table. We can join the inserted or deleted table with any other table. This is the logic followed by the triggers on an insert, update, or delete.
§Each of these triggers looks at the type (that is, "A", "L", "I", "E").
§If the Masters type is a debit type (that is, A or E), the formula for the closing balance is:
Closing balance = dr_amount-cr_amount
§If the type is credit, the formula for the closing balance is: Closing balance = cr_amount - dr_amount
Table 16.2 summarizes the formula for the closing balance calculations used by these three triggers.
|
Table 16.2 Closing Balance Calculations |
|
|
|
||
|
|
|
|
|
|
|
|
Action |
|
Trigger |
|
Formula for updating Masters |
|
|
|
|
|
|
closing balance |
|
|
|
|
|
|
|
|
|
Insert |
|
insert_mstr |
|
For A, E: closing+ |
|
|
|
|
(inserted.dr_amount - |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
inserted.cr_amount) |
|
|
|
|
|
|
For I, L: closing+ |
|
|
|
|
|
|
(inserted.cr_amount - |
|
|
|
|
|
|
inserted.dr_amount) |
|
|
|
|
|
|
|
|
|
Update |
|
update_mstr |
|
A, E: |
|
|
|
|
closing+(inserted.dr_amo |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
unt - |
|
|
|
|
|
|
inserted.cr_amount) |
|
|
|
|
|
|
(deleted.dr_amount - |
|
|
|
|
|
|
deleted.cr_amount) |
|
|
|
|
|
|
I,L : |
|
|
|
|
|
|
closing+(inserted.cr_amo |
|
|
|
|
|
|
unt - |
|
|
|
|
|
|
|
|