ordersform.vb

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· VB 代码 · 共 1,404 行 · 第 1/4 页

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.ComponentModel

Public Class OrdersForm
	Private Const strTitle As String = "Data Entry Error!"
	Private strCustID As String
	Private blnIsNewCustomer As Boolean
	Private blnIsNewOrderRow As Boolean
	Private blnUseTabs As Boolean
	Private blnIsNewDetail As Boolean
	'Change below to false for normal operation
    Private blnUseSampleData As Boolean = True
    Private clsCancelNew As New clsImplCancelNew
    Private dsLookups As New DataSet
    'V3Final - Sorted Products lookup for DataGridView combo box columns
    Private dvProdsLookup As DataView
    Private blnHasLoaded As Boolean

    Private Class clsImplCancelNew
        Implements ICancelAddNew

        Public Sub CancelNew(ByVal itemIndex As Integer) Implements System.ComponentModel.ICancelAddNew.CancelNew

        End Sub

        Public Sub EndNew(ByVal itemIndex As Integer) Implements System.ComponentModel.ICancelAddNew.EndNew

        End Sub
    End Class

#Region "Form_Load and Form_Activate Event Handlers"
	'*********************************************
	'Form_Load and Form_Activate event handlers
	'and related control enable/disable procedures
	'*********************************************

	Private Sub OrdersForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		'Change below to False to start in single page mode
		chkTabPageEdit.Checked = True
		chkTabPageEdit_CheckedChanged(Nothing, Nothing)
        tabOrders.TabPages.Remove(pagEditOrder)

		'Position the help text box
		With OrdersDataGridView
			txtHelp.Top = .Top - 25
			txtHelp.Left = .Left
			txtHelp.Width = .Width
			txtHelp.Height = .Height + 25
            'txtHelp.BackColor = pagCustOrders.BackColor
		End With

		'Lock down the form until user action occurs
		LockCustTextBoxes(True, True)
		EnableOrdersGrid(False, True)
		EnableOrder_DetailsGrid(False, True)
		ShowCustomerEditButtons(False)
		ShowOrdersEditButtons(False)
        ShowGrids(False)

        Dim strFile As String = Application.StartupPath + "\LookupsDataSet.xml"
        If File.Exists(strFile) Then
            'Load dsLookups from the file
            dsLookups.ReadXml(strFile)
        Else
            LoadLookupLists()
        End If
        'Bind all combo box lists to their DataSources
        LoadAndBindComboBoxes()

        'Following is optional
        'Set the combo box to RATTC which has an unshipped order
        With dsLookups.Tables(0)
            Dim intRow As Integer
            For intRow = 0 To .Rows.Count - 1
                If Mid(.Rows(intRow).Item(0).ToString, 1, 5) = "RATTC" Then
                    cboCustomerID.SelectedIndex = intRow
                    Exit For
                End If
            Next intRow
        End With
    End Sub

    Private Sub LoadLookupLists()
        'Use runtime DataSet and DataAdapters to load combo boxes 
        Me.Cursor = Cursors.WaitCursor
        'Customers
        Dim strSQL As String = "SELECT CustomerID, CustomerID + ' - ' + " + _
           "CompanyName AS IDName FROM dbo.Customers;"
        'Employees
        strSQL += "SELECT EmployeeID, LastName + ', ' + FirstName AS EmployeeName " + _
           "FROM dbo.Employees;"
        'Shippers
        strSQL += "SELECT ShipperID, CompanyName FROM dbo.Shippers;"
        'Products
        strSQL += "SELECT ProductID, ProductName, UnitPrice, QuantityPerUnit " + _
           "FROM dbo.Products"
        'An example of use of My.Settings to retrieve connection strings
        Dim strConn As String = MySettings.Default.NorthwindConnection.ToString
        Dim daLookups As New SqlDataAdapter(strSQL, strConn)
        Try
            daLookups.Fill(dsLookups)
            With dsLookups
                .Tables(0).TableName = "CustsLookup"
                .Tables(1).TableName = "EmplsLookup"
                .Tables(2).TableName = "ShipsLookup"
                .Tables(3).TableName = "ProdsLookup"
            End With
            'Save dsLookups as a file with the embedded schema
            Dim strFile As String = Application.StartupPath + "\LookupsDataSet.xml"
            dsLookups.WriteXml(strFile, XmlWriteMode.WriteSchema)
        Catch excFill As Exception
            MsgBox(excFill.Message + excFill.StackTrace, , "Error Filling Lookup Tables")
        Finally
            If daLookups.SelectCommand.Connection.State = ConnectionState.Open Then
                'Shouldn't happen
                daLookups.SelectCommand.Connection.Close()
            End If
        End Try
    End Sub

    Private Sub LoadAndBindComboBoxes()
        'CustomerID (Customers)
        With cboCustomerID
            .DataSource = dsLookups.Tables("CustsLookup")
            .DisplayMember = "IDName"
            .ValueMember = "CustomerID"
        End With

        'EmployeeID (Orders)
        With tbc2EmployeeID 'DataGridViewComboBoxColumn2
            .DataSource = dsLookups.Tables("EmplsLookup")
            .DisplayMember = "EmployeeName"
            .ValueMember = "EmployeeID"
        End With

        With cboEmployeeID 'On Edit Selected Order page
            .DataSource = dsLookups.Tables("EmplsLookup")
            .DisplayMember = "EmployeeName"
            .ValueMember = "EmployeeID"
            .DataBindings.Clear()
            'Any of these bindings work; BindingSource is the preferred data source
            '.DataBindings.Add("SelectedValue", NorthwindDataSet.Orders, "EmployeeID")
            '.DataBindings.Add(New Binding("SelectedValue", NorthwindDataSet, _
            '"Orders.EmployeeID"))
            .DataBindings.Add(New Binding("SelectedValue", OrdersBindingSource, _
               "EmployeeID", True))
        End With

        'ShipVia (Orders)
        With tbc3ShipVia 'DataGridViewComboBoxColumn3
            .DataSource = dsLookups.Tables("ShipsLookup")
            .DisplayMember = "CompanyName"
            .ValueMember = "ShipperID"
        End With

        With cboShipVia 'On Edit Selected Order page
            .DataSource = dsLookups.Tables("ShipsLookup")
            .DisplayMember = "CompanyName"
            .ValueMember = "ShipperID"
            .DataBindings.Clear()
            .DataBindings.Add(New Binding("SelectedValue", OrdersBindingSource, _
               "ShipVia", True))
        End With


        'V3Final - DataSource for ProductName combo boxes changed to DataView
        dvProdsLookup = New DataView(dsLookups.Tables(3))
        dvProdsLookup.Sort = "ProductName"

        'ProductID (Order Details)
        With tbc4ProductID
            '.DataSource = dsLookups.Tables("ProdsLookup")
            .DataSource = dvProdsLookup
            .DisplayMember = "ProductName"
            .ValueMember = "ProductID"
        End With

        'ProductID (Order Details)
        With tbc5ProductID
            '.DataSource = dsLookups.Tables("ProdsLookup")
            .DataSource = dvProdsLookup
            .DisplayMember = "ProductName"
            .ValueMember = "ProductID"
        End With

        blnHasLoaded = True

        bsCustsLookup.DataSource = dsLookups
        bsCustsLookup.DataMember = "CustsLookup"
        'Test the BindingSource (optional)
        Dim intRows As Integer = bsCustsLookup.Count
    End Sub

    Private Sub chkTabPageEdit_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkTabPageEdit.CheckedChanged
        'Change to/from editing on Edit Selected Order page
        With chkTabPageEdit
            If .Checked Then
                If tabOrders.TabPages.Count = 1 And OrdersBindingSource.Count > 0 Then
                    tabOrders.TabPages.Add(pagEditOrder)
                End If
                tabOrders.Height = 522 - 150
                Me.Height = 567 - 150
                Order_DetailsDataGridView.Visible = False
                lblOrderDetailsGrid.Visible = False
                blnUseTabs = True
                If Not OrdersDataGridView.Visible Then
                    txtHelp.Visible = True
                End If
                lblHelp.Text = "Click Edit Orders, select an OrderID and press F2 to display or edit it."
            Else
                txtHelp.Visible = False
                If tabOrders.TabPages.Count = 2 Then
                    tabOrders.TabPages.Remove(pagEditOrder)
                End If
                tabOrders.Height = 522
                Me.Height = 567
                Order_DetailsDataGridView.Visible = True
                lblOrderDetailsGrid.Visible = True
                blnUseTabs = False
                lblHelp.Text = "Click Edit Orders to enable the grids"
            End If
            Me.CenterToScreen()
        End With
    End Sub

    Private Sub LockCustTextBoxes(ByVal blnLock As Boolean, ByVal blnLockID As Boolean)
        'Set text boxes read-only or read-write
        CustomerIDTextBox.ReadOnly = blnLockID
        CompanyNameTextBox.ReadOnly = blnLock
        ContactNameTextBox.ReadOnly = blnLock
        ContactTitleTextBox.ReadOnly = blnLock
        AddressTextBox.ReadOnly = blnLock
        CityTextBox.ReadOnly = blnLock
        RegionTextBox.ReadOnly = blnLock
        PostalCodeTextBox.ReadOnly = blnLock
        CountryTextBox.ReadOnly = blnLock
        PhoneTextBox.ReadOnly = blnLock
        FaxTextBox.ReadOnly = blnLock
    End Sub

    Private Sub ShowCustomerEditButtons(ByVal blnVisible As Boolean)
        'Show or hide Customer buttons
        btnEditCustData.Visible = blnVisible
        'btnNewCustomer.Visible = blnVisible
        btnSaveCustData.Visible = blnVisible
        btnCancelCustEdit.Visible = blnVisible
    End Sub

    Private Sub ShowGrids(ByVal blnVisible As Boolean)
        'Show or hide both grids
        OrdersDataGridView.Visible = blnVisible
        lblOrdersGrid.Visible = blnVisible
        Order_DetailsDataGridView.Visible = blnVisible
        lblOrderDetailsGrid.Visible = blnVisible
        chkTabPageEdit.Visible = blnVisible
        txtHelp.Visible = Not blnVisible
    End Sub

    Private Sub EnableOrdersGrid(ByVal blnEnable As Boolean, ByVal blnReadOnly As Boolean)
        'Enable or disable grids and set read-only or read-write
        With OrdersDataGridView
            .Enabled = blnEnable
            .ReadOnly = blnReadOnly
            .AllowUserToAddRows = Not blnReadOnly
            .AllowUserToDeleteRows = Not blnReadOnly
            'These columns are always read-only
            .Columns(0).ReadOnly = True
            .Columns(1).ReadOnly = True
        End With
    End Sub

    Private Sub ShowOrdersEditButtons(ByVal blnVisible As Boolean)
        'Show or hide Orders buttons
        btnEditOrders.Visible = blnVisible
        btnNewOrder.Visible = blnVisible
        btnSaveOrders.Visible = blnVisible
        btnCancelOrderEdits.Visible = blnVisible
    End Sub

    Private Sub EnableOrder_DetailsGrid(ByVal blnEnable As Boolean, ByVal blnReadOnly As Boolean)
        With Order_DetailsDataGridView
            .Enabled = blnEnable
            .ReadOnly = blnReadOnly
            .AllowUserToAddRows = Not blnReadOnly
            .AllowUserToDeleteRows = Not blnReadOnly
            .Columns(0).ReadOnly = True
        End With
    End Sub

