frmreaderquery.frm

中小型图书馆管理系统开发

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "Msflxgrd.ocx"
Begin VB.Form frmLendInfoQuery 
   Caption         =   "借阅信息查询"
   ClientHeight    =   3870
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6885
   LinkTopic       =   "Form1"
   ScaleHeight     =   3870
   ScaleWidth      =   6885
   StartUpPosition =   3  'Windows Default
   Begin MSFlexGridLib.MSFlexGrid fgBooks 
      Height          =   1695
      Left            =   120
      TabIndex        =   8
      Top             =   2040
      Width           =   6615
      _ExtentX        =   11668
      _ExtentY        =   2990
      _Version        =   393216
   End
   Begin VB.CommandButton Command1 
      Caption         =   "退出(&X)"
      Height          =   375
      Left            =   3840
      TabIndex        =   7
      Top             =   1440
      Width           =   1335
   End
   Begin VB.CommandButton cmdQuery 
      Caption         =   "查询(&Q)"
      Height          =   375
      Left            =   1440
      TabIndex        =   6
      Top             =   1440
      Width           =   1335
   End
   Begin VB.Frame Frame2 
      Caption         =   "查询条件"
      Height          =   975
      Left            =   3360
      TabIndex        =   3
      Top             =   240
      Width           =   3255
      Begin VB.TextBox txtKey 
         Height          =   405
         Left            =   1200
         TabIndex        =   5
         Text            =   "Text1"
         Top             =   360
         Width           =   1935
      End
      Begin VB.Label Label1 
         Caption         =   "读者ID："
         Height          =   255
         Left            =   360
         TabIndex        =   4
         Top             =   480
         Width           =   975
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "查询方式"
      Height          =   975
      Left            =   240
      TabIndex        =   0
      Top             =   240
      Width           =   3015
      Begin VB.OptionButton optQuery 
         Caption         =   "查询所有已借图书"
         Height          =   255
         Index           =   1
         Left            =   240
         TabIndex        =   2
         Top             =   600
         Width           =   2415
      End
      Begin VB.OptionButton optQuery 
         Caption         =   "查询未归还图书"
         Height          =   375
         Index           =   0
         Left            =   240
         TabIndex        =   1
         Top             =   240
         Width           =   2175
      End
   End
End
Attribute VB_Name = "frmLendInfoQuery"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub cmdQuery_Click()
queryLendInfo
End Sub

Sub queryLendInfo()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim sql As String

Dim dbPath As String
Dim connStr As String

Dim i As Integer
Dim j As Integer
   
dbPath = App.Path + "\db\library.mdb"
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath

'使用Connection对象与具体的数据库文件相连接
conn.Open connStr

If txtKey.Text = "" Then
    MsgBox "查询条件不能为空，请重新输入查询条件！！", vbExclamation, "错误提示"
    txtKey.SetFocus
    Exit Sub
End If

If optQuery(0).Value = True Then
    sql = "SELECT book.bookid,book.bookname,lendtime,duetime" _
        & " from lendbook,book where lendbook.readerid=" & Trim(txtKey.Text) _
        & " AND lendbook.bookid=book.bookid" _
        & " AND lendbook.hasreturned=false"
End If
If optQuery(1).Value = True Then
    sql = "SELECT book.bookid,book.bookname,lendtime,duetime" _
       & " from lendbook,book where lendbook.readerid=" & txtKey.Text _
       & " AND lendbook.bookid=book.bookid" _
       & " AND lendbook.hasreturned=true"
End If
'执行SQL语句，建立打开记录集
rs.Open sql, conn, adOpenKeyset, adLockOptimistic

If rs.RecordCount = 0 Then

    frmQueryBook.Height = 2800

    MsgBox "没有查找满足条件的数据！", vbExclamation, "提示"
   
Else
    fgBooks.Rows = rs.RecordCount + 1
    
    fgBooks.Cols = 5
    
    'Print fgBooks.Rows
    
    '设定行高
    For i = 0 To fgBooks.Rows - 1
       fgBooks.RowHeight(i) = 280
    Next i
    
    '设定列的属性
    fgBooks.Row = 0
    
    For i = 0 To fgBooks.Cols - 1
       fgBooks.Col = i  '指定当前列为第i列
       fgBooks.FixedAlignment(i) = 4  '每列内容居中显示
       Select Case i
           Case 0
              fgBooks.ColWidth(i) = 620  '设定列宽
              fgBooks.Text = "序号"
           Case 1
              fgBooks.ColWidth(i) = 1000  '设定列宽
              fgBooks.Text = "图书编号"
           Case 2
              fgBooks.ColWidth(i) = 2600  '设定列宽
              fgBooks.Text = "书名"
           Case 3
              fgBooks.ColWidth(i) = 1100  '设定列宽
              fgBooks.Text = "借出日期"
           Case 4
              fgBooks.ColWidth(i) = 1100  '设定列宽
              fgBooks.Text = "到期日期"
        End Select
           
    Next i
       
       
    'rs.MoveFirst
    
    i = 1
    
    While (Not rs.EOF)
    
       fgBooks.Row = i
       For j = 0 To fgBooks.Cols - 1
       
        fgBooks.Col = j '设置当前为列为第j列
        fgBooks.CellAlignment = 4  '每列内容居中显示
    
        
        Select Case j
            Case 0
               fgBooks.Text = "" & i
            Case 1
               fgBooks.Text = rs.Fields("bookid")
            Case 2
               fgBooks.Text = rs.Fields("bookname")
            Case 3
               fgBooks.Text = rs.Fields("lendtime")
            Case 4
               fgBooks.Text = rs.Fields("duetime")
        End Select
      Next j
    
        rs.MoveNext
        i = i + 1
    Wend

End If

rs.Close



End Sub

Private Sub Command1_Click()
Unload Me
End Sub

Private Sub Form_Load()
txtKey = ""
End Sub