组合查询

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

（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语句补充完整

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

转载于:https://www.cnblogs.com/wangjingna/p/3763599.html