#End Region

#Region "Get Orders and Edit Customer Data Event Handlers"
    '*******************************
    'Get Orders and Edit Customers Event Handlers
    'Command button event handlers
    '*******************************

    Private Sub btnGetOrders_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetOrders.Click

        'Load the DataTables with parameterized SELECT queries
        Dim strCustomerID As String = Nothing
        'V3Final: Manual entry features removed
        'strCustomerID = cboCustomerID.SelectedValue.ToString
        strCustomerID = "RATTC"
        Try
            CustomersTableAdapter.GetCustomerOrders(NorthwindDataSet.Customers, _
              strCustomerID)
            OrdersTableAdapter.FillOrders(NorthwindDataSet.Orders, _
             strCustomerID)
            Order_DetailsTableAdapter.FillOrder_Details(NorthwindDataSet.Order_Details, _
             strCustomerID)
            CustomerIDTextBox.Text = UCase(CustomerIDTextBox.Text)
            If CustomersBindingSource.Count > 0 Or blnIsNewCustomer Then
                If CustomersBindingSource.Count > 0 Then
                    blnIsNewCustomer = False
                Else
                    Return
                End If
                LockCustTextBoxes(True, True)
                ShowCustomerEditButtons(True)
                btnSaveCustData.Enabled = False
                ShowGrids(True)
                If blnUseTabs Then
                    'Hide the Order Details grid
                    Order_DetailsDataGridView.Visible = False
                    lblOrderDetailsGrid.Visible = False
                End If
                'Disable the grids (enable with Edit button)
                EnableOrdersGrid(False, False)
                EnableOrder_DetailsGrid(False, False)
                ShowOrdersEditButtons(True)
                btnCancelCustEdit.Enabled = False
                btnEditCustData.Enabled = True
                pagEditOrder.Text = "Edit Selected Order"
                If tabOrders.TabPages.Count = 2 Then
                    tabOrders.TabPages.Remove(pagEditOrder)
                End If
                lblHelp.Visible = True
                With OrdersDataGridView
                    'Set shipped rows read-only
                    Dim intRow As Integer
                    For intRow = 0 To .Rows.Count - 2
                        If TypeOf (.Rows(intRow).Cells(5).Value) Is DBNull Then
                            .Rows(intRow).ReadOnly = False
                        Else
                            'Order has shipped
                            .Rows(intRow).ReadOnly = True