机房收费系统--组合查询

时间：2022-09-16 10:32:00

机房遇到的一个困扰了我两天的问题就是组合查询，但是真的当我完整的做出来之后感觉也是很简单的


首先在加载里面，将各个按钮的选项加载好，当然不要将每个按钮的选项都输一遍，我选择的是给控件创建了数组，用了for循环语句添加上的


然后就是第一个问题，选择一个中文的选项怎样让它在链接数据库的时候改为英文


Public Function Field(i As String) As String
    Select Case i
        Case "卡号"
            Field = "cardno"
        Case "姓名"
            Field = "studentname"
        Case "上机日期"
            Field = "ondate"
        Case "上机时间"
            Field = "ontime"
        Case "下机日期"
            Field = "offdate"
        Case "下机时间"
            Field = "offtime"
        Case "消费金额"
            Field = "consume"
        Case "余额"
            Field = "cash"
        Case "备注"
            Field = "status"
    End Select
End Function


我想过这个问题，但是发现其他的方法代码都很杂很多，最后还是在网上查到的一段代码解决了我的问题。


然后是逻辑值的问题，引文文本框较少，所以就一步一步的添加上的


Private Sub ComboRel1_Click(Index As Integer)

    If ComboRel1(0).Text = "与" Then
        Rel1 = "and"
    End If

    If ComboRel1(0).Text = "或" Then
        Rel1 = "or"
    End If

    If ComboRel1(1).Text = "与" Then
        Rel2 = "and"
    End If

    If ComboRel1(1).Text = "或" Then
        Rel2 = "or"
    End If

End Sub

再然后就是链接数据库了，如果一句代码太长的话，容易出错，也不容易查找与修改，所以我就先定义了字段，然后再把几个字段组合成了一句完整的话 


    Dim Select1 As String
    Dim Select2 As String
    Dim Select3 As String

    Select1 = "" & Trim(Field(ComboStr1(0).Text)) & " " & Trim(ComboSymbol1(0).Text) & " '" & Text1.Text & "'"
    Select2 = "" & Trim(Field(ComboStr1(1).Text)) & " " & Trim(ComboSymbol1(1).Text) & " '" & Text2.Text & "'"
    Select3 = "" & Trim(Field(ComboStr1(2).Text)) & " " & Trim(ComboSymbol1(2).Text) & " '" & Text3.Text & "'"

然后对逻辑值进行一个判断，来确定与或关系，并且把链接数据库的sql语句表示正确 


    '如果两个逻辑运算符都空着
    If ComboRel1(0).Text = "" And ComboRel1(1).Text = "" Then
        If ComboStr1(0).Text = "" Or ComboSymbol1(0).Text = "" Or Text1.Text = "" Then
            MsgBox "请将查询信息输全！", vbOKOnly + vbExclamation, "温馨提示"
            Exit Sub
        Else
            txtSQL = "select * from Line_Info where " & Select1 & ""
            Set mrc = ExecuteSQL(txtSQL, msgText)
        End If
    End If

    '如果第一个不空，第二个空
    If (Not ComboRel1(0).Text = "") And (ComboRel1(1).Text = "") Then
        If ComboStr1(0).Text = "" Or ComboSymbol1(0).Text = "" Or Text1.Text = "" Or ComboStr1(1).Text = "" Or ComboSymbol1(1).Text = "" Or Text2.Text = "" Then
            MsgBox "请将查询信息输全！", vbOKOnly + vbExclamation, "温馨提示"
            Exit Sub
        Else
            txtSQL = "select * from Line_Info where " & Trim(Select1) & " " & Rel1 & " " & Trim(Select2) & ""
            Set mrc = ExecuteSQL(txtSQL, msgText)
        End If
    End If

    '如果两个都不空
    If (Not ComboRel1(0).Text = "") And (Not ComboRel1(1).Text = "") Then
        If ComboStr1(0).Text = "" Or ComboSymbol1(0).Text = "" Or Text1.Text = "" Or ComboStr1(1).Text = "" Or ComboSymbol1(1).Text = "" Or Text2.Text = "" Or ComboStr1(2).Text = "" Or ComboSymbol1(2).Text = "" Or Text3.Text = "" Then
            MsgBox "请将查询信息输全！", vbOKOnly + vbExclamation, "温馨提示"
            Exit Sub
        Else
            If ComboRel1(0).Text = "或" And ComboRel1(1).Text = "与" Then
                txtSQL = "select * from Line_Info where (" & Select1 & " " & Rel1 & " " & Select2 & ") " & Rel2 & " " & Select3 & ""
                Set mrc = ExecuteSQL(txtSQL, msgText)
            Else
                If ComboRel1(0).Text = "与" And ComboRel1(1).Text = "或" Then
                    txtSQL = "select * from Line_Info where " & Select1 & " " & Rel1 & " (" & Select2 & " " & Rel2 & " " & Select3 & ")"
                    Set mrc = ExecuteSQL(txtSQL, msgText)
                Else
                    txtSQL = "select * from Line_Info where " & Select1 & " " & Rel1 & " " & Select2 & " " & Rel2 & " " & Select3 & ""
                    Print txtSQL
                    Set mrc = ExecuteSQL(txtSQL, msgText)
                End If
            End If
        End If
    End If

总结


我觉得这段代码最重要的就是搞清楚代码的逻辑关系，然后再进行判断与调试。另外代码的重复可以用调用的方法重复使用，这样可以使代码更加简洁明了。

标签：

相关文章