joins.vb

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

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

Public Class Joins
    Private blnIsLoaded As Boolean
    Private strTop As String = "100 PERCENT"

    'Added by Wizard and modified to update the Order Details table
    Private Sub bindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bindingNavigatorSaveItem.Click
        If Me.Validate Then
            Me.Cursor = Cursors.WaitCursor
            Me.OrdersBindingSource.EndEdit()
            Me.Order_DetailsBindingSource.EndEdit()
            Me.OrdersTableAdapter.Update(Me.NorthwindDataSet.Orders)
            Me.Order_DetailsTableAdapter.Update(Me.NorthwindDataSet.Order_Details)
            Me.Cursor = Cursors.Default
        Else
            System.Windows.Forms.MessageBox.Show(Me, "Validation errors occurred.", "Save", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning)
        End If
        'Save XML documents
        Dim strXMLFile As String
        If chkSubselect.Checked Then
            strXMLFile = Application.StartupPath + "\LimitDetails.xml"
        Else
            strXMLFile = Application.StartupPath + "\AllDetails.xml"
        End If
        NorthwindDataSet.WriteXml(strXMLFile)
    End Sub

    Private Sub Joins_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        NorthwindDataSet.Clear()

        'TODO: This line of code loads data into the 'NorthwindDataSet.Products' table. You can move, or remove it, as needed.
        Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)

        'Fill method overloads in TableAdapters.vb partial class file
        Dim strOrdersSelect As String = "SELECT TOP " + strTop + " OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, " + _
            "ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, " + _
            "ShipPostalCode, ShipCountry FROM dbo.Orders ORDER BY OrderID DESC"
        Me.OrdersTableAdapter.Fill(Me.NorthwindDataSet.Orders, strOrdersSelect)
        Dim strDetailsSelect As String = "SELECT dbo.[Order Details].OrderID, dbo.[Order Details].ProductID, " + _
            "dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, " + _
            "dbo.[Order Details].Discount, dbo.Products.ProductName, " + _
            "dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice AS ListPrice " + _
            "FROM dbo.[Order Details] INNER JOIN " + _
            "dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID "
        If chkSubselect.Checked Then
            strDetailsSelect += "WHERE dbo.[Order Details].OrderID IN (SELECT TOP " + strTop + " dbo.Orders.OrderID " + _
            "FROM dbo.Orders ORDER BY dbo.Orders.OrderID DESC)"
        End If
        Me.Order_DetailsTableAdapter.Fill(Me.NorthwindDataSet.Order_Details, strDetailsSelect)
        blnIsLoaded = True
    End Sub

    Private Sub Order_DetailsDataGridView_DefaultValuesNeeded(ByVal sender As Object, _
       ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) _
       Handles Order_DetailsDataGridView.DefaultValuesNeeded
        'Set invalid default values for inserts
        With e.Row
            'Illegal Quantity
            .Cells(1).Value = 0
            'Illegal ProductID
            .Cells(2).Value = 0
            'ProductName
            .Cells(3).Value = "ProductID not selected"
            'Quantity per Unit
            .Cells(4).Value = "Not applicable"
            'ListPrice
            .Cells(5).Value = 0D
            'UnitPrice
            .Cells(6).Value = 0D
            'Discount
            .Cells(7).Value = 0D
            .ErrorText = "Default values: You must enter ProductID and Quantity."
        End With
    End Sub

    Private Sub Order_DetailsDataGridView_CellValueChanged(ByVal sender As Object, _
       ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
       Handles Order_DetailsDataGridView.CellValueChanged
        'Event handler for ProductID update
        If blnIsLoaded AndAlso e.ColumnIndex = 2 Then
            'User edited ProductID value
            With Order_DetailsDataGridView
                'Clear error icon
                .Rows(e.RowIndex).ErrorText = ""
                'Get the new ProductID value
                Dim intProductID As Integer = _
                   CType(.Rows(e.RowIndex).Cells(2).Value, Integer)
                Dim srtQuantity As Short = CType(.Rows(e.RowIndex).Cells(1).Value, Short)
                If intProductID = 0 OrElse intProductID > ProductsBindingSource.Count Then
                    'Bad ProductID value
                    .Rows(e.RowIndex).ErrorText = "ProductID value must be between " + _
                    "1 and " + ProductsBindingSource.Count.ToString
                    Return
                End If
                'Get the required data from the ProductsBindingSource
                Dim drvItem As DataRowView
                drvItem = CType(ProductsBindingSource(intProductID - 1), DataRowView)
                If CBool(drvItem.Item(9)) Then
                    'Discontinued products (5, 9, 17, 24, 28, 29, 42, 53)
                    .Rows(e.RowIndex).ErrorText = "ProductID " + intProductID.ToString + _
                    " (" + drvItem.Item(1).ToString + ") is discontinued."
                Else
                    'ProductName
                    .Rows(e.RowIndex).Cells(3).Value = drvItem.Item(1)
                    'Quantity per Unit
                    .Rows(e.RowIndex).Cells(4).Value = drvItem.Item(4)
                    'ListPrice
                    .Rows(e.RowIndex).Cells(5).Value = drvItem.Item(5)
                    'UnitPrice
                    .Rows(e.RowIndex).Cells(6).Value = drvItem.Item(5)
                    'Discount
                    .Rows(e.RowIndex).Cells(7).Value = 0D
                    If srtQuantity = 0 Then
                        .Rows(e.RowIndex).ErrorText = "Quantity of 0 is not permitted."
                    End If
                End If
            End With
        End If
    End Sub

    '*******************************
    'Number of records radio buttons
    '*******************************

    Private Sub rbAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbAll.CheckedChanged
        If rbAll.Checked Then
            strTop = "100 PERCENT"
        End If
    End Sub

    Private Sub rb500_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rb500.CheckedChanged
        If rb500.Checked Then
            strTop = "500"
        End If
    End Sub

    Private Sub rb200_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rb200.CheckedChanged
        If rb200.Checked Then
            strTop = "200"
        End If
    End Sub

    Private Sub rb100_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rb100.CheckedChanged
        If rb100.Checked Then
            strTop = "100"
        End If
    End Sub

    Private Sub btnReload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReload.Click
        'Reload grids and note time
        Me.Cursor = Cursors.WaitCursor
        Dim lngTicks As Long = Now.Ticks
        Joins_Load(Nothing, Nothing)
        Me.Cursor = Cursors.Default
        lngTicks = Now.Ticks - lngTicks
        txtTime.Text = Format(lngTicks / 10000000, "0.000")
    End Sub
End Class