【VB与数据库】机房收费系统--组合查询

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

首先在加载里面，将各个按钮的选项加载好，当然不要将每个按钮的选项都输一遍，我选择的是给控件创建了数组，用了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 SelectEnd 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 IfEnd 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

总结

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