frmquerystudent.frm

Visual basic + sql server2000学员管理系统原代码

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "msflxgrd.ocx"
Begin VB.Form frmQueryStudent 
   Caption         =   "学员信息查询窗口"
   ClientHeight    =   5145
   ClientLeft      =   255
   ClientTop       =   345
   ClientWidth     =   9675
   Icon            =   "frmQueryStudent.frx":0000
   LinkTopic       =   "Form1"
   MDIChild        =   -1  'True
   ScaleHeight     =   5145
   ScaleWidth      =   9675
   Begin VB.CommandButton cmdShowAll 
      Caption         =   "显示全部学员"
      Height          =   375
      Left            =   7755
      Style           =   1  'Graphical
      TabIndex        =   9
      Top             =   360
      Width           =   1695
   End
   Begin VB.CommandButton cmdQuery 
      Caption         =   "查    询"
      Height          =   375
      Left            =   7755
      Style           =   1  'Graphical
      TabIndex        =   10
      Top             =   900
      Width           =   1695
   End
   Begin VB.CommandButton cmdExit 
      Caption         =   "退    出"
      Height          =   375
      Left            =   7755
      Style           =   1  'Graphical
      TabIndex        =   11
      Top             =   1440
      Width           =   1695
   End
   Begin VB.Frame famQuery 
      Caption         =   "学员信息查询"
      Height          =   1575
      Left            =   212
      TabIndex        =   0
      Top             =   240
      Width           =   7215
      Begin VB.ComboBox cmbCCode 
         Height          =   300
         Left            =   1920
         TabIndex        =   5
         Text            =   "cmbCCode"
         Top             =   480
         Width           =   1215
      End
      Begin VB.ComboBox cmbSMCode 
         Height          =   300
         Left            =   5160
         TabIndex        =   7
         Text            =   "cmbSMCode"
         Top             =   480
         Width           =   1215
      End
      Begin VB.ComboBox cmbStatus 
         Height          =   300
         Left            =   5160
         TabIndex        =   8
         Text            =   "cmbStatus"
         Top             =   960
         Width           =   1215
      End
      Begin VB.CheckBox chkCCode 
         Caption         =   "  按 中 心"
         Height          =   300
         Left            =   480
         TabIndex        =   1
         Top             =   480
         Width           =   1335
      End
      Begin VB.CheckBox chkSMCode 
         Caption         =   "  按 学 期"
         Height          =   300
         Left            =   3600
         TabIndex        =   3
         Top             =   480
         Width           =   1335
      End
      Begin VB.CheckBox chkStatus 
         Caption         =   "  按 认 证"
         Height          =   300
         Left            =   3600
         TabIndex        =   4
         Top             =   960
         Width           =   1335
      End
      Begin VB.ComboBox cmbBCode 
         Height          =   300
         Left            =   1920
         TabIndex        =   6
         Text            =   "cmbBCode"
         Top             =   960
         Width           =   1215
      End
      Begin VB.CheckBox chkBCode 
         Caption         =   "  按 班 级 "
         Height          =   300
         Left            =   480
         TabIndex        =   2
         Top             =   960
         Width           =   1335
      End
   End
   Begin MSFlexGridLib.MSFlexGrid mfgShow 
      Height          =   3000
      Left            =   120
      TabIndex        =   12
      Top             =   2040
      Width           =   9405
      _ExtentX        =   16589
      _ExtentY        =   5292
      _Version        =   393216
      AllowUserResizing=   3
   End
End
Attribute VB_Name = "frmQueryStudent"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub chkBCode_Click()
If chkBCode.Value = 1 Then
    cmbBCode.Enabled = True
ElseIf chkBCode.Value = 0 Then
    cmbBCode.Enabled = False
End If
End Sub

Private Sub chkCCode_Click()
If chkCCode.Value = 1 Then
    cmbCCode.Enabled = True
ElseIf chkCCode.Value = 0 Then
    cmbCCode.Enabled = False
End If
End Sub

Private Sub chkName_Click()
If chkName.Value = 1 Then
    cmbSName.Enabled = True
ElseIf chkName.Value = 0 Then
    cmbSName.Enabled = False
End If
End Sub

Private Sub chkSex_Click()
If chkSex.Value = 1 Then
    cmbSex.Enabled = True
ElseIf chkSex.Value = 0 Then
    cmbSex.Enabled = False
End If
End Sub

Private Sub chkSMCode_Click()
If chkSMCode.Value = 1 Then
    cmbSMCode.Enabled = True
ElseIf chkSMCode.Value = 0 Then
    cmbSMCode.Enabled = False
End If
End Sub

Private Sub chkStatus_Click()
If chkStatus.Value = 1 Then
    cmbStatus.Enabled = True
ElseIf chkStatus.Value = 0 Then
    cmbStatus.Enabled = False
End If
End Sub

Private Sub cmbCCode_Change()
If chkCCode.Value = 1 Then
    cmbCCode.Enabled = True
ElseIf chkCCode.Value = 0 Then
    cmbCCode.Enabled = False
End If
End Sub

