机房收费系统-组合查询

前言：

    上一篇是对机房收费系统的简单功能窗体的介绍，这次给大家介绍一下组合查询。刚敲组合查询的时候，别的人就会说好难好难，每每听到这些负能量的话，我就会暗自和自己说，我没有必要在没真正去做某件事之前，就去定义一件事情的难易，带着我必定会轻松解决的心理，结果真的就是轻松解决了。下面呢我就以学生上机统计信息查询为例，向大家揭开组合查询的神秘面纱！

内容：

    1、理清思路：

     

  2、代码技巧：

        这次敲机房收费系统才明白一个道理，我们的代码不能仅仅是实现一个功能，也要考虑代码的时间和空间复杂度，这里就要提到变量作用。组合查询这点表现得很明显，典型的就是文本框文本与数据库字段的转换，这样可以很大程度减少代码的复杂度，看起来也特别的清晰明了！

Select Case Combo1.Text                 '文本框文本与数据库字段的转换     Case "卡号"      strcon1 = "cardno"     Case "姓名"      strcon1 = "studentname"     Case "上机日期"      strcon1 = "ondate"     Case "上机时间"      strcon1 = "ontime"     Case "下机日期"      strcon1 = "offdate"     Case "下机时间"      strcon1 = "offtime"     Case "消费金额"      strcon1 = "consume"     Case "余额"      strcon1 = "cash"     Case "备注"      strcon1 = "status"   End Select

  3、代码实现：这里需要注意的是只有combo7有文本，第二行的各个框才可用。combo8有文本，第三行的各个框才可用。也就是要用到第二行，第二行的enabled值为true。用到第三行，第三行的enabled的值为true。

