VB—联合查询（查询学生信息实例）

刚刚进行完了机房收费系统关于联合查询的部分，整个代码进行完了，才发现，联合查询的东西不是很多，要用到知识就只有一个写查询的语句，最难的地方就是分析查询情况，首先呢，简单介绍下我整个代码的思路，先列出查询时可能出现的情况：

1.我们现在将第一句话当成一个查询的条件（卡号=1），仅用这一个条件进行查询

2.将第一句话和第二句话联合起来，作为一个查询条件，进行查询（卡号=1与学号=1）

3.将第一、二、三种加起来，视为一种查询的条件，进行查询

分析完查询可能出现的几种情况，接下来，我们就开始写代码了！

Private Subcmdinquire_Click()
   
    Dim mrc As ADODB.Recordset
    Dim txtsql As String
    Dim msgtext As String
    '定义变量，将文本框中所输入的内容转化成字段输出来
    Dim FLD(0 To 4) As String
    Dim relation(0 To 1) As String
    '定义三种方式，判断文本框中输入的内容是否为空（格式：text1.text<>"")
    Dim ok1 As Boolean
    Dim ok2 As Boolean
    Dim ok3 As Boolean
 
    If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" Then
        ok1 = True
    Else
        ok1 = False
    End If
 
    If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" And cmbandor(0).Text <> "" And cmbfield(1).Text<> "" And cmbsign(1).Text <> "" AndTrim(txtinquire(1).Text) <> "" Then
        ok2 = True
    Else
        ok2 = False
    End If
 
    If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" And cmbandor(0).Text <> "" And cmbfield(1).Text<> "" And cmbsign(1).Text <> "" AndTrim(txtinquire(1).Text) <> "" And cmbfield(2).Text <>"" And cmbsign(2).Text <> "" AndTrim(txtinquire(2).Text) <> "" Then
        ok3 = True
    Else
        ok3 = False
    End If
定义一个变量FLD（注意：这里定义变量时所用的名称不能和ＶＢ里的关键字相重复）
　　将下拉菜单中所选择的东西转换成数据库中的字段，进行查询
    FLD(0) = "work_date"
    FLD(1) = "work_time"
    FLD(2) = "work_teacher"
    FLD(3) = "teacher_type"
    FLD(4) = "PC_name"
 
 
    relation(0) = " and "
    relation(1) = " or "
 
   
'当三种查询的条件都满足的时候
    If ok1 = True Then
        If ok2 = True Then
            If ok3 = True Then　　　　　　　　　　　　　　　　（注意：ListIndex属性的使用，这里使用listIndex是为了设定下拉菜单中返回值为字符串）
                txtsql = "select * fromwork_teacher where " & Trim(FLD(cmbfield(0).ListIndex)) & Trim(cmbsign(0).Text) & "'" &Trim(txtinquire(0).Text) & "'" &relation(cmbandor(0).ListIndex) & Trim(FLD(cmbfield(1).ListIndex)) &Trim(cmbsign(1).Text) & "'" & Trim(txtinquire(1).Text) &"'" & relation(cmbandor(0).ListIndex) &Trim(FLD(cmbfield(2).ListIndex)) & Trim(cmbsign(2).Text) &"'" & Trim(txtinquire(2).Text) & "'"
 
     写查询语句的时候更要注意符号的使用，尤其是单引号和双引号，在这里教给大家一个小方法，我们写完一句查询语句，然后可以 msgBox txtsql 设置一个立即窗口检查下自己是否书写正确！
            Else '满足两种查询条件
 
                txtsql = "select * fromwork_teacher where " & Trim(FLD(cmbfield(0).ListIndex)) &Trim(cmbsign(0).Text) & "'" & Trim(txtinquire(0).Text) &"'" & relation(cmbandor(0).ListIndex) & Trim(FLD(cmbfield(1).ListIndex))& Trim(cmbsign(1).Text) & "'" & Trim(txtinquire(1).Text)& "'"
            End If
        Else '满足一种查询条件
            txtsql = "select * fromwork_teacher where " & Trim(FLD(cmbfield(0).ListIndex)) &Trim(cmbsign(0).Text) & "'" & Trim(txtinquire(0).Text) &"'"
 
        End If
    Else  '没有输入完整的条件，查空
        Call checktext(0)
        Exit Sub
    End If
 
    Set mrc = Executesql(txtsql,msgtext)
 
    If mrc.EOF Then
 
        MsgBox "没有找到符合要求的结果！",vbOKOnly +vbInformation,"提示"
 
        With myflexgrid
            .Rows = 1
            .CellAlignment = 4
            .TextMatrix(0,0) = "上机日期"
            .TextMatrix(0,1) = "上机时间"
            .TextMatrix(0,2) = "操作教师"
            .TextMatrix(0,3) = "教师类型"
            .TextMatrix(0,4) = "机器号"
 
        End With
        Exit Sub
    Else
        With myflexgrid
            .Rows = 1
            .CellAlignment = 4
            .TextMatrix(0,4) = "机器号"
            Do While Not mrc.EOF
                .Rows = .Rows + 1
                .TextMatrix(.Rows - 1,0) =mrc.Fields("work_date")
                .TextMatrix(.Rows - 1,1) =mrc.Fields("work_time")
                .TextMatrix(.Rows - 1,2) =mrc.Fields("work_teacher")
                .TextMatrix(.Rows - 1,3) =mrc.Fields("teacher_type")
                .TextMatrix(.Rows - 1,4) =mrc.Fields("PC_name")
                mrc.MoveNext
            Loop
        End With
        mrc.Close
 
    End If
 