Private Sub cmdExit_Click()
'关闭记录集,RsClose函数 见模块MdlConnection
RsClose rs
'关闭窗体
Unload Me
End Sub

Private Sub cmdQuery_Click()

Dim strTempBCode As String
Dim strTempCCode As String
Dim strTempSMCode As String
Dim strTempStatus As String

If chkBCode.Value = 0 And chkCCode.Value = 0 And chkSMCode = 0 And chkStatus.Value = 0 Then
    MsgBox "您没选择任何查询类型" & Chr(10) & "请选择至少一种！", vbOKOnly + vbExclamation, "提示"
    Exit Sub
End If

If chkBCode.Value = 1 Then
    If cmbBCode.Text = "" Then
        MsgBox "您选择了按班级查询" & Chr(10) & "请选择班级！", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    Else
        strTempBCode = "b.bcode='" & Trim(cmbBCode.Text) & "' "
    End If
End If

If chkCCode.Value = 1 Then
    If cmbCCode.Text = "" Then
        MsgBox "您选择了按中心查询" & Chr(10) & "请选择中心代码！", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    Else
        strTempCCode = " a.ccode='" & Trim(cmbCCode.Text) & "' "
    End If
End If

If chkSMCode.Value = 1 Then
    If cmbSMCode.Text = "" Then
        MsgBox "您选择了按学期查询" & Chr(10) & "请选择学期！", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    Else
        strTempSMCode = " b.smcode='" & Trim(cmbSMCode.Text) & "' "
    End If
End If

If chkStatus.Value = 1 Then
    If cmbStatus.Text = "" Then
        MsgBox "您选择了按学习状态查询" & Chr(10) & "请选择学习状态！", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    Else
        strTempStatus = " b.status='" & Trim(cmbStatus.Text) & "' "
    End If
End If

strFind = "select a.sid,a.ccode,a.sname,a.sex,a.idcard,b.bcode,b.jdate,b.status from studentinfo as a inner join studenthistory as b on a.idcard=b.idcard where "
If strTempCCode <> "" Then
    If strTempBCode <> "" Then
        If strTempSMCode <> "" Then
            If strTempStatus <> "" Then
                strFind = strFind & strTempCCode & " and " & strTempBCode & " and " & strTempSMCode & " and " & strTempStatus
            Else
                strFind = strFind & strTempCCode & " and " & strTempBCode & " and " & strTempSMCode
            End If
        Else
            If strTempStatus <> "" Then
                strFind = strFind & strTempCCode & " and " & strTempBCode & " and " & strTempStatus
            Else
                strFind = strFind & strTempCCode & " and " & strTempBCode
            End If
        End If
    Else
        If strTempSMCode <> "" Then
            If strTempStatus <> "" Then
                strFind = strFind & strTempCCode & " and " & strTempSMCode & " and " & strTempStatus
            Else
                strFind = strFind & strTempCCode & " and " & strTempSMCode
            End If
        Else
            If strTempStatus <> "" Then
                strFind = strFind & strTempCCode & " and " & strTempStatus
            Else
                strFind = strFind & strTempCCode
            End If
        End If
    End If
Else
    If strTempBCode <> "" Then
        If strTempSMCode <> "" Then
            If strTempStatus <> "" Then
                strFind = strFind & strTempBCode & " and " & strTempSMCode & " and " & strTempStatus
            Else
                strFind = strFind & strTempBCode & " and " & strTempSMCode
            End If
        Else
            If strTempStatus <> "" Then
                strFind = strFind & strTempBCode & " and " & strTempStatus
            Else
                strFind = strFind & strTempBCode
            End If
        End If
    Else
        If strTempSMCode <> "" Then
            If strTempStatus <> "" Then
                strFind = strFind & strTempSMCode & " and " & strTempStatus
            Else
                strFind = strFind & strTempSMCode
            End If
        Else
            If strTempStatus <> "" Then
                strFind = strFind & strTempStatus
            Else
                'strFind = strFind
                
            End If
        End If
    End If
End If

RsClose rs
'打开表syscenterinfo,创建记录集
RsOpen rs, con, strFind, "adcmdtext"

If rs.BOF And rs.EOF Then
    '隐藏MSFlexGrid控件
    mfgShow.Visible = False
    '缩小窗体
    Me.Width = 9700
    Me.Height = 2450
    
    MsgBox "没有符合条件的记录！", vbOKOnly + vbInformation, "查询结果"
    RsClose rs
    Exit Sub
Else
    '添MSFlexGrid控件台头
    ShowAllTitle
    
    RsClose rs
End If


End Sub

Private Sub cmdShowAll_Click()

Dim ctlcontrol As Control

For Each ctlcontrol In Controls
    If TypeOf ctlcontrol Is ComboBox Then
        ctlcontrol.Enabled = False
    End If
Next

RsClose rs
strFind = "select a.sid,a.ccode,a.sname,a.sex,a.idcard,b.bcode,b.jdate,b.status from studentinfo as a inner join studenthistory as b on a.idcard=b.idcard"
'打开表syscenterinfo,创建记录集
Debug.Print strFind
RsOpen rs, con, strFind, "adcmdtext"

'添MSFlexGrid控件台头
ShowAllTitle

