frmqueryteacher.frm

来自「数据库课程设计」· FRM 代码 · 共 279 行

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "msflxgrd.ocx"
Begin VB.Form frmQueryTeacher 
   Caption         =   "教师查询"
   ClientHeight    =   3825
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6780
   LinkTopic       =   "Form1"
   ScaleHeight     =   3825
   ScaleWidth      =   6780
   StartUpPosition =   3  'Windows Default
   Begin MSFlexGridLib.MSFlexGrid fgTeacher 
      Height          =   1935
      Left            =   120
      TabIndex        =   8
      Top             =   1800
      Width           =   6615
      _ExtentX        =   11668
      _ExtentY        =   3413
      _Version        =   393216
   End
   Begin VB.Frame Frame1 
      Caption         =   "查询方式"
      Height          =   1575
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   6615
      Begin VB.CommandButton cmdCancel 
         Caption         =   "返回(&C)"
         Height          =   375
         Left            =   4320
         TabIndex        =   7
         Top             =   960
         Width           =   1215
      End
      Begin VB.CommandButton cmdQuery 
         Caption         =   "查询(&Q)"
         Height          =   375
         Left            =   4320
         TabIndex        =   6
         Top             =   360
         Width           =   1215
      End
      Begin VB.OptionButton optQuery 
         Caption         =   "查询全部"
         Height          =   375
         Index           =   2
         Left            =   240
         TabIndex        =   5
         Top             =   1080
         Width           =   2175
      End
      Begin VB.ComboBox cboTitle 
         Height          =   315
         Left            =   1560
         TabIndex        =   4
         Top             =   720
         Width           =   1695
      End
      Begin VB.OptionButton optQuery 
         Caption         =   "按职称查询"
         Height          =   375
         Index           =   1
         Left            =   240
         TabIndex        =   3
         Top             =   720
         Width           =   1215
      End
      Begin VB.ComboBox cboDept 
         Height          =   315
         Left            =   1560
         TabIndex        =   2
         Top             =   360
         Width           =   1695
      End
      Begin VB.OptionButton optQuery 
         Caption         =   "按部门查询"
         Height          =   375
         Index           =   0
         Left            =   240
         TabIndex        =   1
         Top             =   360
         Width           =   1455
      End
   End
End
Attribute VB_Name = "frmQueryTeacher"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public sqlStr As String
Public msgText As String

Private Sub queryTeacher()
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim j As Integer
    
    If optQuery(0).Value = True Then
        sqlStr = "select * from teacherInfo where department='" & cboDept.Text & "'"
    End If
    
    If optQuery(1).Value = True Then
        sqlStr = "select * from teacherInfo where title='" & cboTitle.Text & "'"
    End If
    
    If optQuery(2).Value = True Then
        sqlStr = "select * from teacherInfo"
    End If
    
    Set rs = ExecuteSQL(sqlStr, msgText)
    
    If rs.RecordCount = 0 Then
    
        MsgBox "没有查找满足条件的数据！", vbExclamation, "提示"
        fgTeacher.Rows = 1
       
    Else
        
        fgTeacher.Rows = rs.RecordCount + 1
        
        fgTeacher.Cols = 8
        
        
        '设定行高
        For i = 0 To fgTeacher.Rows - 1
           fgTeacher.RowHeight(i) = 280
        Next i
        
        '设定列的属性
        fgTeacher.Row = 0
        
        For i = 0 To fgTeacher.Cols - 1
           fgTeacher.Col = i  '指定当前列为第i列
           fgTeacher.FixedAlignment(i) = 4  '每列内容居中显示
           Select Case i
               Case 0
                  fgTeacher.ColWidth(i) = 800  '设定列宽
                  fgTeacher.Text = "编号"
               Case 1
                  fgTeacher.ColWidth(i) = 800  '设定列宽
                  fgTeacher.Text = "姓名"
               Case 2
                  fgTeacher.ColWidth(i) = 1000  '设定列宽
                  fgTeacher.Text = "出生日期"
               Case 3
                  fgTeacher.ColWidth(i) = 500  '设定列宽
                  fgTeacher.Text = "性别"
               Case 4
                  fgTeacher.ColWidth(i) = 1000  '设定列宽
                  fgTeacher.Text = "地址"
               Case 5
                  fgTeacher.ColWidth(i) = 1000  '设定列宽
                  fgTeacher.Text = "电话"
               Case 6
                  fgTeacher.ColWidth(i) = 700  '设定列宽
                  fgTeacher.Text = "职称"
               Case 7
                  fgTeacher.ColWidth(i) = 1000  '设定列宽
                  fgTeacher.Text = "所属部门"
            
            End Select
               
        Next i
           
           
        'rs.MoveFirst
        
        i = 1
        
        While (Not rs.EOF)
        
           fgTeacher.Row = i
           For j = 0 To fgTeacher.Cols - 1
           
            fgTeacher.Col = j '设置当前为列为第j列
            fgTeacher.CellAlignment = 4  '每列内容居中显示
        
            
            Select Case j
               Case 0
                  fgTeacher.Text = rs.Fields("teacher_no")
               Case 1
                  fgTeacher.Text = rs.Fields("name")
               Case 2
                  fgTeacher.Text = rs.Fields("birthdate")
               Case 3
                  fgTeacher.Text = rs.Fields("sex")
               Case 4
                  fgTeacher.Text = rs.Fields("address")
               Case 5
                  fgTeacher.Text = rs.Fields("telno")
               Case 6
                  fgTeacher.Text = rs.Fields("title")
               Case 7
                  fgTeacher.Text = rs.Fields("department")
            End Select
          Next j
        
            rs.MoveNext
            i = i + 1
        Wend
    
    End If
    
    rs.Close

End Sub

Sub initDepartment()
Dim rstDept As ADODB.Recordset

    '从数据库中读取所有部门并添加到组合列表框中
    sqlStr = "select dept_name from departmentInfo"
    Set rstDept = ExecuteSQL(sqlStr, msgText)
    cboDept.Clear
    
    If Not rstDept.EOF Then
        
            Do While Not rstDept.EOF
                cboDept.AddItem Trim(rstDept.Fields(0))
                rstDept.MoveNext
            Loop
            cboDept.ListIndex = 0
        
    Else
        MsgBox "没有部门信息，请添加部门！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstDept.Close

End Sub

Sub initTitles()
Dim rstTitle As ADODB.Recordset

    '从数据库中读取所有职称并添加到组合列表框中
    sqlStr = "select DISTINCT title from teacherInfo"
    Set rstTitle = ExecuteSQL(sqlStr, msgText)
    cboTitle.Clear
    
    If Not rstTitle.EOF Then
        
            Do While Not rstTitle.EOF
                cboTitle.AddItem Trim(rstTitle.Fields(0))
                rstTitle.MoveNext
            Loop
            cboTitle.ListIndex = 0
        
    Else
        MsgBox "没有教师信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstTitle.Close

End Sub

Private Sub cmdQuery_Click()
queryTeacher
End Sub

Private Sub Form_Load()

'窗体居中显示
Me.Top = (Screen.Height - Me.Height) \ 2
Me.Left = (Screen.Width - Me.Width) \ 2

initDepartment
initTitles

End Sub