Microsoft ASP .NET Professional Projects - Premier Press

	Table 14.4 The Masters Table
	Column		Type		Length		Description
							Balance

The Transactions Header Table

The tr_header table records the header information for the transactions. This is information like date, narration, document number, and so on. The primary key of the tr_header is the document number (doc_no). Table 14.5 is the definition of the tr_header table.

	Table 14.5 The tr_header Table
	Column		Type		Length		Description
	Id		char		3		Voucher
							Type
							("Bank",
							"Sales",
							"Purchases"
							)
	date		datetime				Voucher
							Date
	doc_no		int				Primary Key
	narr		varchar		150		Narration
	ref		varchar		15		Reference

The Transactions Table

This transaction information will be stored in the transactions table. There will be a debit and a credit transaction entry for each deposit or withdrawal. The primary key of the transactions table is the document number and the serial number (doc_no + sn). Table 14.6 gives the description of the transactions table.

	Table 14.6 The Transactions Table
	Column			Type		Length		Description
	doc_no			integer				Primary Key
	sr_no			money				Primary Key
	code_value			integer		n/a		First
								Masters a/c
								Debited or
								Credited
	dr_amount			money		n/a		Debit
								Amount
	cr_amount			money		n/a		Credit
								Account
	posted_to			integer		n/a		Second
								Masters a/c
								Debited or
								Credited

The tr_header and the transactions tables are related on the doc_no field in each table. There exists a one-to-many relationship between the two tables.

TblSelection Table

The tblSelection table has a structure as shown in Table 14.7.

Table 14.7 The tblSelection Table

Column		Type		Length		Description
Selection		Varchar		50		Records
						user
						account
						selection

This table has a single column called selection. This table is used with the Transactions web form that will be developed in Chapter 16. It is used to record the account selection made by a user.

Figure 14.1 shows the database schema for this application.

Figure 14.1: The database schema for the Personal Finance Manager application.

Chapter 15: Chart of Accounts

In this chapter, I will build a web form that will be responsible for maintaining the masters table. The maintenance functionality will include procedures to list, add, modify, and delete master records.

Inserting and Updating Master Records

The logic for the insertion or deletion of records from the masters table is encapsulated in the stored procedure p_masters. All the fields of the masters table are passed to this procedure as input parameters. I introduced the p_masters stored procedure in Chapter 4, "Data Binding," and showed how it was called from either a DataGrid or a DataList.

The code_value is the primary key of the masters table. If a null code_value is passed to the procedure, it builds an insert SQL action query using the passed parameters. Otherwise it updates the masters table record which has the same code_value as the one passed to the stored procedure.

In the case of a new record creation, the stored procedure assigns an account type to the record. As explained in Chapter 14, "The Design of the Personal Finance Manager," this account type can be A,L,I, or E. This account type is determined by looking up the type column in the groups table. To look up this value, you can pass either the code of the group (the @code_category parameter) or the descriptive name of the group (the @group_name parameter) to the stored procedure. If the @group_name parameter is provided the group details are looked up as follows:

SELECT @grtype = type , @grCode_value = code_value from Groups

WHERE code_display = rtrim(@group_name)

If this parameter is not supplied, the code_category parameter should be supplied and this is used to look up the group details as follows:

SELECT @grtype = type , @grCode_value = code_value from Groups

WHERE code_value = @code_category

The following is the complete listing of p_masters:

Stored Procedure p_masters

create procedure p_masters

@code_value integer = null,

@code_display varchar(30),

@code_category integer = NULL ,

@type char(1)= NULL,

@opening money = 0 ,

@closing money =0,

@group_name varchar(30) = NULL

as

/*******************************************************************************

This procedures creates or updates a new master record. If a null

code_value is passed, a record is inserted else the record is updated. You can

pass either the code of the group or the descriptive name of the group.

Example passing the code of the group (604):

execute p_masters 1,' Petty Cash a/c' ,604, 'A',0,0 ,NULL

Example passing the name of the group(Cash a/c):

p_masters 1,'Petty Cash a/c' ,604, 'A',0,0,'Cash a/c'

*******************************************************************************/

DECLARE @flag integer

DECLARE @oldType as char(1)

DECLARE @grCode_value integer

DECLARE @grType as char(1)

IF isnull(@code_value,0) = 0

------------If code value = 0 then INSERT a new record ------------

BEGIN

IF Datalength(@group_name) > 1

----If the group_name is provided look up group

------details using the descriptive name Begin

--Get Group Details

SELECT @grtype = type ,

@grCode_value = code_value from Groups

WHERE code_display = rtrim(@group_name)

End

Else

---If a numeric code_value of the group is provided,

---look up the group details using it Begin

SELECT @grtype = type , @grCode_value = code_value from Groups

WHERE code_value = @code_category

End

Insert into masters(code_category,code_display,type,opening,closing) Values(@grCode_value ,@code_display,@grtype, isnull(@opening,0),isnull(@closing,0))

IF @@ERROR != 0

Begin

GOTO doerror

End

END

ELSE

------------UPDATE a record if a code_value is passed------------

BEGIN

Update masters

Set code_category = @code_category,

code_display = @code_display,

--type = @type, don't allow update of type

opening =@opening,

closing =@closing

Where code_value =@code_value

IF @@ERROR != 0

Begin

GOTO doerror

End

END

SELECT 0

GOTO doreturn

doerror:

Return - 100

doreturn:

RETURN 0

GO

The Masters Web Form

The Masters web form is the form that adds, updates, and deletes rows from the masters table. In Chapter 4, I built the Masters form, step by step, and my last step was

