ordersform.vb

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

		ShipRegionTextBox.ReadOnly = Not blnEnabled
		ShipPostalCodeTextBox.ReadOnly = Not blnEnabled
		ShipCountryTextBox.ReadOnly = Not blnEnabled
		Order_DetailsDataGridView.Enabled = blnEnabled
		Order_DetailsDataGridView.AllowUserToAddRows = blnEnabled
		Order_DetailsDataGridView1.Enabled = blnEnabled
		Order_DetailsDataGridView1.AllowUserToAddRows = blnEnabled
	End Sub
#End Region

#Region "Utility and Sample Data Procedures"
	'**********************************
	'Utility and sample data procedures
	'**********************************

	Private Sub SampleNewCustomerData()
		'Bogus values
		CompanyNameTextBox.Text = "Bogus Systems, Inc."
		ContactNameTextBox.Text = "Joe Bogus"
		ContactTitleTextBox.Text = "President and CEO"
		AddressTextBox.Text = "1234 Broadway"
		CityTextBox.Text = "Oakland"
		RegionTextBox.Text = "CA"
		PostalCodeTextBox.Text = "94612-1234"
		CountryTextBox.Text = "USA"
		PhoneTextBox.Text = "[phone]"
		FaxTextBox.Text = "[phone]"
    End Sub

    Private Function UpdateBaseTables(ByVal blnTest As Boolean) As Boolean
        'Returns True if updates succeed or customer cancels form close
        'Returns False if updates fail or DataSet has no updates

        If NorthwindDataSet.HasChanges Then
            'Customers is the parent table and Deletions aren't allowed
            'Thus it's not necessary to insert or update by DataRowState
            'However, test mode needs to detect the number of changes made
            Dim NewCustomers As NorthwindDataSet.CustomersDataTable = _
               CType(NorthwindDataSet.Customers.GetChanges(DataRowState.Added), _
               NorthwindDataSet.CustomersDataTable)

            Dim ModCustomers As NorthwindDataSet.CustomersDataTable = _
               CType(NorthwindDataSet.Customers.GetChanges(DataRowState.Modified), _
               NorthwindDataSet.CustomersDataTable)

            'Orders (inserts, updates, and Deletions)
            Dim DelOrders As NorthwindDataSet.OrdersDataTable = _
               CType(NorthwindDataSet.Orders.GetChanges(DataRowState.Deleted), _
               NorthwindDataSet.OrdersDataTable)

            Dim NewOrders As NorthwindDataSet.OrdersDataTable = _
               CType(NorthwindDataSet.Orders.GetChanges(DataRowState.Added), _
               NorthwindDataSet.OrdersDataTable)

            Dim ModOrders As NorthwindDataSet.OrdersDataTable = _
               CType(NorthwindDataSet.Orders.GetChanges(DataRowState.Modified), _
               NorthwindDataSet.OrdersDataTable)

            'Order Details (inserts, updates, and Deletions)
            Dim DelDetails As NorthwindDataSet.Order_DetailsDataTable = _
               CType(NorthwindDataSet.Order_Details.GetChanges(DataRowState.Deleted), _
               NorthwindDataSet.Order_DetailsDataTable)

            Dim NewDetails As NorthwindDataSet.Order_DetailsDataTable = _
               CType(NorthwindDataSet.Order_Details.GetChanges(DataRowState.Added), _
               NorthwindDataSet.Order_DetailsDataTable)

            Dim ModDetails As NorthwindDataSet.Order_DetailsDataTable = _
               CType(NorthwindDataSet.Order_Details.GetChanges(DataRowState.Modified), _
               NorthwindDataSet.Order_DetailsDataTable)

            Dim dsChanges As DataSet = Nothing
            Dim intChanges As Integer
            If blnTest Then
                'Create a dataset of the changes
                dsChanges = New DataSet
                dsChanges.DataSetName = "dsChanges"
            End If
            Try
                '1. Delete Order Details records
                If Not DelDetails Is Nothing Then
                    If blnTest Then
                        DelDetails.TableName = "DelDetails"
                        dsChanges.Tables.Add(DelDetails)
                    Else
                        Order_DetailsTableAdapter.Update(DelDetails)
                    End If
                    intChanges += DelDetails.Count
                End If

                '2. Delete Orders records
                If Not DelOrders Is Nothing Then
                    DelOrders.TableName = "DelOrders"
                    If blnTest Then
                        dsChanges.Tables.Add(DelOrders)
                        intChanges += DelOrders.Count
                    Else
                        OrdersTableAdapter.Update(DelOrders)
                    End If
                    intChanges += 1
                End If

                '3. Insert New Customers records
                If Not NewCustomers Is Nothing Then
                    If blnTest Then
                        NewCustomers.TableName = "NewCustomers"
                        dsChanges.Tables.Add(NewCustomers)
                    Else
                        CustomersTableAdapter.Update(NewCustomers)
                    End If
                    intChanges += NewCustomers.Count
                End If

                '4. Update Modified Customers records
                If Not ModCustomers Is Nothing Then
                    If blnTest Then
                        ModCustomers.TableName = "ModCustomers"
                        dsChanges.Tables.Add(ModCustomers)
                    Else
                        CustomersTableAdapter.Update(ModCustomers)
                    End If
                    intChanges += ModCustomers.Count
                End If

                '5. Insert New Orders records
                If Not NewOrders Is Nothing Then
                    If blnTest Then
                        dsChanges.Tables.Add(NewOrders)
                        NewOrders.TableName = "NewOrders"
                    Else
                        OrdersTableAdapter.Update(NewOrders)
                    End If
                    intChanges += NewOrders.Count
                End If

                '6. Update Modified Orders records
                If Not ModOrders Is Nothing Then
                    If blnTest Then
                        dsChanges.Tables.Add(ModOrders)
                        ModOrders.TableName = "ModOrders"
                    Else
                        OrdersTableAdapter.Update(ModOrders)
                    End If
                    intChanges += ModOrders.Count
                End If

                '7. Insert New Order Details records
                If Not NewDetails Is Nothing Then
                    If blnTest Then
                        dsChanges.Tables.Add(NewDetails)
                        NewDetails.TableName = "NewDetails"
                    Else
                        Order_DetailsTableAdapter.Update(NewDetails)
                    End If
                    intChanges += NewDetails.Count
                End If

                '8. Update Modified Order Details records
                If Not ModDetails Is Nothing Then
                    If blnTest Then
                        dsChanges.Tables.Add(ModDetails)
                        ModDetails.TableName = "ModDetails"
                    Else
                        Order_DetailsTableAdapter.Update(ModDetails)
                    End If
                    intChanges += ModDetails.Count
                End If
                If blnTest Then
                    'Warn the user if changes are pending
                    Dim strFile As String = Application.StartupPath + _
                       "\DataSetUpdategram.xml"
                    If intChanges > 0 Then
                        'Save the updates as a DiffGram
                        dsChanges.WriteXml(strFile, XmlWriteMode.DiffGram)
                        Dim strMsg As String = "You have update(s) pending to " + _
                         intChanges.ToString + " records(s)." + vbCrLf + vbCrLf + _
                         "Are you sure you want to quit without " + _
                         " saving these updates to the Northwind database?"
                        If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, _
                         "Pending Updates Not Saved") = MsgBoxResult.Yes Then
                            Return False
                        Else
                            Return True
                        End If
                    Else
                        If File.Exists(strFile) Then
                            File.Delete(strFile)
                        End If
                    End If
                End If
                Return True
            Catch excConcur As DBConcurrencyException
                MsgBox(excConcur.Message, MsgBoxStyle.Exclamation, "Concurrency Exception")
            Catch exc As Exception
                MsgBox(exc.Message + exc.StackTrace, MsgBoxStyle.Exclamation, _
                 "Database Updates Failed")
                Return False
            Finally
                'Dispose the new objects now
                If Not dsChanges Is Nothing Then
                    dsChanges.Dispose()
                End If
                If Not NewCustomers Is Nothing Then
                    NewCustomers.Dispose()
                End If
                If Not ModCustomers Is Nothing Then
                    ModCustomers.Dispose()
                End If
                If Not DelOrders Is Nothing Then
                    DelOrders.Dispose()
                End If
                If Not NewOrders Is Nothing Then
                    NewOrders.Dispose()
                End If
                If Not ModOrders Is Nothing Then
                    ModOrders.Dispose()
                End If
                If Not DelDetails Is Nothing Then
                    DelDetails.Dispose()
                End If
                If Not NewDetails Is Nothing Then
                    NewDetails.Dispose()
                End If
                If Not ModDetails Is Nothing Then
                    ModDetails.Dispose()
                End If
            End Try
        Else
            If Not blnTest Then
                MsgBox("There are no data updates to save.", MsgBoxStyle.Information, _
                   "Save Requested Without Updates")
            End If
            Return False
        End If
    End Function

    Private Function CheckServerForCustID(ByVal strCustID As String) As Boolean
        'Run a fast duplicate check on the server
        'Called by ContactNameTextBox_GotFocus in OrdersForm.vb
        Dim cnNwind As SqlConnection = Nothing
        Try
            Dim strConn As String = MySettings.Default.NorthwindConnection.ToString
            cnNwind = New SqlConnection(strConn)
            Dim strSQL As String = "SELECT COUNT(CustomerID) FROM Customers " + _
               "WHERE CustomerID = '" + strCustID + "'"
            Dim cmCustID As New SqlCommand(strSQL, cnNwind)
            cnNwind.Open()
            Dim intCount As Integer = CInt(cmCustID.ExecuteScalar)
            cnNwind.Close()
            If intCount > 0 Then
                'Duplicate found
                Return True
            Else
                Return False
            End If
        Catch exc As Exception
            MsgBox(exc.Message + exc.StackTrace, MsgBoxStyle.Exclamation, _
               "Test Duplicates Error")
            Return False
        Finally
            If Not cnNwind Is Nothing Then
                If Not cnNwind.State = ConnectionState.Closed Then
                    cnNwind.Close()
                End If
                cnNwind.Dispose()
            End If
        End Try
    End Function

    Private Sub GetOrderSubtotal()
        'Calculate and display order subtotal on F2 and CellValueChanged
        With Order_DetailsDataGridView1
            Dim decSubtotal As Decimal
            Dim intCtr As Integer
            For intCtr = 0 To .Rows.Count - 1
                decSubtotal += CDec(.Rows(intCtr).Cells(5).Value)
            Next
            txtSubtotal.Text = Format(decSubtotal, "$#,##0.00")
        End With
    End Sub

    Private Sub GetUnitPrice(ByVal intRow As Integer, ByVal intCol As Integer, _
       ByRef dgvDetails As DataGridView)
        'Test the ProductID value for duplication
        'Update the unit price, if not a duplicate
        Try
            If intCol = 2 Then
                'ProductID combo box change
                Dim intProdID As Integer = CInt(dgvDetails.Rows(intRow).Cells(2).Value)
                Dim decPrice As Decimal
                Dim intRowCtr As Integer
                Dim rowProd As DataRow
                Dim strName As String = Nothing

                'Test for duplicate ProductID (primary key violation)
                Dim intDups As Integer
                With dgvDetails
                    Dim intCount As Integer = .Rows.Count
                    For intRowCtr = 0 To .Rows.Count - 1
                        If CInt(.Rows(intRowCtr).Cells(2).Value) = intProdID Then
                            intDups += 1
                            If intDups > 1 Then
                                Exit For
                            End If
                        End If
                    Next intRowCtr
                End With
                If intDups > 1 Then
                    Dim strMsg As String = "ProductID " + intProdID.ToString + _
                     " has been added previously to this order. " + vbCrLf + vbCrLf + _
                     "Please select a different product or press Esc to cancel the edit."
                    MsgBox(strMsg, MsgBoxStyle.Exclamation, strTitle)
                    Return
                End If

                'Search the DataTable for the ProductID and update UnitPrice
                With dsLookups.Tables(3)
                    For intRowCtr = 0 To .Rows.Count - 1
                        rowProd = .Rows(intRowCtr)
                        If CInt(rowProd.Item(0)) = intProdID Then
                            decPrice = CDec(rowProd.Item(2))
                            'Change UnitPrice value
                            With Order_DetailsDataGridView1
                                .Rows(intRow).Cells(3).Value = decPrice
                                Exit For
                            End With
                        End If
                    Next intRowCtr
                End With
            End If
        Catch exc As Exception
            MsgBox(exc.Message + exc.StackTrace, , exc.Source)
        End Try
    End Sub

    Private Sub btnTestUpdates_Click(ByVal sender As Object, _
       ByVal e As System.EventArgs) Handles btnTestUpdates.Click
        'Temporary button for testing
        Dim strMsg As String = Nothing
        If UpdateBaseTables(True) Then
            strMsg = "Base table update test succeeded. Open DataSetUpdategram.xml to view proposed changes."
        Else
            strMsg = "Base table update test failed or there are no updates to process. " + vbCrLf + vbCrLf + _
            "Don't click the Customer or Orders Save buttons before testing the UpdateBaseTable function."
        End If
        MsgBox(strMsg, MsgBoxStyle.Information, "Update Base Tables Test")
    End Sub

#End Region
End Class