Private Sub Command2_Click()   Dim mrc As ADODB.Recordset   Dim txtsql As String   Dim Msgtext As String   Dim strcon1, strcon2, strcon3, strcon4, strcon5   Select Case Combo1.Text                 '文本框文本与数据库字段的转换     Case "卡号"      strcon1 = "cardno"     Case "姓名"      strcon1 = "studentname"     Case "上机日期"      strcon1 = "ondate"     Case "上机时间"      strcon1 = "ontime"     Case "下机日期"      strcon1 = "offdate"     Case "下机时间"      strcon1 = "offtime"     Case "消费金额"      strcon1 = "consume"     Case "余额"      strcon1 = "cash"     Case "备注"      strcon1 = "status"   End Select      If Not Testtxt(Combo1.Text) Then                   '判断combo是否为空     MsgBox "请选择字段名", vbOKOnly + vbExclamation, "警告"     Combo1.SetFocus   Else     If Not Testtxt(Combo4.Text) Then                  '判断combo是否为空       MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"       Combo4.SetFocus     Else       If Not Testtxt(Text1.Text) Then                 '判断combo是否为空         MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "警告"         Text1.SetFocus       Else                  '第一行条件         txtsql = "select * from Line_Info where " & strcon1 & Combo4.Text & "'" & Trim(Text1.Text) & "'"         Set mrc = ExecuteSQL(txtsql, Msgtext)                  If mrc.EOF = True Then                           '判断是否有对应条件的记录           MsgBox "此条件没有上机记录！", vbOKOnly + vbExclamation, "警告"           Combo1.Text = ""           Combo4.Text = ""           Text1.Text = ""           MSFlexGrid1.Clear                With MSFlexGrid1                   '清除后显示表头                .CellAlignment = 4                .Rows = 1                .TextMatrix(0, 0) = "卡号"                .TextMatrix(0, 1) = "姓名"                .TextMatrix(0, 2) = "上机日期"                .TextMatrix(0, 3) = "上机时间"                .TextMatrix(0, 4) = "下机日期"                .TextMatrix(0, 5) = "下机时间"                .TextMatrix(0, 6) = "消费金额"                .TextMatrix(0, 7) = "余额"                .TextMatrix(0, 8) = "备注"            End With           Exit Sub        End If      End If    End If               If Not Testtxt(Combo7.Text) Then                     '如果combo7没有文本则直接跳到case1      GoTo case1    Else                                                 '如果有则判断第二行的条件     If Not Testtxt(Combo2.Text) Then                    '判断combo框是否为空       MsgBox "请选择字段名", vbOKOnly + vbExclamation, "警告"       Combo2.SetFocus     Else       If Not Testtxt(Combo5.Text) Then         MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"         Combo5.SetFocus       Else         If Not Testtxt(Text2.Text) Then           MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "警告"           Text2.SetFocus         Else           Select Case Combo7.Text             Case "或"              strcon2 = "or "             Case "与"              strcon2 = "and "           End Select                     Select Case Combo2.Text                 '文本框文本与数据库字段的转换              Case "卡号"               strcon3 = "cardno"              Case "姓名"               strcon3 = "studentname"              Case "上机日期"               strcon3 = "ondate"              Case "上机时间"               strcon3 = "ontime"              Case "下机日期"               strcon3 = "offdate"              Case "下机时间"               strcon3 = "offtime"              Case "消费金额"               strcon3 = "consume"              Case "余额"               strcon3 = "cash"              Case "备注"               strcon3 = "status"         End Select                       txtsql = txtsql & strcon2 & " " & strcon3 & Combo5.Text & "'" & Trim(Text2.Text) & "'"             Set mrc = ExecuteSQL(txtsql, Msgtext)                          If mrc.EOF = True Then               MsgBox "没有该条件下的上机记录", vbOKOnly + vbExclamation, "警告"               Exit Sub               Combo1.Text = ""               Combo4.Text = ""               Text1.Text = ""               Combo2.Text = ""               Combo5.Text = ""               Text2.Text = ""               Combo7.Text = ""                              With MSFlexGrid1                   '清除后显示表头                .CellAlignment = 4                .Rows = 1                .TextMatrix(0, 0) = "卡号"                .TextMatrix(0, 1) = "姓名"                .TextMatrix(0, 2) = "上机日期"                .TextMatrix(0, 3) = "上机时间"                .TextMatrix(0, 4) = "下机日期"                .TextMatrix(0, 5) = "下机时间"                .TextMatrix(0, 6) = "消费金额"                .TextMatrix(0, 7) = "余额"                .TextMatrix(0, 8) = "备注"                              End With             End If            End If          End If        End If      End If            If Combo8.Text = "" Then                          '如果combo8没有文本则直接跳到case1        GoTo case1      Else           If Not Testtxt(Combo3.Text) Then             MsgBox "请选择字段名", vbOKOnly + vbExclamation, "警告"             Combo2.SetFocus           Else            If Not Testtxt(Combo6.Text) Then              MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"              Combo5.SetFocus            Else              If Not Testtxt(Text3.Text) Then                MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "警告"                Text2.SetFocus              Else                  Select Case Combo8.Text                   Case "或"                    strcon4 = "or "                   Case "与"                    strcon4 = "and "                  End Select                              Select Case Combo3.Text                   Case "卡号"                    strcon5 = "cardno"                   Case "姓名"                    strcon5 = "studentname"                   Case "上机日期"                    strcon5 = "ondate"                   Case "上机时间"                    strcon5 = "ontime"                   Case "下机日期"                    strcon5 = "offdate"                   Case "下机时间"                    strcon5 = "offtime"                   Case "消费金额"                    strcon5 = "consume"                   Case "余额"                    strcon5 = "cash"                   Case "备注"                    strcon5 = "status"                End Select                   txtsql = txtsql & " " & strcon4 & strcon5 & Combo6.Text & "'" & Trim(Text3.Text) & "'"                   Set mrc = ExecuteSQL(txtsql, Msgtext)                                      If mrc.EOF = True Then                       MsgBox "没有该条件下的上机记录！", vbOKOnly + vbExclamation, "警告"                    Combo1.Text = ""                    Combo4.Text = ""                    Text1.Text = ""                    Combo2.Text = ""                    Combo5.Text = ""                    Text2.Text = ""                    Combo7.Text = ""                    Combo3.Text = ""                    Combo6.Text = ""                    Combo8.Text = ""                    Text3.Text = ""                                        With MSFlexGrid1                   '清除后显示表头                        .CellAlignment = 4                        .Rows = 1                        .TextMatrix(0, 0) = "卡号"                        .TextMatrix(0, 1) = "姓名"                        .TextMatrix(0, 2) = "上机日期"                        .TextMatrix(0, 3) = "上机时间"                        .TextMatrix(0, 4) = "下机日期"                        .TextMatrix(0, 5) = "下机时间"                        .TextMatrix(0, 6) = "消费金额"                        .TextMatrix(0, 7) = "余额"                        .TextMatrix(0, 8) = "备注"                   End With                    Exit Sub                  End If                End If              End If            End If          End If                  case1:   With MSFlexGrid1                   '窗体加载时显示表头        .CellAlignment = 4        .Rows = 1        .TextMatrix(0, 0) = "卡号"        .TextMatrix(0, 1) = "姓名"        .TextMatrix(0, 2) = "上机日期"        .TextMatrix(0, 3) = "上机时间"        .TextMatrix(0, 4) = "下机日期"        .TextMatrix(0, 5) = "下机时间"        .TextMatrix(0, 6) = "消费金额"        .TextMatrix(0, 7) = "余额"        .TextMatrix(0, 8) = "备注"  Do While Not mrc.EOF                             'MSFlexGrid1显示数据        .Rows = .Rows + 1        .CellAlignment = 4        .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1) & "")        .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3) & "")        .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6) & "")        .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7) & "")        .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8) & "")        .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(9) & "")        .TextMatrix(.Rows - 1, 6) = Format(mrc.Fields(10), "0.00") & ""        .TextMatrix(.Rows - 1, 7) = Format(mrc.Fields(12), "0.00") & ""        .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(13) & "")        mrc.MoveNext  Loop  End WithEnd IfEnd Sub

总结：

    组合查询其实没有大家想象中的那么难，很多代码都是重复的，还是那句话，只要我们理清思路就OK了！

    

                                                                                                                            感谢您的阅读！