机房-组合查询记

前言：

 

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

 

总结：

 

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

 

    

 

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

   

    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.控件命名：一定要规范，这样写代码或者改代码的时候很方便，不会出现改错位置的情况（我的也不是很规范）；

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

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