Expense Tracking System Sql Script

create database ExpenseTrack

use ExpenseTrack

create table ExpenseGroup(GroupId int primary key, GroupName varchar(40) unique)

insert into ExpenseGroup values (1, 'Food')
insert into ExpenseGroup values (2, 'Clothes')
insert into ExpenseGroup values (3, 'Travelling')

select * from ExpenseGroup

create table Expenses (ExpenseId int primary key,
GroupId int references ExpenseGroup(GroupId),
Expense varchar(40))

insert into Expenses values (1, 1, 'Vegetables')

insert into Expenses values (2, 1, 'Hotel')

insert into Expenses values (3, 2, 'Clothes')

insert into Expenses values (4, 3, 'Petrol')


create table ExpenseTrans (TransId int primary key,
ExpenseId int references Expenses(ExpenseId),
TransDate datetime, Amount numeric(8,2), Description varchar(200))

select * from Expenses

create procedure spGetExpenses as
select G.GroupName, E.Expense from ExpenseGroup G, Expenses E
where G.GroupId = E.GroupId
go

spGetExpenses

insert into ExpenseTrans values (1, 3, '14-Oct-09', 400, 'Bought shirt from Navigator')

insert into ExpenseTrans values (2, 2, '15-Oct-09', 40, 'Had two Masala Dosa from Megha Hotel Kacheripady')
insert into ExpenseTrans values (3, 4, '15-Oct-09', 100, 'Petrol for Rs. 100 from Vaduthal Pump')

select * from ExpenseTrans



create procedure spGetExpenseTrans as
select E.Expense, T.TransDate, T.Amount, T.Description
from Expenses E, ExpensTrans T where E.ExpenseId = T.ExpenseId
go

spGetExpenseTrans


'DbConnect


Imports System.Data.SqlClient
Public Class DbConnect
Public cnn As SqlConnection
Dim ds As New DataSet
Dim da As SqlDataAdapter

Public Sub Open()
cnn = New SqlConnection("Integrated security=sspi;Initial Catalog=ExpenseTrack")
cnn.Open()
End Sub
Public Function GetExpenses() As DataTable
Open()
da = New SqlDataAdapter("select * from Expenses", cnn)
da.Fill(ds, "Expense")
Return ds.Tables("Expense")
End Function
End Class

'frmTransDetial

Imports System.Data.SqlClient
Public Class Form1
Dim dbc As New DbConnect
Dim b As Boolean = False
Dim cmd As SqlCommand
Dim intMax As Integer
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cboExpense.DataSource = dbc.GetExpenses
cboExpense.DisplayMember = "Expense"
cboExpense.ValueMember = "ExpenseId"
b = True
cmd = New SqlCommand("select Max(TransId) from ExpenseTrans", dbc.cnn)
b = Int32.TryParse(cmd.ExecuteScalar().ToString(), intMax)
intMax += 1
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim strSql As String
strSql = "insert into ExpenseTrans values (" + intMax.ToString + ", "
strSql += cboExpense.SelectedValue.ToString + ", '"
strSql += dtpDate.Value.ToString("d").ToString + "', "
strSql += txtAmount.Text + ", '" + txtRemarks.Text + "')"
cmd = New SqlCommand(strSql, dbc.cnn)
cmd.ExecuteNonQuery()
End Sub
End Class


Windows Generated Code for Expense Transaction Form


Private Sub InitializeComponent()
Me.Label1 = New System.Windows.Forms.Label
Me.dtpDate = New System.Windows.Forms.DateTimePicker
Me.Label2 = New System.Windows.Forms.Label
Me.cboExpense = New System.Windows.Forms.ComboBox
Me.Label3 = New System.Windows.Forms.Label
Me.txtRemarks = New System.Windows.Forms.TextBox
Me.btnSave = New System.Windows.Forms.Button
Me.Label4 = New System.Windows.Forms.Label
Me.txtAmount = New System.Windows.Forms.TextBox
Me.SuspendLayout()
'
'Label1
'
Me.Label1.AutoSize = True
Me.Label1.Location = New System.Drawing.Point(38, 43)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(30, 13)
Me.Label1.TabIndex = 0
Me.Label1.Text = "Date"
'
'dtpDate
'
Me.dtpDate.CustomFormat = "dd MMM yyyy"
Me.dtpDate.Format = System.Windows.Forms.DateTimePickerFormat.Custom
Me.dtpDate.Location = New System.Drawing.Point(97, 36)
Me.dtpDate.Name = "dtpDate"
Me.dtpDate.Size = New System.Drawing.Size(140, 20)
Me.dtpDate.TabIndex = 1
'
'Label2
'
Me.Label2.AutoSize = True
Me.Label2.Location = New System.Drawing.Point(38, 96)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(48, 13)
Me.Label2.TabIndex = 2
Me.Label2.Text = "Expense"
'
'cboExpense
'
Me.cboExpense.FormattingEnabled = True
Me.cboExpense.Location = New System.Drawing.Point(97, 88)
Me.cboExpense.Name = "cboExpense"
Me.cboExpense.Size = New System.Drawing.Size(121, 21)
Me.cboExpense.TabIndex = 3
'
'Label3
'
Me.Label3.AutoSize = True
Me.Label3.Location = New System.Drawing.Point(38, 180)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(49, 13)
Me.Label3.TabIndex = 4
Me.Label3.Text = "Remarks"
'
'txtRemarks
'
Me.txtRemarks.Location = New System.Drawing.Point(97, 173)
Me.txtRemarks.Multiline = True
Me.txtRemarks.Name = "txtRemarks"
Me.txtRemarks.Size = New System.Drawing.Size(178, 48)
Me.txtRemarks.TabIndex = 5
'
'btnSave
'
Me.btnSave.Location = New System.Drawing.Point(97, 251)
Me.btnSave.Name = "btnSave"
Me.btnSave.Size = New System.Drawing.Size(75, 23)
Me.btnSave.TabIndex = 6
Me.btnSave.Text = "Save"
Me.btnSave.UseVisualStyleBackColor = True
'
'Label4
'
Me.Label4.AutoSize = True
Me.Label4.Location = New System.Drawing.Point(38, 141)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(43, 13)
Me.Label4.TabIndex = 7
Me.Label4.Text = "Amount"
'
'txtAmount
'
Me.txtAmount.Location = New System.Drawing.Point(97, 134)
Me.txtAmount.Name = "txtAmount"
Me.txtAmount.Size = New System.Drawing.Size(100, 20)
Me.txtAmount.TabIndex = 8
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(372, 321)
Me.Controls.Add(Me.txtAmount)
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.btnSave)
Me.Controls.Add(Me.txtRemarks)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.cboExpense)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.dtpDate)
Me.Controls.Add(Me.Label1)
Me.Name = "Form1"
Me.Text = "Expense Trans"
Me.ResumeLayout(False)
Me.PerformLayout()
End Sub
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents dtpDate As System.Windows.Forms.DateTimePicker
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents cboExpense As System.Windows.Forms.ComboBox
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents txtRemarks As System.Windows.Forms.TextBox
Friend WithEvents btnSave As System.Windows.Forms.Button
Friend WithEvents Label4 As System.Windows.Forms.Label
Friend WithEvents txtAmount As System.Windows.Forms.TextBox


Related Blog

Simple Contact Management System In C# and VB .Net