Home

Techniques of Commanding a Database

 

Parameterizing a SQL Expression

 

Introduction

So far, when using a command, we could create a SELECT statement that specified all fields that would be shown in the result. Here is an example:

SELECT * FROM Students;

We also saw how to select a group of fields that would be shown in the result. Here is an example:

SELECT * FROM Students WHERE ParentsNames IS NOT NULL;

When studying data selection, we saw how you could let the user specify the value of a particular column so the table or view would display only the result(s) based on that value. Through the magic of data binding, we saw how you could let the user enter the receipt number of a record, then locate and display that record. Instead of performing these operations manually, Transact-SQL, the .NET Framework, and Microsoft Visual Studio provide you all the necessary means of performing the same action (of locating one particular record or a group of records) without writing a single line of code or by following just a few steps.

 

Practical LearningPractical Learning: Introducing Parameterized Statements

  1. Start Microsoft Visual Basic and create a new Windows Application named WattsALoan2
  2. In the Solution Explorer, right-click Form1.vb and click Rename
  3. Type Central.vb and press Enter
  4. 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
  5. Execute the application
  6. Close the form and return to your programming environment
  7. In the Data Sources window, click Add New Data Source
  8. In the first page of the wizard, make sure Database is selected and click Next
  9. 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
  10. Click Next
  11. Change the connection string to CstWattsALoan and click Next
  12. In the list, click the check box of Tables
  13. Change the data set name to DsWattsALoan and click Finish
  14. In the Server Explorer, expand the server.WattsALoan1.dbo connection
  15. Click the + button of Database Diagram
  16. When the message box comes up, read it and click Yes
  17. Right-click Database Diagram and click Add New Diagram...
  18. In the dialog box, double-click each table and, when all tables have been added, click Close
  19. Create the relationships using the common fields
     
    Diagram
  20. Save the diagram as DgmWattsALoan and close it
  21. To create a new form, on the main menu, click Project -> Add Windows Form...
  22. Set the Name to Customers and click Add
  23. In the Data Sources window, drag Customers and drop it on the Customers form
  24. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    customersBindingSource BsCustomers
    customersTableAdapter TaCustomers
    customersBindingNavigator BnCustomers
  25. 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
  26. 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  
  27. Click OK
  28. Design the form as follows:
     
    Watts A Loan - Customers
     
    Control Text Name
    Button Close btnClose
  29. Double-click the Close button
  30. 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
  31. Access the Central form, add a button and change its properties as follows:
    (Name): BtnCustomers
    Text: Customers...
  32. 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
  33. To create a new form, on the main menu, click Project -> Add Windows Form...
  34. Set the Name to Payments and click Add
  35. In the Data Sources window, drag Payments and drop it on the Payments form
  36. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    customersBindingSource BsPayments
    customersTableAdapter TaPayments
    customersBindingNavigator BnPayments
  37. 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
  38. 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    
  39. Click OK
  40. Design the form as follows:
     
    Watts A Loan - Customers 
     
    Control Text Name
    Label  View Payments For (Enter Loan #):   
    TextBox    TxtAccountNumber 
    Button Submit BtnSubmit
    Button Close BtnClose
  41. 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
  42. In the Class Name combo box, select BtnClose
  43. 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
  44. Access the Central form, add a button and change its properties as follows:
    (Name): BtnPayments
    Text: View Payments...
  45. 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
  46. To create a new form, on the main menu, click Project -> Add Windows Form...
  47. Set the Name to NewLoanAllocation and click Add
  48. Design the form as follows:
     
    Watts A Loan - Customers
     
    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  
  49. Right-click the form and click View Code
  50. Just above the Public Class line, import the System.Data.SqlClient
     
    Imports System.Data.SqlClient
    
    Public Class NewLoanAllocation
    
    End Class
  51. In the Class Name combo box, select TxtEmployeeNumber
  52. 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
  53. In the Class Name combo box, select TxtAccountNumber
  54. 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
  55. In the Class Name combo box, select BtnCalculate
  56. 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
  57. In the Class Name combo box, select BtnSubmit
  58. 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
  59. In the Class Name combo box, select BtnClose
  60. 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
  61. Access the Central form, add a button and change its properties as follows:
    (Name): BtnNewLoanAllocation
    Text: New Loan Allocation...
  62. 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
  63. Execute the application and open the Loan Allocations form
  64. Create a few loans
     
    Watts A Loan
  65. Close the forms and return to your programming environment

A Parameterized Statement

A SQL statement is referred to as parameterized if it expects an external value in order to be complete. The statement starts like a normal SELECT operation with a WHERE clause. Here is an example:

SELECT * FROM Students WHERE Sex = 'male';

Instead of specifying the value of the column(s) in the WHERE clause, you can wait for the user to do so. This is also referred to as a parameterized query.

To create a parameterized statement in the Table window, in the Criteria section, click the box under the Filter column corresponding to the field that holds the value, type @ followed by a variable name. Here is an example:

Parameterized Expression

If you are manually writing a SELECT expression, replace the value with the name of the variable starting with @. Here is an example (we have already studied everything else in this code):

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim strConnection As String = _
 		"Data Source=(local);" & _
          	"Database='ROSH';" & _
          	"Integrated Security='SSPI';"
        Dim strStatement As String = _
       		"SELECT StudentNumber, LastName, " & _
                "FirstName, City, State " & _
                "FROM Students " & _
                "WHERE StudentNumber = @StdNbr;"

        Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

            Dim Command As SqlCommand = _
            New SqlCommand(strStatement, _
                        Connect)

            Connect.Open()
        End Using
    End Sub

End Class

You can also use a parameterized statement for a Windows application. To create it, after adding a data source to your application, you can open the DataSet in the Designer. Right-click the title bar of the table that holds the columns and click Configure... In the Query Builder, select the table(s) followed the columns that will be used. In the Criteria section of the window, click the Filter box that corresponds to the column on which the parameter will be applied and type @ followed by the name of a variable. Here is an example:

Query Builder

After specifying the parameter, you can click OK

TableAdapter Configuration Wizard

If you click Next, the TableAdapter Configuration Wizard would indicate the methods that would be created:

TableAdapter Configuration Wizard

You can then accept the options, click Next, and click Finish:

TableAdapter Configuration Wizard

Executing a Parameterized Statement

After creating a parameterized statement, you can test and/or use it. If you had created the statement in the Table window, when you run the query, the SQL interpreter would request a value for the column. If you create the statement in the Object Explorer in Microsoft SQL Server Management Studio or in the Server Explorer of Microsoft Visual Studio, when you execute the statement, a dialog box would come up, asking you to enter a value for the filtered field:

Executing a Parameterized Expression 

You can then type the appropriate value and click OK (or press Enter).

If you use a parameterized statement on a Windows form, the designer would have equipped the form with a text box and a button. You can type the value in that box and click the button. The controls on the form would then be filled with the values related. Here is an example:

Watts A Loan - A Customer Information

Introduction to the Parameters of a Command

Like a method, a database command can accept one or more arguments, referred to as parameters. This allows the command to serve as an intermediary between an object of the database, such as a table, a view, a function, or a stored procedure, and the user. To support the parameters passed to a command, the .NET Framework provides a class for each type of connection. The class used for a SQL connection is called SqlParameter. SqlParameter is based on the DbParameter class and it implements the IDbParameter, the IDadaParameter, and the IClonable interfaces. The DbParameter class is defined in the System.Data.Common namespace.

Like a method, a command can take more than one parameter. Unlike a method, the parameters passed to a command must be stored in a collection. To support a group of parameters, the .NET Framework provides a collection class for each type of connection. For a SQL connection, the class is called SqlParameterCollection. SqlParameterCollection is based on the DbParameterCollection class. The DbParameter class is defined in the System.Data.Common namespace. Like a normal .NET collection class, DbParameterCollection implements the IList, the ICollection, and the IEnumerable interfaces.

To be able to receive one or more parameters, the command classes are equipped with a property named Parameters which is of the type of the collection (as mentioned already, the name of the class depends on the type of database or collection).

The Parameters of a Command

 

Introduction

In order to pass a parameter to a command, you must build it, which is done in various steps. As mentioned already, a parameter is an object of type SqlParameter. To start, you can declare a variable of type SqlParameter but this is only an option (you can pass a parameter directly to a command). To assist you with declaring a variable, the parameter class is equipped with various constructors. The default constructor allows you to declare the variable without giving much information. Here is an example of using it:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim strConnection As String = _
		 "Data Source=(local);" & _
         	 "Database='ROSH';" & _
         	 "Integrated Security='SSPI';"
        Dim strStatement As String = _
     		"SELECT StudentNumber, LastName, " & _
                "FirstName, City, State " & _
                "FROM Students " & _
                "WHERE StudentNumber = @StdNbr;"

        Using Connect As SqlConnection = _
		 New SqlConnection(strConnection)

            Dim Command As SqlCommand = _
         	  New SqlCommand(strStatement, _
                         	 Connect)

            Dim prmStudent As SqlParameter = New SqlParameter

            Connect.Open()
        End Using
    End Sub

End Class

After creating the parameter, you must pass it to the command object. As mentioned already, a command class has a property named Parameters that is from a class that implements the IList interface. This allows you to call its Add() method to add the parameter. The Add() method comes in various versions. One of the versions allows you to pass a parameter object. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
        	New SqlCommand(strStatement, _
                	       Connect)

        Dim prmStudent As SqlParameter = New SqlParameter

        Command.Parameters.Add(prmStudent)

        Connect.Open()
        Dim rdrStudents As SqlDataReader = Command.ExecuteReader()
    End Using
End Sub

We will review other versions of this method as we move on.

The Name of a Parameter

Before passing a parameter to a command, you must specify the name of the parameter. To support this, the parameter class is equipped with a property named ParameterName. To specify the name of the argument, assign it to this property. Here is an example of using it:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
    	       New SqlCommand(strStatement, _
                              Connect)

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNumber"

        Command.Parameters.Add(prmStudent)

        Connect.Open()
        Dim rdrStudents As SqlDataReader = Command.ExecuteReader()
    End Using
End Sub

An alternative is to declare a parameter variable using a constructor other than the default. As we will see in future sections, the name of the argument is passed as the first argument to the constructor.

You may remember that when calling a procedure that takes an argument, you must provide it (the argument). You can pass a value to the procedure or you can also pass the argument using a name. The name of the argument in the method implementation and the name passed to it when called can be different. This is valid in the Visual Basic language but not in Transact-SQL. For a parameter object, you must use the same name used in the SQL statement for the parameter:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 	New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
        	New SqlCommand(strStatement, _
                         Connect)

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNbr"

        Command.Parameters.Add(prmStudent)

        Connect.Open()
        Dim rdrStudents As SqlDataReader = Command.ExecuteReader()
    End Using
End Sub

The Type of Parameter

As you should know from your knowledge of the Visual Basic language, when calling a method that takes an argument, you must pass the argument of the appropriate type. In the same way, a command requires that you specify the type of argument you are passing. To assist you with this, the parameter class is equipped with a property named DbType. The DbType property is of type DbType. This class provides various data types that correspond to the data types of Transact-SQL.

To specify the data type of a parameter, access the DbType property of your parameter variable. Assign it the corresponding data type accessed as a static field of the DbType class. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
	        New SqlCommand(strStatement, _
                               Connect)

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNbr"
        prmStudent.DbType = DbType.String

        Command.Parameters.Add(prmStudent)

        Connect.Open()
    End Using
End Sub

Instead of specifying the data type from the DbType property, you can use the following constructor of the SqlParameter class:

Public Sub New(parameterName As String, dbType As SqlDbType)

The first argument to this constructor is the name of the parameter. The second parameter is the data type:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
                New SqlCommand(strStatement, _
                         Connect)

        Dim prmStudent As SqlParameter = New SqlParameter("@StdNbr", DbType.String)

        Command.Parameters.Add(prmStudent)

        End Using
    End Sub

End Class

The Value of a Parameter

In order to produce a result from a parameterized SQL statement, you must provide the value that the command will apply. To support this, the parameter class is equipped with a property named Value. This value is of type object, which means it is your responsibility to provide the appropriate value for the parameter. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim strConnection As String = _
	  	"Data Source=(local);" & _
          	"Database='ROSH';" & _
          	"Integrated Security='SSPI';"
        Dim strStatement As String = _
       		"SELECT StudentNumber, LastName, " & _
                "FirstName, City, State " & _
                "FROM Students " & _
                "WHERE StudentNumber = @StdNbr;"

        Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

            Dim Command As SqlCommand = _
 	         New SqlCommand(strStatement, _
        	                Connect)

            Dim prmStudent As SqlParameter = New SqlParameter

            prmStudent.ParameterName = "@StdNbr"
            prmStudent.DbType = DbType.String
            prmStudent.Value = "723897"

            Command.Parameters.Add(prmStudent)

        End Using
    End Sub

End Class

Of course if the value is a number, you can assign it as such. If the user will provide the value from a control, you can access the value of that control and assign it to the Value property. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load

    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
           	New SqlCommand(strStatement, _
                         Connect)

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNbr"
        prmStudent.DbType = DbType.String
        prmStudent.Value = TxtStudentNumber.Text

        Command.Parameters.Add(prmStudent)
    End Using
End Sub

After providing all the information required by the command and adding the parameter to the Parameters collection, you can execute the command to retrieve its values and use them as you see fit. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub BtnLocate_Click(ByVal sender As System.Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnLocate.Click
        Dim strConnection As String = _
        	  "Data Source=(local);" & _
                  "Database='ROSH';" & _
                  "Integrated Security='SSPI';"
        Dim strStatement As String = _
     	        "SELECT StudentNumber, LastName, " & _
                "FirstName, City, State " & _
                "FROM Students " & _
                "WHERE StudentNumber = @StdNbr;"

        Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

            Connect.Open()
            Dim Command As SqlCommand = _
	           New SqlCommand(strStatement, _
                  	          Connect)

            Dim prmStudent As SqlParameter = New SqlParameter

            prmStudent.ParameterName = "@StdNbr"
            prmStudent.DbType = DbType.String
            prmStudent.Value = TxtStudentNumber.Text
            Command.Parameters.Add(prmStudent)

            Dim rdrStudents As SqlDataReader = Command.ExecuteReader()

            While rdrStudents.Read()
                TxtLastName.Text = rdrStudents(1)
                TxtFirstName.Text = rdrStudents(2)
                TxtCity.Text = rdrStudents(3)
                TxtState.Text = rdrStudents(4)
            End While
        End Using
    End Sub
End Class

Red Oak High School

The Parameters of a Stored Procedure

 

Introduction

Although commands can benefit from parameters, the primary use of parameters is on stored procedures. This is because stored procedures can receive arguments, either or both by value and/or by reference. Before using a stored procedure in a command, obviously you must first create it. It could be as simple as selecting one or a few columns of a table or a view. From the previous lesson, we saw the following example:

CREATE PROCEDURE GetStudentIdentification
AS
BEGIN
    SELECT FirstName, LastName, DateOfBirth, Sex
    FROM Students
END

Practical LearningPractical Learning: Creating a Stored Procedure

  1. In the Solution Explorer, right-click Central.vb and click View Code
  2. 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
  3. Execute the application
  4. Close the form and return to your programming environment

The Type of Command

There are a SQL statement and two types of objects a command object can process. When using a command, before executing it, you must specify its type of action. To assist you with this, the command class is equipped with a property named CommandType. The CommandType property is based on the CommandType enumeration.

One of the members of the CommandType enumeration is Text. This is applied for a regular SELECT operation and this is the default type used by the command. This means that if you do not specify the type, Text is applied. That has been the case for all the commands (SELECT statements and functions) we have used in previous sections and lessons. Still, if you want to specify that you are using a regular SQL statement for your action, select Text as the CommandType and assign it to the CommandType property. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load

    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
           	New SqlCommand(strStatement, _
                         Connect)
	Command.CommandType = CommandType.Text

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNbr"
        prmStudent.DbType = DbType.String
        prmStudent.Value = TxtStudentNumber.Text

        Command.Parameters.Add(prmStudent)
    End Using
End Sub

Because a function is treated like a SELECT statement, if you using it to select some columns, its type is also the Text value. For example, imagine you had created a function as follows:

CREATE FUNCTION dbo.GetFullName
(
    @FName varchar(20),
    @LName varchar(20)
)
RETURNS varchar(41)
AS
    BEGIN
	RETURN @LName + ', ' + @FName;
    END

You can call it as follows:

Red Oak High School

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub BtnSubmit_Click(ByVal sender As System.Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnSubmit.Click
        Dim strConnection As String = _
		"Data Source=(local);" & _
       		"Database='ROSH';" & _
       		"Integrated Security='SSPI';"
        Dim strStatement As String = _
        	"SELECT dbo.GetFullName('" & _
          	TxtFirstName.Text & "', '" & _
          	TxtLastName.Text & "');"

        Using Connect As SqlConnection = _
	       New SqlConnection(strConnection)

            Dim Command As SqlCommand = _
            New SqlCommand(strStatement, Connect)
            Command.CommandType = CommandType.Text

            Connect.Open()
            Dim rdrStudents As SqlDataReader = Command.ExecuteReader()

            While rdrStudents.Read()
                TxtFullName.Text = rdrStudents(0)
            End While
        End Using
    End Sub

    Private Sub BtnClose_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        End
    End Sub
End Class

Another member of the CommandType enumeration is named TableDirect. This member can be applied if you are using an OLE DB connection.

Executing a Parameterized Stored Procedure

If you are planning to use a stored procedure, the CommandType enumeration supports it through the StoredProcedure member.

When declaring a command variable, you can pass the name of the stored procedure as the first argument. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='ROSH';" & _
		           "Integrated Security='SSPI';")

        Dim Command As SqlCommand = _
    	    New SqlCommand("GetStudentIdentification", Connect)

        Command.CommandType = CommandType.StoredProcedure

    End Using
End Sub

Alternatively, if you declare the command variable using its default constructur, you can access its CommandText property and assign the name of the stored procedure to it. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='ROSH';" & _
		           "Integrated Security='SSPI';")

        Dim Command As SqlCommand = New SqlCommand()

        Command.CommandText = "GetStudentIdentification;"
        Command.Connection = Connect

        Command.CommandType = CommandType.StoredProcedure

        Connect.Open()
    End Using
End Sub

Once the command is ready, you can execute it. If the stored procedure contains a simple or normal SELECT statement, you can retrieve its values and use them as you see fit, such as displaying them in data grid view. Here is an example:

Red Oak High School

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='ROSH';" & _
     			   "Integrated Security='SSPI';")

        Dim Command As SqlCommand = _
             New SqlCommand("GetStudentIdentification", _
                  Connect)
        Command.CommandType = CommandType.StoredProcedure

        Connect.Open()
        Dim sdaStudents As SqlDataAdapter = New SqlDataAdapter(Command)
        Dim dsStudents As DataSet = New DataSet("StudentsSet")

        sdaStudents.Fill(dsStudents)
        DgvStudents.DataSource = dsStudents
        DgvStudents.DataMember = dsStudents.Tables(0).TableName
    End Using
