机房收费系统（一）——组合查询

对于敲完机房收费系统的我们对于组合查询应该都不在陌生了吧。想想当时我在敲之前听别人说它比较麻烦的时候，我足足停了两天才开始对这部分下手。一下手就感觉我之前在学生信息管理系统里面见过啊（根据姓名、班级、年级查询学生信息），而且它也没有什么啊，其实就是我们平常做的查询啊，就是有时多了几个条件而已。

一、分析：

组合关系选择+组合关系约束

二、设计：

1、form load 加载：

     

Private Sub Form_Load()
    Dim i As Integer
    For i = 2 To 5
        MSFlexGrid1.ColWidth(i) = 2500
    Next i
    With MSFlexGrid1
    .CellAlignment = 4
    .TextMatrix(0, 0) = "教师"
    .TextMatrix(0, 1) = "级别"
    .TextMatrix(0, 2) = "注册日期"
    .TextMatrix(0, 3) = "注册时间"
    .TextMatrix(0, 4) = "注销日期"
    .TextMatrix(0, 5) = "注销时间"
    .TextMatrix(0, 6) = "机器名"
    .TextMatrix(0, 7) = "状态"
    End With
    cmbFieldA.AddItem "教师"
    cmbFieldA.AddItem "注册日期"
    cmbFieldA.AddItem "注册时间"
    cmbFieldA.AddItem "注销日期"
    cmbFieldA.AddItem "注销时间"
    cmbFieldA.AddItem "机器名"
    cmbFieldB.AddItem "教师"
    cmbFieldB.AddItem "注册日期"
    cmbFieldB.AddItem "注册时间"
    cmbFieldB.AddItem "注销日期"
    cmbFieldB.AddItem "注销时间"
    cmbFieldB.AddItem "机器名"
    cmbFieldC.AddItem "教师"
    cmbFieldC.AddItem "注册日期"
    cmbFieldC.AddItem "注册时间"
    cmbFieldC.AddItem "注销日期"
    cmbFieldC.AddItem "注销时间"
    cmbFieldC.AddItem "机器名"
    For i = 0 To 2
        cmbOperator(i).AddItem "="
        cmbOperator(i).AddItem "<>"
        cmbOperator(i).AddItem "<"
        cmbOperator(i).AddItem ">"
    Next i
    cmbCombination(0).AddItem "与"
    cmbCombination(0).AddItem "或"
    cmbCombination(1).AddItem "与"
    cmbCombination(1).AddItem "或"
End Sub

2、组合关系选择：

     

     

    以选择两层关系为例：

    

 '选两个组合，三层关系
    If cmbFieldA.ListIndex = -1 Then         '第一条查询输入内容（字段）为空
        MsgBox "请选择字段名!", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    End If
    If cmbFieldB.ListIndex = -1 Then         '第二条查询输入内容（字段）为空
        MsgBox "请选择字段名!", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    End If
    If cmbFieldC.ListIndex = -1 Then         '第三条查询输入内容（字段）为空      
        MsgBox "请选择字段名!", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    End If

’判断操作符和要查询的内容是否为空
    For i = 0 To 2
        If cmbOperator(i).ListIndex = -1 Then
            MsgBox "请选择操作符!", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        End If
        If txtInquire(i).Text = "" Then
            MsgBox "请输入要查询的内容!", vbOKOnly + vbExclamation, "提示"
            txtInquire(0).SetFocus
            Exit Sub
        End If
    Next i

'将组合框的内容转换为与数据库字段相对应的内容
    strFieldA = GetStringA(cmbFieldA.Text)
    strFieldB = GetStringA(cmbFieldB.Text)
    strFieldC = GetStringA(cmbFieldC.Text)
    strOperatorA = Trim(cmbOperator(0).Text)
    strOperatorB = Trim(cmbOperator(1).Text)
    strOperatorC = Trim(cmbOperator(2).Text)
    strValueA = Trim(txtInquire(0).Text)
    strValueB = Trim(txtInquire(1).Text)
    strRelationA = GetStringB(cmbCombination(0).Text)
    strRelationB = GetStringB(cmbCombination(1).Text)

