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

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

一、分析：

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

二、设计：

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 = NothingEnd 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 SelectEnd Function'定义一个函数将汉字的组合关系转化为计算机语言Private Function GetStringB(strcombotext As String) As String    Select Case strcombotext        Case "或"            GetStringB = "or"        Case "与"            GetStringB = "and"    End SelectEnd Function

四、总结

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