to build Masters2.aspx. I built a call to the stored procedure p_masters but did not actually execute it. Instead I wrote out the procedure call syntax to the screen. Masters2.aspx did not have any addition or deletion capabilities. In this section, I will enhance this form so that I can add, delete, and update records to the masters table. Figure 15.1 shows what the form looks like.

Figure 15.1: The Masters web form.

Figure 15.2 shows what it looks like in Add mode. Figure 15.3 shows what it looks like in Edit mode.

Figure 15.2: The Masters web form in the add mode allows creation of new records.

Figure 15.3: The Masters web form in the edit mode allows you to modify existing records.

I explained most of this script in Chapter 4 while discussing Masters2.aspx, so I will only describe the additions to this form here.

Update Logic

The Sub Grid1_Update handles the Update logic. This function is fired when the grid is activated in the Edit mode. In Chapter 4, I discussed building the SQL action query string that makes a call to the stored procedure p_masters.

Grid1_Update

Sub Grid1_Update(sender As Object, e As DataGridCommandEventArgs)

Dim sql As string

Dim code_display As String

Dim code_category As String

Dim type As String

Dim opening As String

Dim closing As String

Dim myTextBox As TextBox

'This is the key value : Retrieved from the DataKey, since it's a read only field

Dim code_value as string = Grid1.DataKeys.Item(E.Item.ItemIndex).ToString

myTextBox = E.Item.FindControl("edit_name")

code_display = mytextbox.text

myTextBox = E.Item.FindControl("edit_group")

code_category = mytextbox.text

myTextBox = E.Item.FindControl("edit_type")

type = mytextbox.text

myTextBox = E.Item.FindControl("edit_opening")

opening = mytextbox.text

myTextBox = E.Item.FindControl("edit_closing")

closing = mytextbox.text

'Now execute stored procedure

sql = "Execute p_masters " + code_value + ", '" + code_display + " ',"

sql = sql + code_category + ", '" + type +"' ," + opening + "," + closing

RunSql(sql)

End Sub

This SQL query string is passed on to the function RunSql that does the actual work of executing the SQL statement. Note that I extract the primary key (code_value) and pass it on to the procedure. The existence of a valid code_value tells the procedure to issue an update statement. If you pass it a null code_value, it will issue an insert statement.

Adding Records

Three textboxes and one button have been added to the form. These controls reside on a panel that has an id of AddPanel. In the aspx form, I have added HTML comments to show where the section begins and ends.

Insert Logic Is the Form

-----<!	------------insert row logic	>

<asp:Panel id="AddPanel" runat="server" Visible="false">

<table style="font: 8pt verdana">

<tr>

<td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">

Add a New Account:</td>

</tr>

<tr>

<td nowrap>Name: </td>

<td><asp:TextBox id="acode_display" runat="server" /></td>

<td>

<asp:RequiredFieldValidator runat="server"

controltovalidate=acode_display

errormessage="Name is required.">*

</asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td nowrap>Group: </td>

<td>

<asp:DropDownList DataTextField = "code_display"

DataValueField = "code_value" id="acode_category" runat="server" />

</td>

</tr>

<tr>

<td nowrap>Opening Value: </td>

<td><asp:TextBox id="aopening" value = "0" runat="server" /></td>

</tr>

<tr>

<td style="padding-top:15">

<asp:Button id="SubmitDetailsBtn" text="Submit"

onclick="add_Click" runat="server" />

</td>

</tr>

</table>

</asp:Panel>

<!	------------Insert Logic ends	-------->

A button having an id of AddShow displays a caption "Add Account" on the web form. Clicking this button fires the add_show Sub. This Sub simply sets the visible property of the panel to true. When the panel is visible, all the controls on the panel also become visible. At this point, all the textboxes are ready for accepting user input. The insert logic is handled by the function add_click. It builds a SQL query string by extracting the text properties of various textboxes. The following is the Sub:

The add_click Sub

Sub add_click(Source As Object, E As EventArgs)

Dim sql As string

If acode_display.text = "" or acode_category.SelectedItem.Text = "" then

response.write("Incomplete information")

exit sub

end if

SQL = "Execute p_masters @code_value=NULL,"

SQL = SQL + " @code_display = '" + acode_display.text + "' , @group_name = '"

SQL = SQL + acode_category.SelectedItem.Text + "' , @type = NULL ,"

SQL = SQL + " @opening =" + aopening.text + ", @closing = 0"

RunSql(sql)

'reset values

acode_display.text = ""

aopening.text = ""

hidePanel()

End Sub

Note that we are passing a NULL code_value to the procedure. This fires an insert statement. This SQL query string is passed on to the function RunSql, which does the actual work of executing the SQL statement.

Delete Mode

The Delete mode is activated when the user clicks on the delete link. I simply build a delete SQL action query and pass it on to the function RunSql that actually executes this query.

Sub Grid1_delete

Sub Grid1_delete(sender As Object, e As DataGridCommandEventArgs)

Dim code_value As string = Grid1.DataKeys.Item(E.Item.ItemIndex).ToString

Dim sql As string

sql = "Delete from masters where code_value = " + cstr(code_value)

RunSql(sql)

End Sub

The RunSql Function

This is a generic function that can execute a SQL action query. The SQL query is passed to it as a parameter. The Grid1_update Sub, the Add_click Sub, and the Grid1_delete Sub make use of this function to update, add, or delete a record. This function opens a database connection, executes the query using ExecuteNonQuery, and finally closes the connection. It catches OleDbExceptions and other exceptions and writes them out to the screen in red.

Sub RunSql