机房收费系统之组合查询

约定不等于承诺〃 2021-09-23 19:32 374阅读 0赞

都说组合查询是机房收费系统的一大难点，但是你真正开始去敲了，发现它真的不难。基本上敲完一个，其他两个也就出来了。不要自己吓自己，给自己压力。

所有的伟大只源于一个勇敢地开始。阅读本文需要3分钟，三分钟之后你就会把组合查询掌握。

### 一、思维导图 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb21zaW5n_size_16_color_FFFFFF_t_70][]

### 二、代码展示 ###

Private Sub cmdInquiry_Click()
        
        Dim txtsql As String
        Dim Msgtext As String
        Dim mrc As ADODB.Recordset
        
        '判断字段名是否为空
        If Trim(comboField(0)) = "" Then
            MsgBox "请选择字段名", 64, "温馨提示"
            comboField(0).SetFocus
            Exit Sub
        End If
            
        '判断操作符是否为空
        If Trim(comboOpSign(0)) = "" Then
            MsgBox "请选择操作符", 64, "温馨提示"
            comboOpSign(0).SetFocus
            Exit Sub
        End If
        
        '判断操作符是否为空
        If Trim(txtInquiryCont1) = "" Then
            MsgBox "请输入要查询的内容", 64, "温馨提示"
            txtInquiryCont1.SetFocus
            Exit Sub
        End If
        
         Rem;若是选择组合关系
        If Trim(comboCombRela(0)) <> "" Then
            
                 '判断字段名是否为空
            If Trim(comboField(1)) = "" Then
                MsgBox "请选择字段名", 64, "温馨提示"
                comboField(1).SetFocus
                Exit Sub
            End If
                
            '判断操作符是否为空
            If Trim(comboOpSign(1)) = "" Then
                MsgBox "请选择操作符", 64, "温馨提示"
                comboOpSign(1).SetFocus
                Exit Sub
            End If
            
            '判断操作符是否为空
            If Trim(txtInquiryCont2) = "" Then
                MsgBox "请输入要查询的内容", 64, "温馨提示"
                txtInquiryCont2.SetFocus
                Exit Sub
            End If
        
        End If
            
        Rem;若是选择组合关系
        If Trim(comboCombRela(1)) <> "" Then
            
                 '判断字段名是否为空
            If Trim(comboField(2)) = "" Then
                MsgBox "请选择字段名", 64, "温馨提示"
                comboField(2).SetFocus
                Exit Sub
            End If
                
            '判断操作符是否为空
            If Trim(comboOpSign(2)) = "" Then
                MsgBox "请选择操作符", 64, "温馨提示"
                comboOpSign(2).SetFocus
                Exit Sub
            End If
             
            '判断操作符是否为空
            If Trim(txtInquiryCont3) = "" Then
                MsgBox "请输入要查询的内容", 64, "温馨提示"
                txtInquiryCont3.SetFocus
                Exit Sub
            End If
        
        End If
        
         Rem:连接数据库
        txtsql = "select * from Line_info where"
        
        '与上一行SQL语句组合
        txtsql = txtsql & " " & Trim(field(comboField(0).Text)) & " " & _
            Trim((comboOpSign(0).Text)) & " " & "'" & _
            Trim(txtInquiryCont1.Text) & "'"
            
    '         第一个组合关系存在
            If Trim(comboCombRela(0).Text <> "") Then
                 txtsql = txtsql & " " & field(Trim(comboCombRela(0).Text)) & " " & field(comboField(1).Text) & " " & comboOpSign(1).Text & " " & "'" & Trim(txtInquiryCont2.Text) & "'"
            End If
    
            '第二个组合关系存在
            If Trim(comboCombRela(1).Text <> "") Then
                txtsql = txtsql & "" & field(comboCombRela(1).Text) & "" & Trim(field(comboField(2).Text)) & "" & _
                    Trim(comboOpSign(2).Text) & "" & "'" & _
                    Trim(txtInquiryCont3.Text) & "'"
            End If
            
            Set mrc = ExecuteSQL(txtsql, Msgtext)
            
        If mrc.EOF = True Then
            MsgBox "没有查询到结果,请重新输入", 64, "温馨提示"
            Exit Sub
        Else
             With MSHFlexGrid1
                .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(3))         '上机日期
                .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(6))         '上机时间
                .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(7)) & ""    '下机日期
                .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(8)) & ""    '下机时间
                .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
        
       
        
        
    End Sub

*  ### 代码看似很多，其实很多都是判断文本框是否为空 ###
 *  ### 本文用了比较多的控件数组 ###
 *  ### 重点在连接数据库 ###

### 三、注意事项 ###

1.      Set mrc = ExecuteSQL(txtsql, Msgtext)
    
    这句代码一定要写的所有的查询语句之后
2.  ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb21zaW5n_size_16_color_FFFFFF_t_70 1][]
3.  关键字field只出现在“组合关系”与“字段名”哪里，“操作符”与“要查询的内容”框框不用加
4.  ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb21zaW5n_size_16_color_FFFFFF_t_70 2][]
5.  若是出现“实时错误91”一定要认真查看自己的txtSQL语句。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb21zaW5n_size_16_color_FFFFFF_t_70]: /images/20210923/188a998dad3c409e8bf50d6f505eb027.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb21zaW5n_size_16_color_FFFFFF_t_70 1]: /images/20210923/b1063c3341c540c08e89e19d2ac06455.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb21zaW5n_size_16_color_FFFFFF_t_70 2]: /images/20210923/547a6efc3a1a4b4c92e9612c2f4ae57d.png