select9.frm

VB6数据库开发指南》的配套源程序

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form Form1 
   BackColor       =   &H00C0C0C0&
   Caption         =   "Chapter 3.9 Example"
   ClientHeight    =   3315
   ClientLeft      =   2070
   ClientTop       =   1650
   ClientWidth     =   7230
   BeginProperty Font 
      Name            =   "MS Sans Serif"
      Size            =   8.25
      Charset         =   0
      Weight          =   700
      Underline       =   0   'False
      Italic          =   0   'False
      Strikethrough   =   0   'False
   EndProperty
   LinkTopic       =   "Form1"
   PaletteMode     =   1  'UseZOrder
   ScaleHeight     =   3315
   ScaleWidth      =   7230
   Begin VB.CommandButton cmdClose 
      Caption         =   "&Close"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   525
      Left            =   5880
      TabIndex        =   3
      Top             =   2640
      Width           =   1245
   End
   Begin MSFlexGridLib.MSFlexGrid grdValues 
      Height          =   1935
      Left            =   90
      TabIndex        =   2
      Top             =   540
      Width           =   7065
      _ExtentX        =   12462
      _ExtentY        =   3413
      _Version        =   327680
      Cols            =   3
      FixedCols       =   0
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
   Begin VB.Label lblCount 
      Alignment       =   2  'Center
      BorderStyle     =   1  'Fixed Single
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   1590
      TabIndex        =   1
      Top             =   180
      Width           =   735
   End
   Begin VB.Label Label1 
      BackColor       =   &H00C0C0C0&
      Caption         =   "Duplicated values:"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   120
      TabIndex        =   0
      Top             =   180
      Width           =   1695
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Const BIBLIO_PATH = "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"

Private Sub cmdClose_Click()
    End
End Sub

Private Sub Form_Load()
    Dim dbfBiblio As Database
    Dim recSelect As Recordset
    Dim strSQL As String, strSubQuery As String
    Dim intCount As Integer, intGridRow As Integer

   ' Get the database name and open the database.
    Set dbfBiblio = DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
    
    ' Build the subquery, starting with its SELECT statement.
    strSubQuery = "SELECT City FROM Publishers AS Tmp " & _
        "GROUP BY City, State " & _
        "HAVING COUNT(*) > 1 AND State = Publishers.State "
    
    ' Build the SQL statement
    ' Start by designating the fields to be included in the recordset
    ' and the WHERE IN clause
    strSQL = "SELECT City, State, [Company Name] FROM Publishers " & _
        "WHERE City IN (" & strSubQuery & ") " & _
        "ORDER BY State, City"
    
    ' Run the query.
    Set recSelect = dbfBiblio.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' Make sure the query returned at least one record
    If recSelect.RecordCount > 0 Then
    
        ' Get a count of records in the recordset and display it on the form.
        recSelect.MoveLast
        intCount = recSelect.RecordCount
        lblCount.Caption = intCount
        
        ' Initialize the grid
        With grdValues
            .Rows = intCount + 1
            .ColWidth(0) = 700: .ColWidth(1) = 2000: .ColWidth(2) = 4000
            .Row = 0: .Col = 0: .Text = "State"
            .Col = 1: .Text = "City"
            .Col = 2: .Text = "Publisher"
        End With
        
        'Populate the grid
        recSelect.MoveFirst
        For intGridRow = 1 To intCount
            With grdValues
                .Row = intGridRow
                .Col = 0: .Text = recSelect![State]
                .Col = 1: .Text = recSelect![City]
                .Col = 2: .Text = recSelect![Company Name]
            End With
            recSelect.MoveNext
        Next intGridRow
    End If
End Sub