机房收费系统之组合查询

    做机房收费系统时，要用到组合查询，其界面如下：我开始想的特别的复杂，可以这三个里随便选一个查询条件查询，也可在这三个中任选两个，也可三个都选，特别地麻烦，为什么不让他选第二个查询条件时必须选第一个呢，这样就简单了很多。

 

    我开始想的一种方法，代码如下：

    Dim strSQL As String    Dim strA As String, strB As String, strC As String        strSQL = "select * from student where "    
    '判断“字段名”，“操作符”，“要查询内容”是否都选了    If Not Trim(cmbField(0).Text = "") Then                If Trim(cmbOperator(0).Text = "") Then            MsgBox "请选择操作符！", vbOKOnly, "警告!"            cmbOperator(0).SetFocus            Exit Sub        Else            If Trim(txtInquire(0).Text = "") Then                MsgBox "请输入查询内容!", vbOKOnly, "警告!"                txtInquire(0).SetFocus                Exit Sub            End If        End If        blnMark(0) = True                    End If            '判断“字段名”，“操作符”，“要查询内容”是否都选了    If Not Trim(cmbField(1).Text = "") Then                If Trim(cmbOperator(1).Text = "") Then            MsgBox "请选择操作符！", vbOKOnly, "警告!"            cmbOperator(1).SetFocus            Exit Sub        Else            If Trim(txtInquire(1).Text = "") Then                MsgBox "请输入查询内容!", vbOKOnly, "警告!"                txtInquire(1).SetFocus                Exit Sub            End If        End If        blnMark(1) = True               End If
 
    'GetStringA,B是自定义的一个函数，将汉字替代为对应的字符，“Card--卡号”    strA = GetStringA(cmbField(0).Text) & Trim(cmbOperator(0).Text) & Trim(txtInquire(0).Text)    strB = GetStringB(cmbCombination(0).Text) & GetStringA(cmbField(1).Text) & Trim(cmbOperator(1).Text) & Trim(txtInquire(1).Text)    strC = GetStringB(cmbCombination(1).Text) & GetStringA(cmbField(2).Text) & Trim(cmbOperator(2).Text) & Trim(txtInquire(2).Text)
    '看选择了什么查询条件    If blnMark(0) = False Then        MsgBox "请选择查询条件！", vbOKOnly, "警告!"        Exit Sub    Else        strSQL = strSQL & strA        If blnMark(1) = True Then            strSQL = strSQL & strB            If blnMark(2) = True Then                strSQL = strSQL & strC         
           End If
       End If
   End If
    

    

    还有一种方法，不用逻辑考虑，“select * from 表名 where cardNo=1001  ”和“select * from 表名 where cardNo=1001 and 1=1 and1=1”是一样的效果，所以我们就不用选择了什么查询条件了，让查询条件默认是“1=1”，如果选择了什么查询条件就将“1=1”替换为查询条件即可，这样就简单了很多。 

Dim strSQL As String    Dim blnMark(4) As Boolean        Dim strFieldA As String    Dim strFieldB As String    Dim strFieldC As String       Dim strOperatorA As String    Dim strOperatorB As String    Dim strOperatorC As String        Dim strValueA As String    Dim strValueB As String    Dim strValueC As String       Dim strRelationA As String    Dim strRelationB As String        strRelationA = "and"    strRelationB = "and"      '给定默认值    strFieldB = "1"    strOperatorB = "="    strValueB = "1"        strFieldC = "1"    strOperatorC = "="    strValueC = "1"            '判断“字段名”，“操作符”，“要查询内容”是否都选了    If Not Trim(cmbField(0).Text = "") Then                If Trim(cmbOperator(0).Text = "") Then            MsgBox "请选择操作符！", vbOKOnly, "警告!"            cmbOperator(0).SetFocus            Exit Sub        Else            If Trim(txtInquire(0).Text = "") Then                MsgBox "请输入查询内容!", vbOKOnly, "警告!"                txtInquire(0).SetFocus                Exit Sub            End If        End If        blnMark(0) = True                '将默认值替换为查询条件        strFieldA = GetStringA(cmbField(0).Text)        strOperatorA = Trim(cmbOperator(0).Text)        strValueA = Trim(txtInquire(0).Text)            End If            '判断“字段名”，“操作符”，“要查询内容”是否都选了    If Not Trim(cmbField(1).Text = "") Then                If Trim(cmbOperator(1).Text = "") Then            MsgBox "请选择操作符！", vbOKOnly, "警告!"            cmbOperator(1).SetFocus            Exit Sub        Else            If Trim(txtInquire(1).Text = "") Then                MsgBox "请输入查询内容!", vbOKOnly, "警告!"                txtInquire(1).SetFocus                Exit Sub            End If        End If        blnMark(1) = True                '将默认值替换为查询条件        strRelationA = GetStringB(cmbCombination(0).Text)        strFieldB = GetStringA(cmbField(1).Text)        strOperatorB = Trim(cmbOperator(1).Text)        strValueB = Trim(txtInquire(1).Text)            End If            '判断“字段名”，“操作符”，“要查询内容”是否都选了    If Not Trim(cmbField(2).Text = "") Then                If Trim(cmbOperator(2).Text = "") Then            MsgBox "请选择操作符！", vbOKOnly, "警告!"            cmbOperator(2).SetFocus            Exit Sub        Else            If Trim(txtInquire(2).Text = "") Then                MsgBox "请输入查询内容!", vbOKOnly, "警告!"                txtInquire(2).SetFocus                Exit Sub            End If        End If        blnMark(2) = True                '将默认值替换为查询条件        strRelationB = GetStringB(cmbCombination(1).Text)        strFieldC = GetStringA(cmbField(2).Text)        strOperatorC = Trim(cmbOperator(2).Text)        strValueC = Trim(txtInquire(2).Text)            End If        '判断是否选择了查询条件    If blnMark(0) = False Then        MsgBox "请选择查询条件！", vbOKOnly, "警告!"        Exit Sub    End If        strSQL = "select * from student_Info where  " + strFieldA + strOperatorA + Add(strValueA) + " " + strRelationA + " " + strFieldB + strOperatorB + Add(strValueB) + " " + strRelationB + " " + strFieldC + strOperatorC + Add(strValueC)   

    原本要复杂的拼凑SQL语句，现在只要一条SQL语句就搞定，不用拼凑了，选择了查询语句就替换掉他的默认值，没有就将让他为默认值“1=1”。