|
 Practical
Learning: Introducing Parameterized Statements
|
|
- Start Microsoft Visual Basic and create a new Windows Application named
WattsALoan2
- In the Solution Explorer, right-click Form1.vb and click Rename
- Type Central.vb and press Enter
- Double-click the middle of the form and implement the Load event as
follows:
Imports System.Data.SqlClient
Public Class Central
Friend Sub CreateDatabase()
Dim strWattsALoan As String = ""
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Integrated Security='SSPI';")
strWattsALoan = _
"IF EXISTS ( " & _
"SELECT name " & _
"FROM sys.databases " & _
"WHERE name = N'WattsALoan1' " & _
") " & _
"DROP DATABASE WattsALoan1; " & _
"CREATE DATABASE WattsALoan1"
Dim Command As SqlCommand = _
New SqlCommand(strWattsALoan, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A database named WattsALoan1 " & _
"has been created.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='WattsALoan1'; " & _
"Integrated Security='SSPI';")
strWattsALoan = _
"CREATE TABLE dbo.Employees( " & _
"EmployeeNumber nchar(10) NOT NULL, " & _
"FirstName nvarchar(20) NULL, " & _
"LastName nvarchar(10) NOT NULL, " & _
"FullName AS ((LastName + ', ') + FirstName), " & _
"Title nvarchar(100), " & _
"HourlySalary money, " & _
"Username nvarchar(20), " & _
"Password nvarchar(20), " & _
"CONSTRAINT PK_Employees " & _
" PRIMARY KEY(EmployeeNumber)); "
Dim Command As SqlCommand = _
New SqlCommand(strWattsALoan, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named Employees has been " & _
"created in the WattsALoan database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='WattsALoan1'; " & _
"Integrated Security='SSPI';")
strWattsALoan = _
"INSERT INTO dbo.Employees(EmployeeNumber, " & _
"FirstName, LastName, Title, HourlySalary) " & _
"VALUES('22740', 'Jeanne', 'Tryler', " & _
"'Accounts Manager', 22.24); " & _
_
"INSERT INTO dbo.Employees(EmployeeNumber, " & _
"FirstName, LastName, Title, HourlySalary) " & _
"VALUES('40952', 'Helene', 'Gustman', " & _
"'Accounts Representative', 14.55); " & _
_
"INSERT INTO dbo.Employees(EmployeeNumber, " & _
"FirstName, LastName, Title, HourlySalary) " & _
"VALUES('84615', 'Ernest', 'Thomas', " & _
"'Accounts Representative', 12.75);"
Dim Command As SqlCommand = _
New SqlCommand(strWattsALoan, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A few records have been added to the " & _
"Employees table of the WattsALoan database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='WattsALoan1'; " & _
"Integrated Security='SSPI';")
strWattsALoan = _
"CREATE TABLE dbo.Customers( " & _
"AccountNumber nchar(10) NOT NULL, " & _
"DateCreated nvarchar(50), " & _
"CustomerName nvarchar(50) NOT NULL, " & _
"BillingAddress nvarchar(100), " & _
"BillingCity nvarchar(50), " & _
"BillingState nvarchar(50), " & _
"BillingZIPCode nvarchar(10), " & _
"EmailAddress nvarchar(100), " & _
"CONSTRAINT PK_Customers " & _
" PRIMARY KEY(AccountNumber)); "
Dim Command As SqlCommand = _
New SqlCommand(strWattsALoan, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named Customers has been " & _
"added to the WattsALoan database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='WattsALoan1'; " & _
"Integrated Security='SSPI';")
strWattsALoan = _
"INSERT INTO Customers(AccountNumber, DateCreated, " & _
"CustomerName, BillingAddress, BillingCity, " & _
"BillingState, BillingZIPCode, EmailAddress) " & _
"VALUES('917394', '2/26/2004', 'Julius Ramse', " & _
"'927 Feuler Ave', 'Silver Spring', " & _
"'MD', '20904', 'ramses1990@netscape.net'); " & _
_
"INSERT INTO Customers(AccountNumber, DateCreated, " & _
"CustomerName, BillingAddress, BillingCity, " & _
"BillingState, BillingZIPCode) " & _
"VALUES('862864', '06/22/2006', 'Gertrude Vaillant', " & _
"'10055 Larsenic Rd', 'Takoma Park', " & _
"'MD', '20910'); " & _
_
"INSERT INTO Customers(AccountNumber, DateCreated, " & _
"CustomerName, BillingAddress, BillingCity, " & _
"BillingState, BillingZIPCode, EmailAddress) " & _
"VALUES('846864', '12/3/2004', 'James Barrouch', " & _
"'4204 Fallon Drive', 'Silver Spring', " & _
"'MD', '20906', 'barrouchj@hotmail.com'); " & _
_
"INSERT INTO Customers(AccountNumber, DateCreated, " & _
"CustomerName, BillingAddress, BillingCity, " & _
"BillingState, BillingZIPCode) " & _
"VALUES('248047', '08/02/2006', 'Christine Rougher', " & _
"'825 Manning Street', 'Alexandria', " & _
"'VA', '22231'); " & _
_
"INSERT INTO Customers(AccountNumber, DateCreated, " & _
"CustomerName, BillingAddress, BillingCity, " & _
"BillingState, BillingZIPCode, EmailAddress) " & _
"VALUES('131804', '10/08/2006', 'Patrick Heller', " & _
"'2480 Clarington Drive NW', 'Washington', " & _
"'DC', '20006', 'hellerp@yahooo.com');"
Dim Command As SqlCommand = _
New SqlCommand(strWattsALoan, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A few records have been added to the Customers " & _
"table of the WattsALoan database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='WattsALoan1'; " & _
"Integrated Security='SSPI';")
strWattsALoan = _
"CREATE TABLE dbo.LoanAllocations( " & _
"LoanNumber int identity(1000, 1) NOT NULL, " & _
"DatePrepared nvarchar(50), " & _
"EmployeeNumber nchar(10), " & _
"AccountNumber nchar(10), " & _
"LoanType nvarchar(50), " & _
"LoanAmount money NOT NULL, " & _
"InterestRate decimal(6,2) NOT NULL, " & _
"Periods decimal(6,2) NOT NULL, " & _
"InterestAmount money, " & _
"FutureValue money, " & _
"MonthlyPayment money, " & _
"Notes ntext, " & _
"CONSTRAINT PK_LoanAllocations " & _
" PRIMARY KEY(LoanNumber)); "
Dim Command As SqlCommand = _
New SqlCommand(strWattsALoan, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named LoanAllocations has been " & _
"added to the WattsALoan database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='WattsALoan1'; " & _
"Integrated Security='SSPI';")
strWattsALoan = _
"CREATE TABLE dbo.Payments( " & _
"PaymentID int identity(1000, 1) NOT NULL, " & _
"PaymentDate datetime NOT NULL, " & _
"EmployeeNumber nchar(10), " & _
"LoanNumber int NOT NULL " & _
" CONSTRAINT FK_LoanAllocations " & _
" FOREIGN KEY REFERENCES " & _
" LoanAllocations(LoanNumber), " & _
"PaymentAmount money NOT NULL, " & _
"Balance money, " & _
"Notes ntext, " & _
"CONSTRAINT PK_Payments " & _
" PRIMARY KEY(PaymentID));"
Dim Command As SqlCommand = _
New SqlCommand(strWattsALoan, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named Payments has been " & _
"added to the WattsALoan database.")
End Using
End Sub
Private Sub Central_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
CreateDatabase()
End Sub
End Class
|
- Execute the application
- Close the form and return to your programming environment
- In the Data Sources window, click Add New Data Source
- In the first page of the wizard, make sure Database is selected and
click Next
- If you see a WattsALoan1 connection in the combo box, select it.
Otherwise, use the New Connection... button and the Add Connection dialog
box to create a connection to the WattsALoan1 database
- Click Next
- Change the connection string to CstWattsALoan and click
Next
- In the list, click the check box of Tables
- Change the data set name to DsWattsALoan and click
Finish
- In the Server Explorer, expand the server.WattsALoan1.dbo connection
- Click the + button of Database Diagram
- When the message box comes up, read it and click Yes
- Right-click Database Diagram and click Add New Diagram...
- In the dialog box, double-click each table and, when all tables have been
added, click Close
- Create the relationships using the common fields
- Save the diagram as DgmWattsALoan and close it
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to Customers and click Add
- In the Data Sources window, drag Customers and drop it on the Customers form
- Under the form, click the objects and, using the Properties window,
change their names as follows:
| Object |
Name |
| customersBindingSource |
BsCustomers |
| customersTableAdapter |
TaCustomers |
| customersBindingNavigator |
BnCustomers |
- On the form, click the data grid view control and, in the
Properties window, change the following properties:
(Name): DgvCustomers
ColumnHeadersHeightSizeMode: EnableResizing
Anchor: Top, Bottom, Left, Right
- Click the ellipsis of the Columns field and make the
following changes:
| Selected Columns |
HeaderText |
Width |
| AccountNumber |
Acnt # |
50 |
| DateCreated |
Date Created |
80 |
| CustomerName |
Customer Name |
100 |
| BillingAddress |
Address |
120 |
| BillingCity |
City |
80 |
| BillingState |
State |
40 |
| BillingZIPCode |
ZIP Code |
60 |
| EmailAddress |
Email Address |
|
- Click OK
- Design the form as follows:
 |
