组合查询

在做《机房收费系统》的时候多次用到组合查询，对于组合查询关键的有两步：

（1）对组合查询的各个查询条件做标记

         因为查询条件有很多，所以很难像以前一样把条件直接写到链接语句中查询。又由于vb中的内容往往和数据库中的字段不一样，这就需要对查询条件做一下标记（这算是第一次真正的用到给vb中的内容做标记，虽然不难，但还是值得提一下）

         下面用的是控件的tag属性来达到vb与数据库内容之间的转换，这个方法有很多种，总之是比较简单的代码编写

Private Sub cboFields1_Click()                  '把查询条件转换成SQL可识别的字段    If cboFields1.Text = "卡号" Then cboFields1.Tag = "CardNO"    If cboFields1.Text = "学号" Then cboFields1.Tag = "StudentNO"    If cboFields1.Text = "姓名" Then cboFields1.Tag = "StudentName"    If cboFields1.Text = "性别" Then cboFields1.Tag = "Sex"    If cboFields1.Text = "系别" Then cboFields1.Tag = "Department"    If cboFields1.Text = "年级" Then cboFields1.Tag = "Grade"    If cboFields1.Text = "班号" Then cboFields1.Tag = "Class"End Sub

（2）查询语句的编写

         这一块儿是组合查询的核心部分，代码中对txtSQL进行了多次添加修改。我出错的部分基本上都是对txtSQL的编写上，主要在“（）、""、 & ”这三个符号上

'连接数据库表Student_Info    txtSQL = "select * from Student_Info where ( " & cboFields1.Tag & cboOperator1.Text & "'" & txtContent1.Text & "'"        txtSQL = txtSQL & " " & cboUnit1.Tag & " " & cboFields2.Tag & cboOperator2.Text & "'" & txtContent2.Text & "'"txtSQL = txtSQL & " " & cboUnit2.Tag & " " & cboFields3.Tag & cboOperator3.Text & "'" & txtContent3.Text & "'"    txtSQL = txtSQL & ")"           '把txtsql语句补充完整

下面是txtSQL语句完整的代码过程

    '定义连接数据库表Student_Info的变量    Dim txtSQL As String    Dim MsgString As String    Dim mrt As ADODB.Recordset        '连接数据库表Student_Info    txtSQL = "select * from Student_Info where ( " & cboFields1.Tag & cboOperator1.Text & "'" & txtContent1.Text & "'"    '检验第一组查询信息是否为空    If cboFields1.Text = "" Then        MsgBox "请输入字段名！", vbOKOnly + vbExclamation, "警告"        Exit Sub    ElseIf cboOperator1.Text = "" Then        MsgBox "请输入操作符！", vbOKOnly + vbExclamation, "警告"        Exit Sub    ElseIf txtContent1.Text = "" Then        MsgBox "请输入查询内容！", vbOKOnly + vbExclamation, "警告"        Exit Sub    End If    '检验第二组查询信息是否为空    If cboUnit1.Text <> "" Then        If cboFields2.Text = "" Then            MsgBox "请输入字段名！", vbOKOnly + vbExclamation, "警告"            Exit Sub        End If        If cboOperator2.Text = "" Then            MsgBox "请输入操作符！", vbOKOnly + vbExclamation, "警告"            Exit Sub        End If        If txtContent2.Text = "" Then            MsgBox "请输入查询内容！", vbOKOnly + vbExclamation, "警告"            Exit Sub        End If        '第二组条件无误，添加信息        txtSQL = txtSQL & " " & cboUnit1.Tag & " " & cboFields2.Tag & cboOperator2.Text & "'" & txtContent2.Text & "'"    End If        '检验第三组查询信息是否为空    If cboUnit2.Text <> "" Then        If cboFields3.Text = "" Then            MsgBox "请输入字段名！", vbOKOnly + vbExclamation, "警告"            Exit Sub        End If        If cboOperator3.Text = "" Then            MsgBox "请输入操作符！", vbOKOnly + vbExclamation, "警告"            Exit Sub        End If        If txtContent3.Text = "" Then            MsgBox "请输入查询内容！", vbOKOnly + vbExclamation, "警告"            Exit Sub        End If        '第三组条件无误，添加信息        txtSQL = txtSQL & " " & cboUnit2.Tag & " " & cboFields3.Tag & cboOperator3.Text & "'" & txtContent3.Text & "'"    End If        txtSQL = txtSQL & ")"           '把txtsql语句补充完整

      接下来就可以按照我们以往的查询方法进行代码编写了。