ordersform.vb

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

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

Public Class OrdersForm
    Private blnIsNewCustomer As Boolean

    '*********************************************
    'Form_Load and Form_Activate event handlers
    'and related control enable/disable procedures
    '*********************************************

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Lock down the form until user action occurs
        LockTextBoxes(True, True)
        EnableOrdersGrid(False, True)
        EnableOrder_DetailsGrid(False, True)
        ShowCustomerEditButtons(False)
        OrdersToolStrip.Visible = False
        LoadCustomerIDCombo()
    End Sub

    Private Sub LoadCustomerIDCombo()
        'Load the cbo CustomerID combo box
        Dim cnNwind As New SqlConnection(My.Settings.NorthwindConnectionString)
        Dim strSQL As String = "SELECT CustomerID FROM dbo.Customers ORDER BY CustomerID"
        Dim cmNwind As New SqlCommand(strSQL, cnNwind)
        Try
            cnNwind.Open()
            Dim sdrCustID As SqlDataReader = cmNwind.ExecuteReader
            With sdrCustID
                If .HasRows Then
                    cboCustomerID.Items.Clear()
                    While .Read
                        cboCustomerID.Items.Add(sdrCustID(0).ToString)
                    End While
                    cboCustomerID.Text = cboCustomerID.Items(0).ToString
                End If
                .Close()
            End With
        Catch exc As Exception
            MsgBox("Error loading CustomerID combo box.")
        Finally
            cnNwind.Close()
        End Try
    End Sub

    Private Sub LockTextBoxes(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)
        EditCustomerDataToolStripButton.Visible = blnVisible
        NewCustomerToolStripButton.Visible = blnVisible
        SaveDataToolStripButton.Visible = blnVisible
        CancelEditToolStripButton.Visible = 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
        End With
    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
        End With
    End Sub

    Private Sub OrdersForm_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
        'Set focus to the CustomerID text box
        cboCustomerID.Focus()
    End Sub

    Private Sub EditCustomerDataToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditCustomerDataToolStripButton.Click
        'Enable the Customers text boxes
        LockTextBoxes(False, True)
        OrdersToolStrip.Visible = True
        ShowCustomerEditButtons(True)
    End Sub

    Private Sub EditOrdersToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditOrdersToolStripButton.Click
        'Enable the DataGridView controls
        EnableOrdersGrid(True, False)
        EnableOrder_DetailsGrid(True, False)
    End Sub

    '**************************************************************
    'Event handlers and procedures for adding a new customer record
    '**************************************************************

    Private Sub NewCustomerToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewCustomerToolStripButton.Click
        'Add a new customer record
        CustomersBindingSource.AddNew()
        CustomerIDTextBox.Text = ""
        CustomerIDTextBox.Visible = True
        CustomerIDLabel.Visible = True
        cboCustomerID.DropDownStyle = ComboBoxStyle.DropDown
        cboCustomerID.Text = ""
        'Clear the text boxes and order grids
        FillByCustomerIDToolStripButton_Click(Nothing, Nothing)
        LockTextBoxes(False, False)
        EditCustomerDataToolStripButton.Visible = False
        CompanyNameTextBox.Focus()
        blnIsNewCustomer = True
    End Sub

    Private Sub CompanyNameTextBox_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles CompanyNameTextBox.LostFocus
        If blnIsNewCustomer Then
            'Compute a five-character CustomerID
            With CompanyNameTextBox
                If InStr(.Text, " ") > 0 Then
                    Dim intPos As Integer
                    intPos = InStr(.Text, " ")
                    Dim strID As String
                    strID = UCase(Mid(.Text, 1, intPos - 1))
                    If Len(strID) > 4 Then
                        strID = Mid(strID, 1, 4)
                    End If
                    'Add to create 5 characters
                    strID += UCase(Mid(.Text, intPos + 1, 5 - Len(strID)))
                    strID = Replace(strID, " ", "")
                    'Remove acutes, punctuation, and non-USASCII characters
                    Dim strChars As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
                    Dim strChar As String = Nothing
                    Dim intCtr As Integer
                    For intCtr = 1 To Len(.Text)
                        strChar = Mid(strID, intCtr, 1)
                        If InStr(strChars, strChar) = 0 Then
                            strID = Replace(strID, strChar, "X")
                        End If
                    Next
                    'Test for five remaining characters (e.g, Company name = "El Al")
                    If Len(strID) < 5 Then
                        strID += "XXXXX"
                        strID = Mid$(strID, 1, 5)
                    End If
                    CustomerIDTextBox.Text = strID
                Else
                    CustomerIDTextBox.Text = UCase(Mid(.Text, 1, 5))
                End If
                CustomerIDTextBox.Text = CustomerIDTextBox.Text
                SaveDataToolStripButton.Visible = True
                CancelEditToolStripButton.Visible = True
                OrdersToolStrip.Visible = True
                'TODO: Test for a duplicate with a lookup table 
                'Enable entering a new order
                EnableOrdersGrid(True, False)
                'OrdersDataConnector.AddNew()
            End With
        End If
    End Sub


    '***************************
    'DataGridView event handlers
    '***************************

    Private Sub Order_DetailsDataGridView_CellValueNeeded(ByVal sender As Object, _
            ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) _
            Handles Order_DetailsDataGridView.CellValueNeeded
        'Calculate and display the unbound Extended column values
        With Order_DetailsDataGridView
            'Test for correct column and DBNull values, which throw exceptions
            If e.ColumnIndex = 5 And _
            Not (TypeOf (.Rows(e.RowIndex).Cells(1).Value) Is DBNull _
            OrElse TypeOf (.Rows(e.RowIndex).Cells(3).Value) Is DBNull _
            OrElse TypeOf (.Rows(e.RowIndex).Cells(4).Value) Is DBNull) Then
                'Variables are declared for readability
                Dim intQuan As Integer
                Dim decPrice As Decimal
                Dim decDisc As Decimal

                intQuan = CInt(.Rows(e.RowIndex).Cells(1).Value)
                decPrice = CDec(.Rows(e.RowIndex).Cells(3).Value)
                decDisc = CDec(.Rows(e.RowIndex).Cells(4).Value)
                e.Value = intQuan * decPrice * (1 - decDisc)
            End If
        End With
    End Sub

    Private Sub OrdersDataGridView_DefaultValuesNeeded(ByVal sender As Object, _
       ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) _
       Handles OrdersDataGridView.DefaultValuesNeeded
        'Add default values to a new Orders row
        With e.Row
            .Cells(1).Value = Me.cboCustomerID.Text
            'Andrew Fuller gets default credit because he's the sales VP
            .Cells(2).Value = 2
            .Cells(3).Value = Today.ToShortDateString
            'Two weeks is the default shipment arrival time
            .Cells(4).Value = Today.AddDays(14).ToShortDateString
            'United Package is the preferred shipper
            .Cells(6).Value = 2
            'Freight requires a 0 value, null throws an exception
            .Cells(7).Value = 0
            'Default shipping address is the billing address
            .Cells(8).Value = Me.CompanyNameTextBox.Text
            .Cells(9).Value = Me.AddressTextBox.Text
            .Cells(10).Value = Me.CityTextBox.Text
            .Cells(11).Value = Me.RegionTextBox.Text
            .Cells(12).Value = Me.PostalCodeTextBox.Text
            .Cells(13).Value = Me.CountryTextBox.Text
            'Deselect any selected cells
            Dim intCtr As Integer
            For intCtr = 0 To 13
                .Cells(intCtr).Selected = False
            Next
            'Select EmployeeID
            .Cells(2).Selected = True
        End With
    End Sub

    Private Sub OrdersDataGridView_UserAddedRow(ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles OrdersDataGridView.UserAddedRow
        'Confirm or cancel new order entry
        Dim strMsg As String = "Please confirm addition of new order for " + _
         Me.CompanyNameTextBox.Text + "?" + vbCrLf + vbCrLf + _
         "Click the Order Details grid to add a default line item." + vbCrLf + vbCrLf + _
         "(The OrderID might not be the same as shown when you update the database.)"
        With OrdersBindingSource
            If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, _
             "New Order Confirmation") = MsgBoxResult.Yes Then
                If blnIsNewCustomer Then
                    EnableOrder_DetailsGrid(True, False)
                    OrdersToolStrip.Visible = True
                End If
            Else
                'Delete the entry and reposition to latest order
                .RemoveAt(.Position - 1)
                .MoveFirst()
            End If
        End With
    End Sub

    Private Sub Order_DetailsDataGridView_DefaultValuesNeeded(ByVal sender _
            As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) _
            Handles Order_DetailsDataGridView.DefaultValuesNeeded
        'Add temporary default values
        With e.Row
            .Cells(1).Value = 1
            .Cells(2).Value = 17
            .Cells(3).Value = 0
            .Cells(4).Value = 0
        End With
    End Sub

    Private Sub OrdersDataGridView_DataError(ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) _
        Handles OrdersDataGridView.DataError
        'Handle data entry errors (delegate)
        Dim strMsg As String = "Invalid data in column " + e.ColumnIndex.ToString + _
        " of row " + e.RowIndex.ToString + " of the Orders grid. " + _
        "Press Esc to cancel the edit or enter an appropriate value."
        MsgBox(strMsg, MsgBoxStyle.Exclamation, "Data Entry Error")
    End Sub

    '*********************************************
    'Autogenerated ToolStrip button event handlers
    '*********************************************

    Private Sub FillByCustomerIDToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByCustomerIDToolStripButton.Click
        'Autogenerated code modified
        Try
            Me.CustomersTableAdapter.FillByCustomerID(Me.NorthwindDataSet.Customers, cboCustomerID.Text)
            Me.OrdersTableAdapter.FillByCustomerID(Me.NorthwindDataSet.Orders, cboCustomerID.Text)
            Me.Order_DetailsTableAdapter.FillByCustomerID(Me.NorthwindDataSet.Order_Details, cboCustomerID.Text)
            LockTextBoxes(True, True)
            EnableOrdersGrid(True, True)
            EnableOrder_DetailsGrid(True, True)
            ShowCustomerEditButtons(True)
            SaveDataToolStripButton.Visible = False
            CancelEditToolStripButton.Visible = False
            OrdersToolStrip.Visible = True
            If Len(cboCustomerID.Text) > 0 Then
                CustomerIDTextBox.Text = UCase(cboCustomerID.Text)
                blnIsNewCustomer = False
            End If
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub bindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        'Autogenerated code moved to SaveDataToolStripButton_Click
    End Sub

    Private Sub SaveDataToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveDataToolStripButton.Click
        'Modified Autogenerated code
        If Me.Validate Then
            Me.CustomersBindingSource.EndEdit()
            Me.CustomersTableAdapter.Update(Me.NorthwindDataSet.Customers)
        Else
            System.Windows.Forms.MessageBox.Show(Me, "Validation errors occurred.", "Save", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning)
        End If
    End Sub

    Private Sub btnSelectOrdersDGV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectOrdersDGV.Click
        EditOrdersToolStripButton.PerformClick()
        OrdersDataGridView.Focus()
    End Sub

    Private Sub btnSelectOrderDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectOrderDetails.Click
        EditOrdersToolStripButton.PerformClick()
        Order_DetailsDataGridView.Focus()
    End Sub
End Class