frmmain.frm

《VB6数据库开发指南》所有的例程的源码

VERSION 5.00
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#2.0#0"; "MSCOMCTL.OCX"
Begin VB.Form frmMain 
   Caption         =   "Royalty List"
   ClientHeight    =   4680
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7920
   LinkTopic       =   "Form1"
   ScaleHeight     =   4680
   ScaleWidth      =   7920
   StartUpPosition =   3  'Windows Default
   Begin ComctlLib.ListView listWorks 
      Height          =   4215
      Left            =   2760
      TabIndex        =   1
      Top             =   360
      Width           =   5055
      _ExtentX        =   8916
      _ExtentY        =   7435
      View            =   3
      LabelEdit       =   1
      LabelWrap       =   -1  'True
      HideSelection   =   -1  'True
      _Version        =   393217
      ForeColor       =   -2147483640
      BackColor       =   -2147483643
      BorderStyle     =   1
      Appearance      =   1
      NumItems        =   2
      BeginProperty ColumnHeader(1) {BDD1F052-858B-11D1-B16A-00C0F0283628} 
         Text            =   "Author"
         Object.Width           =   3528
      EndProperty
      BeginProperty ColumnHeader(2) {BDD1F052-858B-11D1-B16A-00C0F0283628} 
         SubItemIndex    =   1
         Text            =   "Title"
         Object.Width           =   3528
      EndProperty
      _Items          =   "frmMain.frx":0000
   End
   Begin VB.ListBox lstRoyalty 
      Height          =   4155
      Left            =   120
      TabIndex        =   0
      Top             =   360
      Width           =   2535
   End
   Begin VB.Label lblWorks 
      Caption         =   "Authors and Titles"
      Height          =   735
      Left            =   2760
      TabIndex        =   3
      Top             =   120
      Width           =   1815
   End
   Begin VB.Label lblRoyalty 
      Caption         =   "Royalty"
      Height          =   495
      Left            =   120
      TabIndex        =   2
      Top             =   120
      Width           =   1215
   End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private mConn As Connection

Private Sub Form_Load()
    'open the connection
    Set mConn = New Connection
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password;" _
        + "Location=WINEMILLER;Database=pubs"
    FillRoyalty
End Sub


Private Sub Form_Unload(Cancel As Integer)
    mConn.Close
    Set mConn = Nothing
End Sub

Private Sub FillRoyalty()
    'fill the list with the royalty values
    Dim rs As Recordset
    lstRoyalty.Clear
    Set rs = mConn.Execute("select distinct royaltyper from titleauthor" _
        , , adCmdText)
    Do Until rs.EOF
        lstRoyalty.AddItem rs("royaltyper")
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
End Sub

Private Sub lstRoyalty_Click()
    'display a list of authors and titles at the selected royalty level
    Dim rs As Recordset
    Dim cmd As Command
    Dim param As adodb.Parameter
    Dim NewItem As ListItem
    
    Set cmd = New Command
    cmd.ActiveConnection = mConn
    cmd.CommandText = "AuthorTitleByRoyalty"
    cmd.CommandType = adCmdStoredProc
    
    'now build the parameter list
    
    'The stored procedure returns a true or false if there were results
    Set param = cmd.CreateParameter("Return", adBoolean _
        , adParamReturnValue)
    cmd.Parameters.Append param
    
    'The input parameter
    Set param = cmd.CreateParameter("percentage", adInteger _
        , adParamInput, , Val(lstRoyalty.Text))
    cmd.Parameters.Append param
    
    'The output parameter, the number of rows as reported by @@ROWCOUNT
    Set param = cmd.CreateParameter("numrows", adInteger, adParamOutput)
    cmd.Parameters.Append param
    
    'clear the list
    listWorks.ListItems.Clear
    
    'cmd execute generates the Recordset for us then it's
    'business as usual
    Set rs = cmd.Execute
    Do Until rs.EOF
        Set NewItem = listWorks.ListItems.Add(, , rs("au_lname") _
            & ", " & rs("au_fname"))
        NewItem.SubItems(1) = rs("title")
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set NewItem = Nothing
    'use the return value and output parameter to display a message
    If cmd("Return") = True Then
        MsgBox "This stored procedure returned " & cmd("numrows") _
            & " rows as reported by @@ROWCOUNT"
    Else
        MsgBox "No rows were found."
    End If
End Sub