NWLoginForm.VB Listing
'* Enable SQL Server database connectivity
Imports System.Data.SqlClient
PublicClass NWLoginForm
PrivateSub NWLoginForm_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) HandlesMyBase.Load
'*********************************************************************
'* Populates combo box of employees
'* Positions form on the screen
'*********************************************************************
'* Declare variables
'* Programming note: Because this form has only one DB operation that
'* will not be repeated these items are declared inside the form
'* load event. In other cases some objects might be declared at the
'* form or module level.
Dim sqlConNW AsNew SqlConnection
Dim sqlComEmployees AsNew SqlCommand
Dim dsNW AsNew DataSet
Dim sqlDANW AsNew SqlDataAdapter
'* Position form on the screen
With System.Windows.Forms.Screen.PrimaryScreen.Bounds
Me.Top = (.Height - Me.Height) / 3 '* Set top of window 1/3 down
Me.Left = (.Width - Me.Width) / 2 '* Center left to right
EndWith
'*
'* Populate combo box
'*
'* Configure and open connection
sqlConNW.ConnectionString = _
NorthwindSales.My.Settings.NorthwindConnectionString
IfNot sqlConNW.State = ConnectionState.Open Then
sqlConNW.Open()
EndIf
'* Configure command object
With sqlComEmployees
.Connection = sqlConNW
.CommandType = CommandType.StoredProcedure
.CommandText = "up_Fill_Employee_Combo"
EndWith
'* Load dataset
sqlDANW.SelectCommand = sqlComEmployees
sqlDANW.Fill(dsNW, "Employees")
'* Configure combo box
cboEmployees.DataSource = dsNW.Tables("Employees")
cboEmployees.ValueMember = "EmployeeID"
cboEmployees.DisplayMember = "EmployeeName"
'* Disable sales button if there are no employees
If cboEmployees.Items.Count = 0 Then
cmdSales.Enabled = False
MsgBox("There are no employees available to process sales. Contact" _
& " Human Resources to hire somebody.", MsgBoxStyle.Exclamation, _
"Error")
EndIf
EndSub
PrivateSub cmdExit_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdExit.Click
'*********************************************************************
'* Exits the entire application
'*********************************************************************
End
EndSub
PrivateSub cmdSales_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdSales.Click
'*********************************************************************
'* Opens sales form and passes selected employee information to
'* controls on the sales form.
'* Closes the current form.
'*********************************************************************
Dim myNWSalesForm AsNew NWSalesForm
'* Pass employee information to the lblEmployees control on the newly
'* created sales form instance
With myNWSalesForm
.lblEmployee.Text = cboEmployees.Text
.lblEmployee.Tag = CStr(cboEmployees.SelectedValue)
EndWith
myNWSalesForm.Show()
'* Close current form
Me.Hide()
EndSub
EndClass
1
NWSalesForm.vb Listing
'* Enable use of SQL Server connectivity
Imports System.Data.SqlClient
PublicClass NWSalesForm
'* Declare form-level variables and objects
Dim sqlConNW AsNew SqlConnection
Dim dsNW AsNew DataSet
Dim sqlDANW AsNew SqlDataAdapter
Dim dvProducts As DataView
'* Declare form-level variable to indicate whether combo boxes
'* are in the process of being populated. Used to control
'* execution of SelectedIndexChanged events for the controls.
Dim blnIsLoading AsBoolean = False
PrivateSub NWSalesForm_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) HandlesMyBase.Load
'*********************************************************************
'* Positions form on the screen
'* Performs many set up activities to prepare the form for use. Uses
'* subroutine calls to:
'* - Load available products into the products gridview
'* - Perform one-time configuration of the products gridview control
'* - Load the Shippers combo box
'* - Load the Categories combo box
'* - Load the Customers combo box
'*********************************************************************
'* Declare variables
'* Programming note: Because this form has only one DB operation that
'* will not be repeated these items are declared inside the form
'* load event. In other cases some objects might be declared at the
'* form or module level.
Dim sqlComEmployees AsNew SqlCommand
'* Position form on the screen
With System.Windows.Forms.Screen.PrimaryScreen.Bounds
Me.Top = (.Height - Me.Height) / 3 '* Set top of window 1/3 down
Me.Left = (.Width - Me.Width) / 2 '* Center left to right
EndWith
'* Configure connection object connection string to be used
'* thrhoughout the form.
sqlConNW.ConnectionString = _
NorthwindSales.My.Settings.NorthwindConnectionString
'* Open the connection to be used for several operations during
'* form load
IfNot sqlConNW.State = ConnectionState.Open Then
sqlConNW.Open()
EndIf
'*
'* Load various combo boxes used throughout the form
'*
'* Set blnIsLoading variable to indicate that combo boxes are in the
'* process of being loaded
blnIsLoading = True
'* Load products dataset table and configure products
'* data grid view control
'* This code must be run before the LoadCategories() sub as that
'* procedure relies on the existence of a data view established
'* in GetProducts().
Call GetProducts()
Call ConfigureProductsGrid()
'* Load Shippers combo box
Call LoadShippers()
'* Load Categories combo box
Call LoadCategories()
'* Load Customers combo box
Call LoadCustomers()
blnIsLoading = False
EndSub
PrivateSub cmdExit_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdExit.Click
'*********************************************************************
'* Exits the entire application
'*********************************************************************
End
EndSub
#Region"Products"
PrivateSub GetProducts()
'*********************************************************************
'* Loads the products data table from a SP into a dataset table
'* Sets the form-level dataview object dvProducts to be the default
'* view of the Products dataset table
'*********************************************************************
Dim sqlCmd AsNew SqlCommand
'* Open the data connection if not already open
IfNot sqlConNW.State = ConnectionState.Open Then
sqlConNW.Open()
EndIf
'* Set SQL command specifics to fill the products grid
sqlCmd.Connection = sqlConNW
sqlCmd.CommandText = "up_Fill_Product_Grid" '* Point to SP
sqlCmd.CommandType = CommandType.StoredProcedure
'* Clear existing records from the Products dataset table
If dsNW.Tables.Contains("Products") Then
dsNW.Tables("Products").Clear()
EndIf
'* Configure data adaptor and fill data set
sqlDANW.SelectCommand = sqlCmd
sqlDANW.Fill(dsNW, "Products")
'* Set the data view object to a data source
'* Note: A dataview can stand between data in a dataset and
'* controls that may otherwise be bound to that data. A
'* dataview can have filters and sorts applied to it to
'* determine which data is displayed to the user without
'* requerying the source DB to control data display. Also,
'* changes to data in the local dataset persist even if a
'* dataview filter does not display changed rows.
'* Look in the Sub ConfigureProductsGrid() to see that the
'* .datasource property of the dgvProducts data grid view
'* is the dataview dvProducts and not the dataset table
'* Products. Also look at the cboCategories_SelectedIndexChanged
'* event to see how filters are applied to the dataview
dvProducts = dsNW.Tables("Products").DefaultView
EndSub
PrivateSub ConfigureProductsGrid()
'*********************************************************************
'* Performs one-time configuration of dgvProducts data grid view
'* - Sets data source
'* - Configures header row
'* - Configures column widths and column header text
'* - Sets some formatting
'* Loads the following columns in order:
'* ProductID, ProductName, UnitPrice, UnitsInStock, CategoryID
'*
'* THIS PROCEDURE MUST BE RUN AFTER THE PRODUCTS DATA TABLE IS
'* LOADED INTO THE DATASET (GetProducts() sub).
'*
'*********************************************************************
'* Configure data grid view
With dgvProducts
'* Configure data bindings. Automatically generates
'* columns and rows based on query contents
.DataSource = dvProducts
'* Configure headers and other grid properties
.ColumnHeadersDefaultCellStyle.Alignment = _
DataGridViewContentAlignment.MiddleCenter
.RowHeadersWidth = 25 '* Sets width of selection row
'*
'* Configure columns
'*
'* First column (0) ProductID
.Columns(0).Width = 50
.Columns(0).HeaderText = "ID"
.Columns(0).DefaultCellStyle.Alignment = _
DataGridViewContentAlignment.MiddleRight
'* Second column (1) ProductName
.Columns(1).Width = 224
.Columns(1).HeaderText = "Product"
.Columns(1).DefaultCellStyle.Alignment = _
DataGridViewContentAlignment.MiddleLeft
'* Third column (2) UnitPrice
.Columns(2).Width = 77
.Columns(2).HeaderText = "Price"
.Columns(2).DefaultCellStyle.Format = "#,##0.00"
.Columns(2).DefaultCellStyle.Alignment = _
DataGridViewContentAlignment.MiddleRight
'* Fourth column (3) UnitsInStock
.Columns(3).Width = 77
.Columns(3).HeaderText = "On Hand"
.Columns(3).DefaultCellStyle.Alignment = _
DataGridViewContentAlignment.MiddleRight
'* Fifth column (4) CategoryID (invisible)
.Columns(4).Visible = False
EndWith
EndSub
PrivateSub cboCategories_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cboCategories.SelectedIndexChanged
'*********************************************************************
'* Responds to customer selection of a category in the cboCategories
'* control. Applies a filter to the dvProducts data view to limit
'* which records in the dataset Products table are actually visible
'* in the data grid view control dgvProducts.
'* cboCategories.SelectedValue = 0 equates to the "All Categories"
'* choice that is loaded into the cboCategories data source by the
'* SQL SP up_Fill_Category_Combo. Modifying this SP to eliminate
'* this choice will destroy this logic.
'* Tests to see if IsLoading is True in which case it is assumed that
'* the form is loading initially and the DevaultView established in
'* GetProducts() is applied.
'*********************************************************************
'* Test to see if event is being called while combo box is being
'* loaded and exit sub without action if it is.
If blnIsLoading = TrueThen '* Combo box is being loaded
ExitSub
EndIf
'* Remove or apply a row filter depending on user choice
If cboCategories.SelectedValue = 0 Then'* All Categories selected
dvProducts.RowFilter = ""
Else '* One category selected
dvProducts.RowFilter = "CategoryID = " & _
cboCategories.SelectedValue.ToString
EndIf
EndSub
#EndRegion
#Region"ComboBoxes"
'* Repositiory of code to load various combo boxes in the form
'* Use #Region "RegionName"...#End Region blocks to group related
'* code in the editor and be able to hide it is a block.
'* Regions have no effect on code execution.
PrivateSub LoadCustomers()
'*********************************************************************
'* Retrieves data and populates the cboCustomers combo box with the
'* CompanyName and CustomerID values
'* Configures the combo box to have no customer initially selected
'* and disables the order tab control.
'*********************************************************************
Dim sqlComCustomers AsNew SqlCommand
'* Open connection if it is not already open
IfNot sqlConNW.State = ConnectionState.Open Then
sqlConNW.Open()
EndIf
'* Configure command object
With sqlComCustomers
.Connection = sqlConNW
.CommandType = CommandType.StoredProcedure
.CommandText = "up_Fill_Cust_Combo"
EndWith
'* Clear existing records if present and loads dataset table
If dsNW.Tables.Contains("Customers") Then
dsNW.Tables("Customers").Clear()
EndIf
sqlDANW.SelectCommand = sqlComCustomers
sqlDANW.Fill(dsNW, "Customers")
'* Configure combo box
cboCustomers.DataSource = dsNW.Tables("Customers")
cboCustomers.ValueMember = "CustomerID"
cboCustomers.DisplayMember = "CompanyName"
'* Set combo box to have no customer currently selected
If cboCustomers.Items.Count > 0 Then
cboCustomers.SelectedIndex = -1
EndIf
'* Disable tab control
tabOrder.Enabled = False
EndSub
PrivateSub LoadShippers()
'*********************************************************************
'* Retrieves data and populates the cboShippers combo box with the
'* CompanyName and ShipperID values
'*********************************************************************
Dim sqlComShippers AsNew SqlCommand
'* Open connection if it is not already open
IfNot sqlConNW.State = ConnectionState.Open Then
sqlConNW.Open()
EndIf
'* Configure command object
With sqlComShippers
.Connection = sqlConNW
.CommandType = CommandType.StoredProcedure
.CommandText = "up_Fill_Ship_Combo"
EndWith
'* Load dataset
sqlDANW.SelectCommand = sqlComShippers
sqlDANW.Fill(dsNW, "Shippers")
'* Configure combo box
cboShippers.DataSource = dsNW.Tables("Shippers")
cboShippers.ValueMember = "ShipperID"
cboShippers.DisplayMember = "CompanyName"
EndSub
PrivateSub LoadCategories()
'*********************************************************************
'* Populates combo box of categories
'* Uses a parameterized SP that will return either all rows in the
'* Categories table (CategoryID, CategoryName) or append the stored
'* data with a row with (0, 'All Categories') in it. We want the
'* latter version so we will use the parameter value of 1 to
'* make this happen.
'*********************************************************************
Dim sqlComNW AsNew SqlCommand
'* Empty existing records from Categories Table
If dsNW.Tables.Contains("Categories") Then
dsNW.Tables("Categories").Clear()
EndIf
'* Configure command object and add parameter
sqlComNW.Connection = sqlConNW
sqlComNW.CommandType = CommandType.StoredProcedure
sqlComNW.CommandText = "up_Fill_Category_Combo"
sqlComNW.Parameters.Add("@ReturnAllRow", SqlDbType.Int).Value = 1
'* Open connection if needed
If sqlConNW.State > ConnectionState.Open Then
sqlConNW.Open()
EndIf
'* Configure data adapter and load table
sqlDANW.SelectCommand = sqlComNW
sqlDANW.Fill(dsNW, "Categories")
'* Bind combo box to data table
cboCategories.DataSource = dsNW.Tables("Categories")
cboCategories.DisplayMember = "CategoryName"
cboCategories.ValueMember = "CategoryID"
EndSub
#EndRegion
#Region"FormManagement"
PrivateSub cmdNewOrder_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdNewOrder.Click
'*********************************************************************
'* Configures the form to enable a new order to be processed
'* - Clears shopping cart (lstvwOrderDetails)
'* - Zeros the total labels
'* - Resets the order text information controls and puts focus on
'* shipping information page of order tab control
'* - Disables this button
'*********************************************************************
'* Clear shopping card
lstvwOrderDetails.Items.Clear()
'* Update running total information
lblSubTotal.Text = "0.00"
lblShipping.Text = "0.00"
lblTotal.Text = "0.00"
'* Move to other tab control page
tabOrder.SelectTab("tabpageOrder")
'* Clear order input fields
Call ClearOrderInputs()
'* Disable this button and invoice button
cmdNewOrder.Enabled = False
cmdInvoice.Enabled = False
'* Set focus back to customer combo box
cboCustomers.Focus()
EndSub
PrivateSub ClearOrderInputs()
'*********************************************************************
'* Clears the input controls on the order information page in the
'* tabOrder control.
'*********************************************************************
txtShipTo.Text = ""
txtAddress.Text = ""
txtCity.Text = ""
txtRegion.Text = ""
txtPostalCode.Text = ""
txtCountry.Text = ""
'* Reset combo boxes
blnIsLoading = True
cboShippers.SelectedIndex = -1
cboCustomers.SelectedIndex = -1
blnIsLoading = False
EndSub
#EndRegion
#Region"Customers"
PrivateSub cmdRefresh_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdRefresh.Click
'*********************************************************************
'* Repopulates combo box of customers (initially populated during
'* form load
'* Must toggle the blnIsLoading to suppress execution of the
'* cboCustomers SelectedIndexChanged event
'*********************************************************************
blnIsLoading = True
Call LoadCustomers()
blnIsLoading = False
EndSub
PrivateSub cboCustomers_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cboCustomers.SelectedIndexChanged
'****************************************************************
'* Responds to customer selection of a customer from the customer
'* combo list.
'* - Fills in customer information including customer address as
'* default ShipTo address info
'* - Sets current date as order date and sets default Required
'* date to be five days later
'* - Enables frames on rest of the form to enable order processing
'****************************************************************
With cboCustomers
IfNot blnIsLoading Then '* Supresses execution if form is being loaded
Call GetCustomer() '* Retrieve and load customer data
Call LoadDates() '* Load default ship and required dates
'* Enable tab control for further processing
tabOrder.Enabled = True
EndIf
EndWith
EndSub
PrivateSub GetCustomer()
'****************************************************************
'* Uses ValueMember of customer combo box to retrieve customer
'* record. Fills in default customer information in Order area
'****************************************************************
Dim sqlCmd As SqlCommand
'* Open database connection
If sqlConNW.State = ConnectionState.Closed Then
sqlConNW.Open()
EndIf
'* Set up SQL Command used to populate all recordsets
sqlCmd = New SqlCommand
sqlCmd.Connection = sqlConNW
sqlCmd.CommandText = "up_Find_Cust_by_ID" '* Point to SP
sqlCmd.CommandType = CommandType.StoredProcedure
'* Load stored procedure parameter. See parameter code to see
'* how this code works with the parameter
Dim sqlParam AsNew SqlParameter
sqlParam.SqlDbType = SqlDbType.NChar
sqlParam.ParameterName = "@CustomerID"
sqlParam.Size = 5
sqlCmd.Parameters.Add(sqlParam)
'* Set SQL parameter value from the combo box of customers.
'* This value can be set repeatedly, if necessary, after the
'* parameter has been added to the Parameters collection.
sqlParam.Value = cboCustomers.SelectedValue
'* Configure data adaptor and fill data set
'* Clear the dataset table for customer if it already exists
If dsNW.Tables.Contains("Customer") Then
dsNW.Tables("Customer").Clear()
EndIf
'* Set data adapter and fill the dataset table Customer
sqlDANW.SelectCommand = sqlCmd
sqlDANW.Fill(dsNW, "Customer")
'* Test for successful read and populate Customer controls
'* in Order frame
'* The CheckNull function is in a module and ensures that a Null
'* database value is not written to the .Text property of a
'* control
With dsNW.Tables("Customer")
If .Rows.Count = 1 Then
txtShipTo.Text = CheckNull(.Rows(0)("CompanyName"))
txtAddress.Text = CheckNull(.Rows(0)("Address"))
txtCity.Text = CheckNull(.Rows(0)("City"))
txtRegion.Text = CheckNull(.Rows(0)("Region"))
txtPostalCode.Text = CheckNull(.Rows(0)("PostalCode"))
txtCountry.Text = CheckNull(.Rows(0)("Country"))
EndIf
EndWith
EndSub
PrivateSub LoadDates()
'****************************************************************
'* Loads date pickers with current date for Order Date and
'* a default value five days from now for the Required By date
'****************************************************************
dtpOrderDate.Value = Now.Date
dtpReqDate.Value = Now.Date.AddDays(5)
EndSub
PrivateSub TextBox_GotFocus(ByVal sender AsObject, _
ByVal e As System.EventArgs) Handles txtShipTo.GotFocus, _
txtAddress.GotFocus, txtCity.GotFocus, txtCountry.GotFocus, _
txtPostalCode.GotFocus, txtRegion.GotFocus, txtShipTo.Click, _
txtAddress.Click, txtCity.Click, txtCountry.Click, _
txtPostalCode.Click, txtRegion.Click
'******************************************************************
'* Handles the GotFocus and Click events for all text boxes on the
'* Order(TAB). Note that selecting a text box with the mouse does
'* not initiate the GotFocus event so the Handles expression for
'* this event must cover two events. This is possible because no
'* sepearte code is needed for either event in this case.
'* Selects all text contents when focus occurs.
'* Sender, the object initiating the event, will not be recognized
'* by the editor as being a text box and therefore will not be
'* recognized as supporting the .SelectAll event. This will result
'* in build error messages whenever the program is run.
'* Specifically typing myTextBox as a TextBox and setting it equal
'* to sender overcomes this issue.
'******************************************************************
Dim myTextBox As TextBox
myTextBox = sender
myTextBox.SelectAll()
EndSub
#EndRegion
#Region"ShoppingCart"
PrivateSub dgvProducts_CellClick(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvProducts.CellClick
'***************************************************************************************
'* Responds to clicking a row in the grid containing the available products.
'* Checks to ensure that there are products available (quantity is not zero). If so,
'* initializes the quantity selected to one, sets the focus to the quantity text box
'* (which prepares the current entry to be overwritten by selecting it in the GotFocus
'* event), and enables the Add button.
'* e.RowIndex gives the row address of the row containing the cell that was clicked
'* The actual column clicked is immaterial.
'***************************************************************************************
'* Reset selected row to be currenc clicked row.
dgvProducts.ClearSelection()
dgvProducts.Rows(e.RowIndex).Selected = True
'* Clear help label
lblHelp.Text = ""
'* Test to ensure that there are products available
IfCInt(dgvProducts.Item(3, e.RowIndex).Value) > 0 Then
txtQty.Text = "1"
txtQty.Focus()
cmdAdd.Enabled = True
Else '* No products available
Beep()
lblHelp.Text = "Not enough on hand to order."
txtQty.Text = "0"
cmdAdd.Enabled = False
EndIf
EndSub
PrivateSub txtQty_TextChanged(ByVal sender AsObject, ByVal e As System.EventArgs) _
Handles txtQty.TextChanged
'***************************************************************************************
'* Validates text entered by user in the Quantity text box to ensure that values are
'* valid integers and also to ensure that quantity entered does not exceed the