机房-组合查询记

Love The Way You Lie 2022-06-03 05:05 273阅读 0赞

# 前言： #

我们都用过简单的查询，组合查询在这之前也只是简单的了解一下，通过机房的学习，对组合查询有了更深刻的认识。

# 总结： #

机房中用到组合查询的窗体一共有4个，基本流程都一样，下面就以“操作员工作记录查询”为例，分析一下组合查询，纯属个人见解，有问题欢迎评论。

![20171219105618491][]

##    一、窗体登录的时候给各个控件添加内容 ##

cboField1.AddItem "教师"
        cboField1.AddItem "注册日期"
        cboField1.AddItem "注册时间"
        cboField1.AddItem "注销日期"
        cboField1.AddItem "注销时间"
        cboField1.AddItem "机器名"

cboConnect1.AddItem "与"
        cboConnect1.AddItem "或"

其他控件类似。

##    二、查询的过程，注意转化字段名内容和组合关系内容 ##

###      1.总体代码 ###

Dim txtSQL As String
        Dim mrc As ADODB.Recordset
        Dim Msgtext As String
        
        Dim strcboField1, strcboField2, strcboField3                    '转换字段名内容
        Dim strcboConnect1, strcboConnect2                              '转换组合关系内容
        
        '第一行查询，转换汉字为数据库中的字段
        Select Case cboField1.Text
        Case "教师"
            strcboField1 = "UserID"
        Case "注册日期"
            strcboField1 = "LoginDate"
        Case "注册时间"
            strcboField1 = "LoginTime"
        Case "注销日期"
            strcboField1 = "LogoutDate"
        Case "注销时间"
            strcboField1 = "LogoutTime"
        Case "机器名"
            strcboField1 = "computer"
            
        End Select
        
        If Trim(cboField1.Text) = "" Or Trim(cboOpe1.Text) = "" Or (Trim(txtInquiry1.Text) = "" And Trim(DTPicker1.Value = "")) Then
            
            MsgBox "请将选项内容填写完整！", vbOKOnly + vbExclamation, "提示"
            
            Exit Sub
            
        Else
            
            '在worklog_info这张表中获得整行记录
            If DTPicker1.Visible = True Then
            
                txtSQL = "select * from worklog_Info where " & strcboField1 & cboOpe1.Text & "'" & DTPicker1.Value & "'"
            
            Else
            
                txtSQL = "select * from worklog_Info where " & strcboField1 & cboOpe1.Text & "'" & txtInquiry1.Text & "'"
            
            End If
            
                Set mrc = ExecuteSQL(txtSQL, Msgtext)
            
        End If
        
        '实现第二行查询
        If cboConnect1.Text = "请选择" Then
            GoTo Case1
            
        Else
            
            Select Case cboConnect1.Text
            Case "与"
                strcboConnect1 = "and"
            Case "或"
                strcboConnect1 = "or"
            End Select
            
            Select Case cboField2.Text
            Case "教师"
                strcboField2 = "UserID"
            Case "注册日期"
                strcboField2 = "LoginDate"
            Case "注册时间"
                strcboField2 = "LoginTime"
            Case "注销日期"
                strcboField2 = "LogoutDate"
            Case "注销时间"
                strcboField2 = "LogoutTime"
            Case "机器名"
                strcboField2 = "computer"
                
            End Select
            
            If Trim(cboField2.Text) = "" Or Trim(cboOpe2.Text) = "" Or (Trim(txtInquiry2.Text) = "" And Trim(DTPicker2.Value = "")) Then
                
                MsgBox "请将选项内容填写完整！", vbOKOnly + vbExclamation, "提示"
                
                Exit Sub
                
            Else
            
                '在worklog_info这张表中获得整行记录。
                If DTPicker2.Visible = False Then
                    
                    txtSQL = txtSQL & strcboConnect1 & " " & strcboField2 & cboOpe2.Text & "'" & txtInquiry2.Text & "'"
                
                Else
                
                    txtSQL = txtSQL & strcboConnect1 & " " & strcboField2 & cboOpe2.Text & "'" & DTPicker2.Value & "'"
                    
                End If
                
                    Set mrc = ExecuteSQL(txtSQL, Msgtext)
                
            End If
            
        End If
        
        '实现第三行查询
        
        If cboConnect2.Text = "请选择" Then
            GoTo Case1
            
        Else
            
            Select Case cboConnect2.Text
            Case "与"
                strcboConnect2 = "and"
            Case "或"
                strcboConnect2 = "or"
            End Select
            
            Select Case cboField3.Text
            Case "教师"
                strcboField3 = "UserID"
            Case "注册日期"
                strcboField3 = "LoginDate"
            Case "注册时间"
                strcboField3 = "LoginTime"
            Case "注销日期"
                strcboField3 = "LogoutDate"
            Case "注销时间"
                strcboField3 = "LogoutTime"
            Case "机器名"
                strcboField3 = "computer"
                
            End Select
            
            If Trim(cboField3.Text) = "" Or Trim(cboOpe3.Text) = "" Or (Trim(txtInquiry3.Text) = "" And Trim(DTPicker3.Value = "")) Then
                
                MsgBox "请将选项内容填写完整！", vbOKOnly + vbExclamation, "提示"
                
                Exit Sub
                
            Else
                
                If DTPicker3.Visible = False Then
                
                    txtSQL = txtSQL & strcboConnect2 & " " & strcboField3 & cboOpe3.Text & "'" & txtInquiry3.Text & "'"
                    
                Else
                
                    txtSQL = txtSQL & strcboConnect2 & " " & strcboField3 & cboOpe3.Text & "'" & DTPicker3.Value & "'"
                    
                End If
                    
                    Set mrc = ExecuteSQL(txtSQL, Msgtext)
                
                If mrc.EOF Then
                    
                    MsgBox "该条件的数据不存在！", vbOKOnly + vbInformation, "提示"
                    
                    Exit Sub
                End If
                
            End If
            
        End If
        
    Case1:
        
        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
                .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))
                .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(1))
                .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(2))
                .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(3))
                .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))
                .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5)) & ""
                .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6)) & ""
                .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(7)) & ""
                .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(8))
                
                mrc.MoveNext
                
            Loop
            
        End With
        mrc.Close

