机房收费系统之组合查询(代码部分)

       最近的事情比较多,本该留出大块时间整理组合查询的代码部分,结果是分为一段一段的零散时间来整理的,最终整理的这些组合查询代码可能还是有很多冗余,本来不想拿出来的,但考虑到自己的不足就要展示给大家,这样自己才能进步嘛,所以还是厚着脸皮把我冗余的代码展示出来了,还望大家多多给予指导^_^

Private Sub cmdquery_Click()
    Dim mrc As ADODB.Recordset
    Dim txtSQL As String
    Dim MsgText As String
    Dim cmrc As ADODB.Recordset
    Dim ctxtSQL As String
    Dim cMsgText As String

    
    myflexgrid.Clear
    myflexgrid.Rows = 1
    
    '条件一查询
    If Not testtxt(comb12.Text) And Not testtxt(comb123.Text) Then
    
        If Not testtxt(combf1.Text) Then                      '判断字段一是否有内容
            MsgBox "请输入字段!", vbOKOnly, "温馨提示:"
            combf1.SetFocus
            Exit Sub
        End If
    
        If Not testtxt(combop1.Text) Then                     '判断操作符一是否有内容
            MsgBox "请输入操作符!", vbOKOnly, "温馨提示:"
            combop1.SetFocus
            Exit Sub
        End If
    
        If Not testtxt(txtquery1.Text) Then                   '判断文本框一是否有内容
            MsgBox "请输入查询内容!", vbOKOnly, "温馨提示:"
            txtquery1.SetFocus
            Exit Sub
        End If
        
        If testtxt(combf2.Text) And Not testtxt(comb12.Text) Then    '当字段二有内容时,判断组合一是否有内容
            MsgBox "请输入查询条件!", vbOKOnly, "温馨提示:"
            comb12.SetFocus
            Exit Sub
        End If
        

        '从数据库上下机信息表中查询符合字段一的内容
        txtSQL = "select * from Line_Info where " & fieldname(combf1.Text) & "" & combop1.Text & "'" & Trim(txtquery1.Text) & "' and status='正常下机'"
        Set mrc = ExecuteSQL(txtSQL, MsgText)
        
    
            If mrc.EOF Then             '如果无查询结果,则说明数据不存在
            myflexgrid.Clear
            myflexgrid.Rows = 1
            MsgBox "该条件的数据不存在!", vbOKOnly, "温馨提示:"
            Exit Sub
            End If
     
            With myflexgrid             '如果有数据,那么就逐条显示
                .Rows = 1
                .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) = "余额"
                .TextMatrix(0, 8) = "备注"
                
                Do While Not mrc.EOF
                    .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) = Trim(mrc.Fields(11))
                    .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(12))
                    .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(13))
                    mrc.MoveNext
                Loop
            End With
            mrc.Close
       End If
        
       '条件二查询
        '判断字段三有内容时,组合二是否有内容,如果没有则组合二获得焦点输入组合条件
        If testtxt(combf3.Text) And Not testtxt(comb123.Text) Then
            MsgBox "请输入查询条件!", vbOKOnly, "温馨提示:"
            comb123.SetFocus
            Exit Sub
        End If
        
        If testtxt(comb12.Text) Then                    '如果组合一有内容,那么判断对应的文本框是否为空
            If Not testtxt(combf1.Text) Then        '判断字段一是否为空
                MsgBox "请输入字段!", vbOKOnly, "温馨提示:"
                combf1.SetFocus
                Exit Sub
            End If
    
            If Not testtxt(combop1.Text) Then      '判断操作符一是否为空
                MsgBox "请输入操作符!", vbOKOnly, "温馨提示:"
                combop1.SetFocus
                Exit Sub
            End If
    
            If Not testtxt(txtquery1.Text) Then    '判断查询一是否为空
                MsgBox "请输入查询内容!", vbOKOnly, "温馨提示:"
                txtquery1.SetFocus
                Exit Sub
            End If
            If Not testtxt(combf2.Text) Then       '判断字段二是否为空
                MsgBox "请输入字段名!", vbOKOnly, "温馨提示:"
                combf2.SetFocus
                Exit Sub
            End If
            If Not testtxt(combop2.Text) Then      '判断操作符二是否为空
                MsgBox "请输入操作符!", vbOKOnly, "温馨提示:"
                combop2.SetFocus
                Exit Sub
            End If
        
            If Not testtxt(txtquery2.Text) Then    '判断查询二是否为空
                MsgBox "请输入查询内容!", vbOKOnly, "温馨提示:"
                txtquery2.SetFocus
                Exit Sub
            End If
            
            '从数据库上下机表中查询对应条件的结果
            txtSQL = "select * from Line_Info where " & fieldname(combf1.Text) & "" & combop1.Text & "'" & Trim(txtquery1.Text) & "'  and status='正常下机'" & fieldname(comb12.Text) & " " & fieldname(combf2.Text) & "" & combop2.Text & "'" & Trim(txtquery2.Text) & "' and status='正常下机'"
            Set mrc = ExecuteSQL(txtSQL, MsgText)
    
            If mrc.EOF Then
                myflexgrid.Clear
                myflexgrid.Rows = 1
                MsgBox "该条件的数据不存在!", vbOKOnly, "温馨提示:"
            Exit Sub
            End If
    
            With myflexgrid
                .Rows = 1
                .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) = "余额"
                .TextMatrix(0, 8) = "备注"
                Do While Not mrc.EOF
                    .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) = Trim(mrc.Fields(11))
                    .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(12))
                    .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(13))
                    mrc.MoveNext
                Loop
            End With
     End If
        If testtxt(comb123.Text) Then           '如果组合二有内容,判断一三是否为空
            
            If Not testtxt(combf1.Text) Then    '判断字段一是否为空
                MsgBox "请输入字段!", vbOKOnly, "温馨提示:"
                combf1.SetFocus
                Exit Sub
            End If
        
            If Not testtxt(combop1.Text) Then    '判断操作符一是否为空
                MsgBox "请输入操作符!", vbOKOnly, "温馨提示:"
                combop1.SetFocus
                Exit Sub
            End If
        
            If Not testtxt(txtquery1.Text) Then   '判断查询一是否为空
                MsgBox "请输入查询内容!", vbOKOnly, "温馨提示:"
                txtquery1.SetFocus
                Exit Sub
            End If
            If Not testtxt(combf3.Text) Then      '判断字段三是否为空
                MsgBox "请输入字段!", vbOKOnly, "温馨提示:"
                combf3.SetFocus
                Exit Sub
            End If
        
            If Not testtxt(combop3.Text) Then     '判断操作符三是否为空
                MsgBox "请输入操作符!", vbOKOnly, "温馨提示:"
                combop3.SetFocus
                Exit Sub
            End If
        
            If Not testtxt(txtquery3.Text) Then   '判断查询三是否为空
                MsgBox "请输入查询内容!", vbOKOnly, "温馨提示:"
                txtquery3.SetFocus
                Exit Sub
            End If
            
            If comb12.Text = "" Then              '组合一为空的查询

                txtSQL = "select * from Line_Info where " & fieldname(combf1.Text) & "" & combop1.Text & "'" & Trim(txtquery1.Text) & "' " & fieldname(comb123.Text) & "  " & fieldname(combf3.Text) & "" & combop3.Text & " '" & Trim(txtquery3.Text) & "' and status='正常下机'"
                Set mrc = ExecuteSQL(txtSQL, MsgText)
            End If
            
            If testtxt(comb12.Text) Then          '组合一 不为空的查询
                If comb123.Text = "与" Then
                txtSQL = "select * from Line_Info where (" & fieldname(combf1.Text) & "" & combop1.Text & "'" & Trim(txtquery1.Text) & "' and status='正常下机' " & fieldname(comb12.Text) & " " & fieldname(combf2.Text) & "" & combop2.Text & "'" & Trim(txtquery2.Text) & "' and status='正常下机')and " & fieldname(combf3.Text) & "" & combop3.Text & "'" & Trim(txtquery3.Text) & "' and status='正常下机'"
                Set mrc = ExecuteSQL(txtSQL, MsgText)
                 End If
                If comb123.Text = "或" Then
                txtSQL = "select * from Line_Info where( " & fieldname(combf1.Text) & "" & combop1.Text & "'" & Trim(txtquery1.Text) & " ' and status='正常下机' " & fieldname(comb12.Text) & " " & fieldname(combf2.Text) & "" & combop2.Text & "'" & Trim(txtquery2.Text) & "' and status='正常下机') or " & fieldname(combf3.Text) & "" & combop3.Text & "'" & Trim(txtquery3.Text) & "' and status='正常下机'"
                Set mrc = ExecuteSQL(txtSQL, MsgText)
                End If
                Else
                txtSQL = "select * from Line_Info where " & fieldname(combf1.Text) & "" & combop1.Text & "'" & Trim(txtquery1.Text) & "' " & fieldname(comb123.Text) & "  " & fieldname(combf3.Text) & "" & combop3.Text & " '" & Trim(txtquery3.Text) & "' and status='正常下机'"
                Set mrc = ExecuteSQL(txtSQL, MsgText)
            End If
                
                   
                With myflexgrid
                    .Rows = 1
                    .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) = "余额"
                    .TextMatrix(0, 8) = "备注"
                    Do While Not mrc.EOF
                        .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) = Trim(mrc.Fields(11))
                        .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(12))
                        .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(13))
                        mrc.MoveNext
                    Loop
                End With
                mrc.Close
    End If

End Sub

      以上是自己的代码思路,看着冗余的代码忽然感觉自己的脑子不会转弯(⊙o⊙)…

      当然我也看了很多小伙伴们的代码,简单的几段代码就解决了问题, 我觉着由繁到简的推导过程自己去感受一下也是不错的体验,当然后面我会站在巨人的肩膀上前行,简化组合查询,下一站继续走起^_^。

 

评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Elsa~

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值