select3.frm

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

VERSION 5.00
Object = "{FAEEE763-117E-101B-8933-08002B2F4F5A}#1.1#0"; "DBLIST32.OCX"
Begin VB.Form Form1 
   BackColor       =   &H00C0C0C0&
   Caption         =   "Chapter 3.3 Example"
   ClientHeight    =   3165
   ClientLeft      =   1770
   ClientTop       =   1860
   ClientWidth     =   6780
   LinkTopic       =   "Form1"
   PaletteMode     =   1  'UseZOrder
   ScaleHeight     =   3165
   ScaleWidth      =   6780
   Begin VB.TextBox txtYearPublished 
      Height          =   285
      Left            =   4380
      TabIndex        =   3
      Top             =   1020
      Width           =   915
   End
   Begin VB.CommandButton cmdClose 
      Caption         =   "&Close"
      Default         =   -1  'True
      Height          =   555
      Left            =   5340
      TabIndex        =   2
      Top             =   2520
      Width           =   1335
   End
   Begin VB.ListBox lstTitles 
      Height          =   1035
      Left            =   120
      TabIndex        =   1
      Top             =   1380
      Width           =   6555
   End
   Begin VB.Data dtaData 
      Caption         =   "dtaData"
      Connect         =   "Access"
      DatabaseName    =   """"""
      DefaultCursorType=   0  'DefaultCursor
      DefaultType     =   2  'UseODBC
      Exclusive       =   0   'False
      Height          =   345
      Left            =   120
      Options         =   0
      ReadOnly        =   0   'False
      RecordsetType   =   2  'Snapshot
      RecordSource    =   "SELECT [Company Name] FROM [Publishers]  ORDER BY [Company Name]"
      Top             =   990
      Visible         =   0   'False
      Width           =   2115
   End
   Begin MSDBCtls.DBList dblPublishers 
      Bindings        =   "Select3.frx":0000
      Height          =   840
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   5175
      _ExtentX        =   9128
      _ExtentY        =   1482
      _Version        =   327680
      MatchEntry      =   -1  'True
      BackColor       =   -2147483643
      ListField       =   "Company Name"
      BoundColumn     =   "Name"
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      BackColor       =   &H00C0C0C0&
      Caption         =   "Year Published:"
      Height          =   195
      Left            =   3210
      TabIndex        =   4
      Top             =   1065
      Width           =   1110
   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"
Dim dbfBiblio As Database

Private Sub Form_Load()
    On Error GoTo FormLoadError
        'Set the data control & load the Database object dbfBiblio.
        dtaData.DatabaseName = BIBLIO_PATH
        dtaData.Refresh
        Set dbfBiblio = DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
    On Error GoTo 0
Exit Sub

FormLoadError:
    MsgBox Err.Description, vbExclamation
    Exit Sub
End Sub

Private Sub dblPublishers_Click()
    Dim recSelect As Recordset
    Dim strSQL As String
    Dim intYearPublished As Integer
    
    On Error GoTo PublishersClickError
        'Clear the list box
        lstTitles.Clear
        
        'Confirm that the year is numeric; if so, set the intYearPublished variable
        'to its numeric value.
        If IsNumeric(txtYearPublished) Then intYearPublished = Val(txtYearPublished)
    
        'Build the SQL statement
        strSQL = "SELECT [Title], [ISBN] FROM [Titles] " & _
            "WHERE [PubID] = " & GetPubID()
    
        'If the year published selection is greater than zero, modify the SQL
        'to search for it.
        If intYearPublished > 0 Then
            strSQL = strSQL & " AND [Year Published] = " & intYearPublished
        End If
    
        'Sort the results by the ISBN number.
        strSQL = strSQL & " ORDER BY [ISBN]"
    
        'Get the recordset from our SQL statement.
        Set recSelect = dbfBiblio.OpenRecordset(strSQL, dbOpenSnapshot)
    
        'If we have obtained results, add the ISBN & Title fields to the list box.
        If recSelect.RecordCount > 0 Then
            recSelect.MoveFirst
            Do Until recSelect.EOF
                lstTitles.AddItem recSelect![ISBN] & ": " & recSelect![Title]
                recSelect.MoveNext
            Loop
        End If
    On Error GoTo 0
Exit Sub
    
PublishersClickError:
    MsgBox Err.Description, vbExclamation
    Exit Sub
End Sub

Function GetPubID() As Long
    Dim recPubID As Recordset
    Dim strSQL As String
    
    'This subquery, once constructed, selects the publisher ID
    'given a company name.
    strSQL = "SELECT [PubID] FROM [Publishers] " & _
        "WHERE [Company Name] = """ & dblPublishers.Text & """"
    
    'Construct the recordset from our SQL statement.
    Set recPubID = dbfBiblio.OpenRecordset(strSQL, dbOpenSnapshot)
    
    'If we have a record, get the ID.  If not, return zero.
    If recPubID.RecordCount > 0 Then
        GetPubID = recPubID![PubID]
    Else
        GetPubID = 0
    End If
End Function

Private Sub cmdClose_Click()
    End
End Sub