End Sub

Red Oak High School

When it comes to parameters of a stored procedure, if you have one that takes arguments, follow the same rules we reviewed earlier for the commands, except that if the argument has a default value, you can omit specifying its value.

Practical LearningPractical Learning: Executing an Argumentative Procedure

  1. To create a new form, on the main menu, click Project -> Add Windows Form...
  2. Set the Name to NewPayment and click Add
  3. Design the form as follows:
     
    Watts A Loan - Payment
     
    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  
  4. Right-click the form and click View Code
  5. Import the System.Data.SqlClient namespace
     
    Imports System.Data.SqlClient
    
    Public Class NewPayment
    
    End Class
  6. In the Class Name combo box, select TxtEmployeeNumber
  7. 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
  8. In the Class Name combo box, select BtnSubmit button
  9. 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
  10. In the Class Name combo box, select BtnClose
  11. 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
  12. Access the Central form, add a button and change its properties as follows:
    (Name): btnNewPayment
    Text: New Payment...
  13. 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
  14. In the Class Name combo box, select Btn Close
  15. 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
  16. Execute the application and open the New Payment form
  17. Create a few Payments and close the New Payment form
  18. Open the Payments form to see a summary of the payments
  19. Close the forms and return to your programming environment

Previous Copyright 2008 Yevol Home