| Control |
Text |
Name |
| Button |
Close |
btnClose |
|
- Double-click the Close button
- Implement its even as follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnCustomers
Text: Customers...
- Double-click the Account Types button and implement its event as
follows:
Private Sub Central_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
' CreateDatabase()
End Sub
Private Sub btnCustomers_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btnCustomers.Click
Dim Clients As Customers = New Customers
Clients.ShowDialog()
End Sub
|
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to Payments and click Add
- In the Data Sources window, drag Payments and drop it on the Payments form
- Under the form, click the objects and, using the Properties window,
change their names as follows:
| Object |
Name |
| customersBindingSource |
BsPayments |
| customersTableAdapter |
TaPayments |
| customersBindingNavigator |
BnPayments |
- On the form, click the data grid view control and, in the
Properties window, change the following properties:
(Name): DgvPayments
ColumnHeadersHeightSizeMode: EnableResizing
Anchor: Top, Bottom, Left, Right
- Click the ellipsis of the Columns field and make the
following changes:
| Selected Columns |
HeaderText |
Width |
| PaymentID |
Pmt ID |
45 |
| PaymentDate |
Pmt Date |
80 |
| EmployeeNumber |
Received By |
75 |
| LoanNumber |
Loan # |
60 |
| PaymentAmount |
Amount Paid |
75 |
| Balance |
|
60 |
| Notes |
|
|
- Click OK
- Design the form as follows:
 |