###      2.注意问题： ###

查询框上为什么有DTPicker控件？所起的作用是什么？

大家可以看到，cboField里加的内容有注册日期和注销日期等，那如果用户选择注册日期，是不是需要手动输入日期，如果输的日期格式不正确，是否会影响该软件的使用呢？为了给用户一个方便，便在txtInquiry上添加一个DTPicker控件，用代码使它在选择日期时出现DTPicker控件，选择教师之类的出现txtInquiry框。

如果选择教师，操作符里可以出现“<”“>”符号吗？

所以这里就是我们需要完善的地方。

Private Sub cboField1_Click()
        cboOpe1.Clear                          '避免cboOpe1重复添加操作符

Select Case cboField1.Text
            Case "教师"
                cboOpe1.AddItem "="
                cboOpe1.AddItem "<>"
                txtInquiry1.Locked = False      '选择教师的时候，可以用txtInquiry1，DTPicker1不可见
                DTPicker1.Visible = False
            Case "注册日期"
                cboOpe1.AddItem "="
                cboOpe1.AddItem "<"
                cboOpe1.AddItem ">"
                cboOpe1.AddItem "<>"
                txtInquiry1.Locked = True
                DTPicker1.Visible = True
                DTPicker1.Format = dtpShortDate
            Case "注册时间"
                cboOpe1.AddItem "="
                cboOpe1.AddItem "<"
                cboOpe1.AddItem ">"
                cboOpe1.AddItem "<>"
                txtInquiry1.Locked = True
                DTPicker1.Visible = True
                DTPicker1.Format = dtpShortDate
            Case "注销日期"
                cboOpe1.AddItem "="
                cboOpe1.AddItem "<"
                cboOpe1.AddItem ">"
                cboOpe1.AddItem "<>"
                txtInquiry1.Locked = True
                DTPicker1.Visible = True
                DTPicker1.Format = dtpShortDate
            Case "注销时间"
                cboOpe1.AddItem "="
                cboOpe1.AddItem "<"
                cboOpe1.AddItem ">"
                cboOpe1.AddItem "<>"
                txtInquiry1.Locked = True
                DTPicker1.Visible = True
                DTPicker1.Format = dtpShortDate
            Case "机器名"
                cboOpe1.AddItem "="
                cboOpe1.AddItem "<>"
                txtInquiry1.Locked = False
                DTPicker1.Visible = False
        End Select
    End Sub

其他两个cboField类似。

大家是否注意到.TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5)) & ""后面的 & ""，那么它起的是什么作用呢？

比如说现在登录的12号，但是还没有注销。所以数据库表里的注销日期和注销时间为Null，如果不加 & "" ，会出现“无效使用Null”的情况。 简单说，就是为了避免为空。

# 后记： #

这是组合查询需要注意的一系列问题，最后想说一下自己的感受：

1.控件命名：一定要规范，这样写代码或者改代码的时候很方便，不会出现改错位置的情况（我的也不是很规范![大笑][laugh.gif]）；

2.代码规范：代码的位置一定要规范，不要都挤在一起。推荐使用Tab键；

3.简单说一下组合查询的逻辑吧：（图画的不是很好，有什么疑问欢迎私聊![吐舌头][tongue.gif]）

![20171219113959226][]

[20171219105618491]: /images/20220603/14a8607dc5f84918a2d7c7eeec10cb76.png
[laugh.gif]: /images/20220603/75255b449f4641d98da7c828624f3d92.png
[tongue.gif]: /images/20220603/ad1f9ef43ba54ac480d85b88a82c8682.png
[20171219113959226]: /images/20220603/290fdfc965354f4a9c294e36ab571e14.png