RsClose rs


End Sub

Private Sub Form_Load()

'设置窗体的背景色为GetColor函数的返回值,GetColor函数 见模块MdlSystem
Me.BackColor = GetColor
Me.Width = 9700
Me.Height = 2450

'遍历窗体上的所有控件,改变颜色为GetColor函数的返回值(GetColor函数 见模块MdlSystem)
Dim ctlcontrol As Control
For Each ctlcontrol In Controls
    ctlcontrol.BackColor = GetColor
Next

'设置控件的各种属性,字体大小,对齐类型,清空文本显示
For Each ctlcontrol In Controls
    ctlcontrol.FontSize = 10
    If TypeOf ctlcontrol Is ComboBox Then
        ctlcontrol.Clear
        ctlcontrol.Enabled = False
    End If
Next

'隐藏MSFlexGrid控件
mfgShow.Visible = False

'向组合框添入数据
DisplayRefresh


End Sub

Private Sub Form_LostFocus()
'关闭记录集,RsClose函数 见模块MdlConnection
RsClose rs
End Sub

Private Sub Form_Unload(Cancel As Integer)
'关闭记录集,RsClose函数 见模块MdlConnection
RsClose rs
End Sub
Private Sub DisplayRefresh()

Dim ctlcontrol As Control

'清空组合框文本显示
For Each ctlcontrol In Controls
    If TypeOf ctlcontrol Is ComboBox Then
        ctlcontrol.Clear
    End If
Next

cmbSMCode.AddItem "S1"
cmbSMCode.AddItem "S2"
cmbSMCode.AddItem "Y2"

cmbStatus.AddItem "在读"
cmbStatus.AddItem "休学"
cmbStatus.AddItem "已获认证"
cmbStatus.AddItem "毕业未获证"

'从表syscenterinfo中取得中心代码,放入cmbccode中
RsClose rsTemp
strFind = "select ccode from syscenterinfo"
RsOpen rsTemp, con, strFind, "adcmdtext"
Do While (rsTemp.EOF = False)
        cmbCCode.AddItem rsTemp.Fields("ccode")
        rsTemp.MoveNext
Loop
RsClose rsTemp

'从表studenthistory中取得班级编号,放入cmbBCode中
RsClose rsTemp
strFind = "select distinct bcode from studenthistory "
RsOpen rsTemp, con, strFind, "adcmdtext"
Do While (rsTemp.EOF = False)
        cmbBCode.AddItem rsTemp.Fields("bcode")
        rsTemp.MoveNext
Loop
RsClose rsTemp

End Sub

Private Sub ShowAllTitle()

'计数器
Dim i As Long, j As Long
'颜色标识
Dim markColor As Boolean

'显示MSFlexGrid控件
mfgShow.Height = 3000
mfgShow.Width = 9400
mfgShow.Visible = True
frmQueryStudent.Width = 9800
frmQueryStudent.Height = 5550

'设置MSFlexGrid控件的第0行、第0列的背景色
mfgShow.BackColorFixed = GetColor
'统计记录总数
intRow = 0
Do While (Not rs.EOF)
    intRow = intRow + 1
    rs.MoveNext
Loop

'select a.sid,a.ccode,a.sname,a.sex,a.idcard,b.bcode,b.jdate,b.status from studentinfo as a inner join studenthistory as b on a.idcard=b.idcard

'MSFlexGrid控件的总行数和列数
mfgShow.Rows = intRow + 1
mfgShow.Cols = 9

'设定表格的各列宽度
mfgShow.ColWidth(0) = 400
mfgShow.ColWidth(1) = 2000
mfgShow.ColWidth(2) = 1000
mfgShow.ColWidth(3) = 1000
mfgShow.ColWidth(4) = 800
mfgShow.ColWidth(5) = 2000
mfgShow.ColWidth(6) = 1000
mfgShow.ColWidth(7) = 1500
mfgShow.ColWidth(8) = 2500


'添MSFlexGrid控件台头
mfgShow.TextMatrix(0, 1) = "学号"
mfgShow.TextMatrix(0, 2) = "中心代码"
mfgShow.TextMatrix(0, 3) = "姓名"
mfgShow.TextMatrix(0, 4) = "性别"
mfgShow.TextMatrix(0, 5) = "身份证"
mfgShow.TextMatrix(0, 6) = "班级代码"
mfgShow.TextMatrix(0, 7) = "加入日期"
mfgShow.TextMatrix(0, 8) = "学习状态"


'显示每行的行数
mfgShow.Col = 0
For i = 1 To intRow
    mfgShow.Row = i
    mfgShow.Text = i
Next

'添入数据
rs.MoveFirst
mfgShow.Row = 0
markColor = True
Do While (Not rs.EOF)
    mfgShow.Row = mfgShow.Row + 1
    For i = 1 To 8
        mfgShow.Col = i
        mfgShow.Text = rs.Fields.Item(i - 1).Value
        If markColor = True Then
            mfgShow.CellBackColor = GetColor
        Else
            mfgShow.CellBackColor = &HC0C0C0
        End If
    Next
    rs.MoveNext
    markColor = Not markColor
Loop

End Sub