| Control |
Text |
Name |
| Label |
View Payments For (Enter Loan #): |
|
| TextBox |
|
TxtAccountNumber |
| Button |
Submit |
BtnSubmit |
| Button |
Close |
BtnClose |
|
- Double-click the Submit button and implement its event as follows:
Private Sub BtnSubmit_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnSubmit.Click
BsPayments.Filter = "LoanNumber = '" & TxtAccountNumber.Text & "'"
End Sub
|
- In the Class Name combo box, select BtnClose
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnClose_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnPayments
Text: View Payments...
- Double-click the View Payments... button and implement its event as
follows:
Private Sub btnPayments_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btnPayments.Click
Dim Pmts As Payments = New Payments
Pmts.ShowDialog()
End Sub
|
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to NewLoanAllocation and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| Label |
Date Prepared: |
|
|
| DateTimePicker |
|
DtpDatePrepared |
|
| Label |
Prepared By |
|
BackColor: Gray
AutoSize: False |
| Label |
Employee #: |
|
|
| MaskedTextBox |
|
TxtEmployeeNumber |
Mask: 00000 |
| TextBox |
|
TxtEmployeeName |
|
| Label |
Prepared For |
|
BackColor: Gray
AutoSize: False |
| Label |
Account #: |
|
|
| MaskedTextBox |
|
TxtAccountNumber |
Mask: 000000 |
| TextBox |
|
TxtCustomerName |
|
| Label |
Loan Preparation |
|
BackColor: Gray
AutoSize: False |
| Label |
Loan Type |
|
|
| ComboBox |
|
CbxLoanTypes |
Items:
Other
Car Loan
Credit Card
Personal Loan
Furniture Loan |
| Label |
Loan Amount: |
|
|
| TextBox |
0.00 |
TxtPresentValue |
TextAlign: Right |
| Label |
Interest Rate: |
|
|
| TextBox |
0.00 |
TxtInterestRae |
TextAlign: Right |
| Label |
% |
|
|
| Label |
Number of Months: |
|
|
| TextBox |
0 |
TxtMonths |
TextAlign: Right |
| Button |
Calculate |
BtnCalculate |
|
| Label |
Interest Amt: |
|
|
| TextBox |
0.00 |
TxtInterestAmount |
TextAlign: Right |
| Label |
Future Value: |
|
|
| TextBox |
0.00 |
TxtFutureValue |
TextAlign: Right |
| Label |
Monthly Payment: |
|
|
| TextBox |
0.00 |
TxtMonthlyPayment |
TextAlign: Right |
| Label |
Notes |
|
BackColor: Gray
AutoSize: False |
| TextBox |
|
TxtNotes |
Multiline: True
ScrollBars: Vertical |
| Button |
Submit |
BtnSubmit |
|
| Button |
Close |
BtnClose |
|
|
- Right-click the form and click View Code
- Just above the Public Class line, import the System.Data.SqlClient
Imports System.Data.SqlClient
Public Class NewLoanAllocation
End Class
|
- In the Class Name combo box, select TxtEmployeeNumber
- In the Method Name combo box, select Leave and implement the event as follows:
Imports System.Data.SqlClient
Public Class NewLoanAllocation
Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles TxtEmployeeNumber.Leave
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='WattsALoan1';" & _
"Integrated Security=SSPI;")
Dim strSelect As String = _
"SELECT FullName FROM Employees " & _
"WHERE EmployeeNumber = '" & _
TxtEmployeeNumber.Text & "';"
Dim Command As SqlCommand = _
New SqlCommand(strSelect, Connect)
Connect.Open()
Dim rdrWattsALoan As SqlDataReader = _
Command.ExecuteReader()
While rdrWattsALoan.Read()
txtEmployeeName.Text = rdrWattsALoan(0)
End While
End Using
End Sub
End Class
|
- In the Class Name combo box, select TxtAccountNumber
- In the Method Name combo box, select Leave and
implement the event as follows:
Private Sub TxtAccountNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles TxtAccountNumber.Leave
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='WattsALoan1';" & _
"Integrated Security=SSPI;")
Dim strSelect As String = _
"SELECT CustomerName FROM Customers " & _
"WHERE AccountNumber = '" & _
TxtAccountNumber.Text & "';"
Dim Command As SqlCommand = _
New SqlCommand(strSelect, Connect)
Connect.Open()
Dim rdrWattsALoan As SqlDataReader = _
Command.ExecuteReader()
While rdrWattsALoan.Read()
TxtCustomerName.Text = rdrWattsALoan(0)
End While
End Using
End Sub
|
- In the Class Name combo box, select BtnCalculate
- In the Method Name combo box, select Click and implement the event as follows:
Private Sub BtnCalculate_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnCalculate.Click
Dim Principal As Double = 0.0, InterestRate As Double = 0.0
Dim Periods As Double = 0.0, InterestAmount As Double = 0.0
Dim FutureValue As Double = 0.0, MonthlyPayment As Double = 0.0
Try
Principal = CDbl(TxtPresentValue.Text)
Catch ex As Exception
MsgBox("Invalid Principal Amount")
End Try
Try
InterestRate = CDbl(TxtInterestRate.Text)
Catch ex As Exception
MsgBox("Invalid Interest Rate")
End Try
Try
Periods = CDbl(TxtMonths.Text)
Catch ex As Exception
MsgBox("Invalid Number of Months")
End Try
InterestAmount = Principal * (InterestRate / 100) * Periods / 12
FutureValue = Principal + InterestAmount
MonthlyPayment = FutureValue / Periods
TxtInterestAmount.Text = FormatNumber(InterestAmount)
TxtFutureValue.Text = FormatNumber(FutureValue)
TxtMonthlyPayment.Text = FormatNumber(MonthlyPayment)
End Sub
|
- In the Class Name combo box, select BtnSubmit
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnSubmit_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnSubmit.Click
' Don't save the record if there is no employee number
If txtEmployeeName.Text.Length = 0 Then
MsgBox("You must enter a valid employee number.")
Exit Sub
End If
' Don't save the record if there is no account number
If TxtCustomerName.Text.Length = 0 Then
MsgBox("You must enter a valid account number.")
Exit Sub
End If
' Make sure the user has specified the type of loan
If CbxLoanTypes.Text.Length = 0 Then
MsgBox("You must specify the type of loan.")
Exit Sub
End If
' Don't save the record if there is principal
If TxtPresentValue.Text.Length = 0 Then
MsgBox("You must specify the amount " & _
"that is being lent.")
Exit Sub
End If
' Don't save the record if there is principal
If TxtInterestRate.Text.Length = 0 Then
MsgBox("You must specify the interest rate of the loan.")
Exit Sub
End If
' Don't save the record if there is principal
If TxtMonths.Text.Length = 0 Then
MsgBox("You must specify the number " & _
"of months as period of the loan.")
Exit Sub
End If
' Before saving the loan, just in case, perform the calculation
BtnCalculate_Click(sender, e)
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='WattsALoan1';" & _
"Integrated Security=SSPI;")
Dim strInsert As String = _
"INSERT INTO LoanAllocations( " & _
"DatePrepared, EmployeeNumber, AccountNumber, " & _
"LoanType, LoanAmount, InterestRate, Periods, " & _
"InterestAmount, FutureValue, MonthlyPayment, " & _
"Notes) VALUES('" & _
DtpDatePrepared.Value.ToString("d") & "', '" & _
TxtEmployeeNumber.Text & "', '" & _
TxtAccountNumber.Text & "', '" & _
CbxLoanTypes.Text & "', '" & _
TxtPresentValue.Text & "', '" & _
TxtInterestRate.Text & "', '" & _
TxtMonths.Text & "', '" & _
TxtInterestAmount.Text & "', '" & _
TxtFutureValue.Text & "', '" & _
TxtMonthlyPayment.Text & "', '" & _
TxtNotes.Text & "');"
Dim Command As SqlCommand = _
New SqlCommand(strInsert, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A new loan has been created.")
End Using
' Reset the form
DtpDatePrepared.Value = DateTime.Today
TxtEmployeeNumber.Text = ""
txtEmployeeName.Text = ""
TxtAccountNumber.Text = ""
TxtCustomerName.Text = ""
CbxLoanTypes.SelectedIndex = 0
TxtPresentValue.Text = "0.00"
TxtInterestRate.Text = "0.00"
TxtMonths.Text = "0"
TxtInterestAmount.Text = "0.00"
TxtFutureValue.Text = "0.00"
TxtMonthlyPayment.Text = "0.00"
TxtNotes.Text = ""
End Sub
|
- In the Class Name combo box, select BtnClose
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnClose_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnNewLoanAllocation
Text: New Loan Allocation...
- Double-click the Loan Allocations button and implement its event as
follows:
Private Sub btnNewLoanAllocation_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles btnNewLoanAllocation.Click
Dim Loan As NewLoanAllocation = New NewLoanAllocation
Loan.ShowDialog()
End Sub
|
- Execute the application and open the Loan Allocations form
- Create a few loans
- Close the forms and return to your programming environment
|
Practical Learning: Creating a Stored Procedure
|
|
- In the Solution Explorer, right-click Central.vb and click View Code
- Just above the Load event, create the following procedure and call it in
the Load event:
Friend Sub CurrentBalanceCalculator()
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='WattsALoan1'; " & _
"Integrated Security='SSPI';")
' Here is how we will evaluate the current balance of an account
' Get the amount that was lent to the customer
' If the customer had already made at least one payment,
' get the current balance of the customer's account
' If the customer has never made a payment (yet),
' to specify the balance, subtract the current payment
' from the original amount of the loan
' If the customer had already made at least one payment,
' subtract the current payment from the previous balance
Dim strWattsALoan As String = _
"CREATE PROCEDURE SpecifyCurrentBalance " & _
" @PmtDate datetime, " & _
" @EmplNbr nchar(10), " & _
" @LoanNbr int, " & _
" @PmtAmt money, " & _
" @Comments ntext = '' " & _
"AS " & _
"BEGIN " & _
" DECLARE @AmountOfLoan money; " & _
_
" SET @AmountOfLoan = (SELECT las.FutureValue " & _
" FROM LoanAllocations las " & _
" WHERE (las.LoanNumber = @LoanNbr)); " & _
_
" DECLARE @CurrentBalance money; " & _
" SET @CurrentBalance = (SELECT MIN(pay.Balance) " & _
" FROM Payments pay " & _
" WHERE (pay.LoanNumber = @LoanNbr)); " & _
_
" IF @CurrentBalance IS NULL " & _
" BEGIN " & _
" INSERT INTO Payments(PaymentDate, EmployeeNumber, " & _
" LoanNumber, PaymentAmount, " & _
" Balance, Notes) " & _
" VALUES(@PmtDate, @EmplNbr, @LoanNbr, @PmtAmt, " & _
" @AmountOfLoan - @PmtAmt, @Comments); " & _
" END " & _
" ELSE " & _
" BEGIN " & _
" INSERT INTO Payments(PaymentDate, EmployeeNumber, " & _
" LoanNumber, PaymentAmount, " & _
" Balance, Notes) " & _
" VALUES(@PmtDate, @EmplNbr, @LoanNbr, " & _
" @PmtAmt, @CurrentBalance - @PmtAmt, @Comments); " & _
" END " & _
"END;"
Dim Command As SqlCommand = _
New SqlCommand(strWattsALoan, Connect)
Connect.Open()
Command.ExecuteNonQuery()
msgbox("A new stored procedure has been created.")
End Using
End Sub
Private Sub Central_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
' CreateDatabase()
CurrentBalanceCalculator()
End Sub
|
- Execute the application
- Close the form and return to your programming environment
|
Practical Learning: Executing an Argumentative Procedure
|
|
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to NewPayment and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| Label |
Payment Date: |
|
|
| DateTimePicker |
|
DtpPaymentDate |
|
| Label |
Received By |
|
BackColor: Gray
AutoSize: False |
| Label |
Employee #: |
|
|
| MaskedTextBox |
|
TxtEmployeeNumber |
Mask: 00000 |
| TextBox |
|
TxtEmployeeName |
|
| Label |
Payment For |
|
BackColor: Gray
AutoSize: False |
| Label |
Loan #: |
|
|
| TextBox |
|
TxtLoanNumber |
|
| Label |
Payment Amount: |
|
|
| TextBox |
0.00 |
TxtPaymentAmount |
|
| Label |
Notes |
|
|
| Button |
Submit |
BtnSubmit |
|
| Button |
Close |
BtnClose |
|
|
- Right-click the form and click View Code
- Import the System.Data.SqlClient namespace
Imports System.Data.SqlClient
Public Class NewPayment
End Class
|
- In the Class Name combo box, select TxtEmployeeNumber
- In the Method Name combo box, select Leave and implement the event as follows:
Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles TxtEmployeeNumber.Leave
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='WattsALoan1';" & _
"Integrated Security=SSPI;")
Dim strSelect As String = _
"SELECT FullName FROM Employees " & _
"WHERE EmployeeNumber = '" & _
TxtEmployeeNumber.Text & "';"
Dim Command As SqlCommand = _
New SqlCommand(strSelect, Connect)
Connect.Open()
Dim rdrEmployees As SqlDataReader = Command.ExecuteReader()
While rdrEmployees.Read()
TxtEmployeeName.Text = rdrEmployees(0)
End While
End Using
End Sub
|
- In the Class Name combo box, select BtnSubmit button
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnSubmit_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnSubmit.Click
Dim PaymentAmount As Double = 0.0
Dim LoanNumber As Integer = 1000
' Don't save the record if there is no employee number
If TxtEmployeeName.Text.Length = 0 Then
MsgBox("You must enter a valid employee number.")
Exit Sub
End If
' Don't save the record if there is no loan number
If TxtLoanNumber.Text.Length = 0 Then
MsgBox("You must specify the loan number.")
Exit Sub
End If
' Make sure the user has specified the amount paid
If TxtPaymentAmount.Text.Length = 0 Then
MsgBox("You must specify the amount paid.")
Exit Sub
End If
Try
PaymentAmount = CDbl(TxtPaymentAmount.Text)
Catch Exc As FormatException
MsgBox("Invalid Payment Amount.")
End Try
Try
LoanNumber = CInt(TxtLoanNumber.Text)
Catch Exc As FormatException
MsgBox("Invalid Loan Number.")
End Try
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='WattsALoan1'; " & _
"Integrated Security='SSPI';")
Dim Command As SqlCommand = _
New SqlCommand("dbo.SpecifyCurrentBalance", _
Connect)
Command.CommandType = CommandType.StoredProcedure
Dim PrmWattsALoan As SqlParameter = New SqlParameter
PrmWattsALoan.ParameterName = "@PmtDate"
PrmWattsALoan.DbType = DbType.DateTime
PrmWattsALoan.Value = DtpPaymentDate.Value.ToString("d")
Command.Parameters.Add(PrmWattsALoan)
PrmWattsALoan = New SqlParameter
PrmWattsALoan.ParameterName = "@EmplNbr"
PrmWattsALoan.DbType = DbType.String
PrmWattsALoan.Value = TxtEmployeeNumber.Text
Command.Parameters.Add(PrmWattsALoan)
PrmWattsALoan = New SqlParameter
PrmWattsALoan.ParameterName = "@LoanNbr"
PrmWattsALoan.DbType = DbType.Int32
PrmWattsALoan.Value = LoanNumber
Command.Parameters.Add(PrmWattsALoan)
PrmWattsALoan = New SqlParameter
PrmWattsALoan.ParameterName = "@PmtAmt"
PrmWattsALoan.DbType = DbType.Double
PrmWattsALoan.Value = PaymentAmount
Command.Parameters.Add(PrmWattsALoan)
PrmWattsALoan = New SqlParameter
PrmWattsALoan.ParameterName = "@Comments"
PrmWattsALoan.DbType = DbType.String
PrmWattsALoan.Value = TxtNotes.Text
Command.Parameters.Add(PrmWattsALoan)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A new payment has been made.")
DtpPaymentDate.Value = DateTime.Today
TxtEmployeeNumber.Text = ""
TxtEmployeeName.Text = ""
TxtLoanNumber.Text = ""
TxtPaymentAmount.Text = "0.00"
TxtNotes.Text = ""
End Using
End Sub
|
- In the Class Name combo box, select BtnClose
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnClose_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): btnNewPayment
Text: New Payment...
- Double-click the Loan Allocations button and implement its event as
follows:
Private Sub Central_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
' CreateDatabase()
' CurrentBalanceCalculator()
End Sub
. . . No Change
Private Sub BtnNewPayment_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnNewPayment.Click
Dim Payment As NewPayment = New NewPayment
Payment.ShowDialog()
End Sub
|
- In the Class Name combo box, select Btn Close
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnClose_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
End
End Sub
|
- Execute the application and open the New Payment form
- Create a few Payments and close the New
Payment form
- Open the Payments form to see a summary of the payments
- Close the forms and return to your programming environment
|
|
