vb复合查询

在敲《机房收费系统》的时候，我们遇到了一个组合查询，经过不懈努力，终于做出来了，现把思路和代码总结如下：

首先是组合框列表项的载入，在Form_Load中添加如下代码：

    For intIndex = 0 To 2
        cboFields(intIndex).AddItem "教师"
        cboFields(intIndex).AddItem "注册日期"
        cboFields(intIndex).AddItem "注册时间"
        cboFields(intIndex).AddItem "注销日期"
        cboFields(intIndex).AddItem "注销时间"
        cboFields(intIndex).AddItem "机器名"
        cboFields(intIndex).ListIndex = 0
        
        cboOperator(intIndex).AddItem "="
        cboOperator(intIndex).AddItem "<"
        cboOperator(intIndex).AddItem ">"
        cboOperator(intIndex).AddItem "<>"
        cboOperator(intIndex).ListIndex = 0
    Next intIndex
    
    For intIndex = 0 To 1
        cboRelation(intIndex).AddItem ""
        cboRelation(intIndex).AddItem "或"
        cboRelation(intIndex).AddItem "与"
        cboRelation(intIndex).ListIndex = 0
    Next intIndex

下面是“查询”的代码：

Private Sub cmdInquiry_Click()
    Dim txtSQL As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset
    
    '字段和关系
    Dim strFields(0 To 2) As String
    Dim strRelation(0 To 1) As String
    
    '当选择不同的字段时，赋给变量相应的数据库字段名
    For intIndex = 0 To 2
        Select Case cboFields(intIndex).Text
            Case "教师"
                strFields(intIndex) = "UserID"
            Case "注册日期"
                strFields(intIndex) = "LoginDate"
            Case "注册时间"
                strFields(intIndex) = "LoginTime"
            Case "注销日期"
                strFields(intIndex) = "LogoutDate"
            Case "注销时间"
                strFields(intIndex) = "LogoutTime"
            Case "机器名"
                strFields(intIndex) = "computer"
        End Select
    Next intIndex
    
    '关系
    For intIndex = 0 To 1
        Select Case cboRelation(intIndex).Text
            Case "或"
                strRelation(intIndex) = "or"
            Case "与"
                strRelation(intIndex) = "and"
        End Select
    Next intIndex
    
    '防止查询条件为空
    If txtContent1.Text = "" Then
        MsgBox "请输入要查询的内容！",vbOKOnly + vbInformation,"提示"
        txtContent1.SetFocus
        Exit Sub
    End If
    
    '如果只有一个查询条件
    If cboRelation(0).Text = "" And cboRelation(1).Text = "" Then
        txtSQL = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'"
        Set mrc = ExecuteSQL(txtSQL,MsgText)
    End If
    
    '如果为一二组合查询
    If cboRelation(0).Text <> "" And cboRelation(1).Text = "" Then
    
        '防止查询条件为空
        If txtContent2.Text = "" Then
            MsgBox "请输入要查询的内容！","提示"
            txtContent2.SetFocus
            Exit Sub
        End If

        txtSQL = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'" _
                       & " " & strRelation(0) & " " & strFields(1) & cboOperator(1).Text & "'" & Trim(txtContent2.Text) & "'"
        Set mrc = ExecuteSQL(txtSQL,MsgText)
    End If
    
    '如果为一二三组合查询
    If cboRelation(0).Text <> "" And cboRelation(1).Text <> "" Then
    
        '防止查询条件为空
        If txtContent2.Text = "" Then
            MsgBox "请输入要查询的内容！","提示"
            txtContent2.SetFocus
            Exit Sub
        End If
        
        If txtContent3.Text = "" Then
            MsgBox "请输入要查询的内容！","提示"
            txtContent3.SetFocus
            Exit Sub
        End If

        txtSQL = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'" _
                       & " " & strRelation(0) & " " & strFields(1) & cboOperator(1).Text & "'" & Trim(txtContent2.Text) & "'" _
                       & " " & strRelation(1) & " " & strFields(2) & cboOperator(2).Text & "'" & Trim(txtContent3.Text) & "'"
        Set mrc = ExecuteSQL(txtSQL,MsgText)
    End If
    
    '如果用户选择了组合关系2没选组合关系1，提示用户
    If cboRelation(0).Text = "" And cboRelation(1).Text <> "" Then
        MsgBox "请选择组合关系！","提示"
        cboRelation(0).SetFocus
        Exit Sub
    End If
    
    With MSHFlexGrid1
        .Rows = 1
        .Cols = 8
        .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) = "机器名"
        
        Do While Not mrc.EOF
            .Rows = .Rows + 1
            .CellAlignment = 4
            .TextMatrix(.Rows - 1,0) = mrc!serial
            .TextMatrix(.Rows - 1,1) = mrc!UserID
            .TextMatrix(.Rows - 1,2) = mrc!Level
            .TextMatrix(.Rows - 1,3) = mrc!LoginDate
            .TextMatrix(.Rows - 1,4) = mrc!LoginTime
            If IsNull(mrc.Fields("LogoutDate")) Then
                .TextMatrix(.Rows - 1,5) = ""
            Else
                .TextMatrix(.Rows - 1,5) = mrc!LogoutDate
            End If
            If IsNull(mrc.Fields("LogoutTime")) Then
                .TextMatrix(.Rows - 1,6) = ""
            Else
                .TextMatrix(.Rows - 1,6) = mrc!LogoutTime
            End If
            .TextMatrix(.Rows - 1,7) = mrc!computer
            mrc.MoveNext
        Loop
    End With
    
    mrc.Close

End Sub

初学者，不当之处敬请批评指正！

欢迎光临我的网易博客：http://blog.163.com/liu_xiaochun/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!