'根据条件查询
    strSQL1 = "select * from worklog_Info where "
    strSQL1 = strSQL1 & strFieldA & strOperatorA & "'" & strValueA & "'" & " " & strRelationA & " " & strFieldB & strOperatorB & "'" & strValueB & "'" & " " & strRelationB & " " & strFieldC & strOperatorC & "'" & strValueC & "'"
    Set objRst1 = ExecuteSQL(strSQL1, strMsgText1)
    If objRst1.EOF Then
        MsgBox "没有相应的记录，您可以重新查询!", vbOKOnly + vbExclamation, "提示"
        cmbFieldA.ListIndex = -1
        cmbFieldB.ListIndex = -1
        cmbFieldC.ListIndex = -1
        cmbOperator(0).ListIndex = -1
        cmbOperator(1).ListIndex = -1
        cmbOperator(2).ListIndex = -1
        txtInquire(0).Text = ""
        txtInquire(1).Text = ""
        txtInquire(2).Text = ""
        cmbCombination(0).ListIndex = -1
        cmbCombination(1).ListIndex = -1
        cmbFieldB.Enabled = False
        cmbFieldC.Enabled = False
        cmbOperator(1).Enabled = False
        cmbOperator(2).Enabled = False
        txtInquire(1).Enabled = False
        txtInquire(2).Enabled = False
        cmbFieldA.SetFocus
        Exit Sub
    End If
    Do While Not objRst1.EOF
        With MSFlexGrid1
            .CellAlignment = 4
            .Rows = .Rows + 1
            .TextMatrix(.Rows - 1, 0) = objRst1.Fields(1)
            .TextMatrix(.Rows - 1, 1) = objRst1.Fields(2)
            .TextMatrix(.Rows - 1, 2) = objRst1.Fields(3)
            .TextMatrix(.Rows - 1, 3) = objRst1.Fields(4)

'如果查询的内容数据中为空
            If IsNull(objRst1.Fields(5).Value) Then
                .TextMatrix(.Rows - 1, 4) = ""
            Else
            .TextMatrix(.Rows - 1, 4) = objRst1.Fields(5)
            End If
            
            If IsNull(objRst1.Fields(6).Value) Then
                .TextMatrix(.Rows - 1, 5) = ""
            Else
            .TextMatrix(.Rows - 1, 5) = objRst1.Fields(6)
            End If
            
            If IsNull(objRst1.Fields(7).Value) Then
                .TextMatrix(.Rows - 1, 6) = ""
            Else
            .TextMatrix(.Rows - 1, 6) = objRst1.Fields(7)
            End If
            
            If IsNull(objRst1.Fields(8).Value) Then
                .TextMatrix(.Rows - 1, 7) = ""
            Else
                .TextMatrix(.Rows - 1, 7) = objRst1.Fields(8)
            End If
            .ColAlignment(-1) = 2
           
           End With
        objRst1.MoveNext
    Loop
    Set objRst1 = Nothing
End Sub

 3、定义函数，使组合框中的内容与数据库中的相对应

      

Private Function GetStringA(strFieldName As String) As String
    Select Case strFieldName
        Case "教师"
            GetStringA = "UserID"
        Case "注册日期"
            GetStringA = "LoginDate"
        Case "注册时间"
            GetStringA = "LoginTime"
        Case "注销日期"
            GetStringA = "LogoutDate"
        Case "注销时间"
            GetStringA = "LogoutTime"
        Case "机器名"
            GetStringA = "computer"
    End Select
End Function
'定义一个函数将汉字的组合关系转化为计算机语言
Private Function GetStringB(strcombotext As String) As String
    Select Case strcombotext
        Case "或"
            GetStringB = "or"
        Case "与"
            GetStringB = "and"
    End Select
End Function

四、总结

不要独立的去想一件事，每件事都不是孤立的，事务之间总是有联系的，运用生活中的和之前接触过的内容去解决我们现在遇到的东西，总会简化不少的。