End Sub
 
Private SubForm_Load()
cmbandor(0).AddItem"与"
cmbandor(0).AddItem"或"
 
cmbandor(1).AddItem"与"
cmbandor(1).AddItem"或"
 
cmbfield(0).AddItem"上机日期"
cmbfield(0).AddItem"上机时间"
cmbfield(0).AddItem"操作教师"
cmbfield(0).AddItem"教师类型"
cmbfield(0).AddItem"机器号"
 
 
cmbfield(1).AddItem"上机日期"
cmbfield(1).AddItem"上机时间"
cmbfield(1).AddItem"操作教师"
cmbfield(1).AddItem"教师类型"
cmbfield(1).AddItem"机器号"
 
cmbfield(2).AddItem"上机日期"
cmbfield(2).AddItem"上机时间"
cmbfield(2).AddItem"操作教师"
cmbfield(2).AddItem"教师类型"
cmbfield(2).AddItem"机器号"
 
cmbsign(0).AddItem"<"
cmbsign(0).AddItem">"
cmbsign(0).AddItem"="
cmbsign(0).AddItem"<>"
 
cmbsign(1).AddItem"<"
cmbsign(1).AddItem">"
cmbsign(1).AddItem"="
cmbsign(1).AddItem"<>"
 
 
cmbsign(2).AddItem"<"
cmbsign(2).AddItem">"
cmbsign(2).AddItem"="
cmbsign(2).AddItem"<>"
'
 
End Sub
Private Subchecktext(Index As Integer)
    Dim ok1 As Boolean
    Dim ok2 As Boolean
    Dim ok3 As Boolean
 
    If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" Then
        ok1 = True
    Else
        ok1 = False
    End If
 
    If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" And cmbandor(0).Text <> "" And cmbfield(1).Text<> "" And cmbsign(1).Text <> "" AndTrim(txtinquire(1).Text) <> "" Then
        ok2 = True
    Else
        ok2 = False
    End If
 
    If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <> ""And cmbandor(0).Text <> "" And cmbfield(1).Text <>"" And cmbsign(1).Text <> "" AndTrim(txtinquire(1).Text) <> "" And cmbfield(2).Text <>"" And cmbsign(2).Text <> "" AndTrim(txtinquire(2).Text) <> "" Then
        ok3 = True
    Else
        ok3 = False
    End If
 
    If Not TestTxt(cmbfield(Index).Text) Then
        MsgBox "请选择字段名",vbOKOnly + vbInformation,"提示"
        cmbfield(Index).SetFocus
        Exit Sub
    End If
    If Not TestTxt(cmbsign(Index).Text) Then
        MsgBox "请选择操作符","提示"
        cmbsign(Index).SetFocus
        Exit Sub
    End If
    If Not TestTxt(txtinquire(Index).Text) Then
        MsgBox "请输入要查询的内容","提示"
        txtinquire(Index).SetFocus
        Exit Sub
    End If
 
End Sub

到这了，所有的功能就能全部实现了，看下是不是很简单呢！如果谁有更好的方法欢迎指点批评！

VB—联合查询（查询学生信息实例）

猜你在找的VB相关文章