机房收费系统之组合查询

【前言】

      组合查询在机房收费系统中出现的较多，同时个人认为组合查询还是对大脑的逻辑思维要求蛮高的，因此，在这篇博客里以学生基本信息查询窗体为例来总结一下机房收费系统之组合查询。

 

【正文】

窗体：

逻辑思路：

 

（一）对于窗体中的需要填写的内容设置为非空：

 

<span style="font-size:18px;">'***************第一行所有内容不为空
If Trim(comboFieldname(0).Text = "") Then
    MsgBox "请选择字段名", vbOKOnly, "警告"
    comboFieldname(0).SetFocus
    Exit Sub
End If

If Trim(comboOperator(0).Text = "") Then
    MsgBox "请选择字段名", vbOKOnly, "警告"
    comboFieldname(0).SetFocus
    Exit Sub
End If

If Trim(txtContent(0).Text = "") Then
    MsgBox "请选择字段名", vbOKOnly, "警告"
    comboFieldname(0).SetFocus
    Exit Sub
End If

blnMark(0) = True
'**************************************
        
'***************第二行所有内容和组合关系不为空
If Not comboRelation(0).Text = "" Then
    If Trim(comboFieldname(1).Text = "") Then
        MsgBox "请选择字段名", vbOKOnly, "警告"
        comboFieldname(1).SetFocus
        Exit Sub
    End If
    
    If Trim(comboOperator(1).Text = "") Then
        MsgBox "请选择字段名", vbOKOnly, "警告"
        comboFieldname(1).SetFocus
        Exit Sub
    End If
    
    If Trim(txtContent(1).Text = "") Then
        MsgBox "请选择字段名", vbOKOnly, "警告"
        comboFieldname(1).SetFocus
        Exit Sub
    End If
    
    If Trim(comboRelation(0).Text = "") Then
        MsgBox "请选择组合关系", vbOKOnly, "提醒"
    End If
    
    blnMark(1) = True
End If
'**************************************
        
''***************第三行所有内容和组合关系不为空
If blnMark(0) = True And blnMark(1) = True And comboRelation(1).Text <> "" Then
    If Trim(comboFieldname(2).Text = "") Then
        MsgBox "请选择字段名", vbOKOnly, "警告"
        comboFieldname(2).SetFocus
        Exit Sub
    End If

    If Trim(comboOperator(2).Text = "") Then
        MsgBox "请选择字段名", vbOKOnly, "警告"
        comboFieldname(2).SetFocus
        Exit Sub
    End If

    If Trim(txtContent(2).Text = "") Then
        MsgBox "请选择字段名", vbOKOnly, "警告"
        comboFieldname(2).SetFocus
        Exit Sub
    End If
    
    If Trim(comboRelation(1).Text = "") Then
        MsgBox "请选择组合关系", vbOKOnly, "提醒"
    End If
    blnMark(2) = True
End If
'**************************************</span>

 

（二）查询

1、将窗体上组合框中所选择的内容转换为数据库的查询：

 

<span style="font-size:18px;">'**************将组合框里的内容转换为数据库语言
Public Function Filename(i As String) As String
    
    Select Case i
        Case "卡号"
         Filename = "cardno"
        Case "学号"
         Filename = "studentno"
        Case "姓名"
         Filename = "studentname"
        Case "性别"
         Filename = "sex"
        Case "系别"
         Filename = "department"
        Case "年级"
         Filename = "grade"
        Case "班级"
         Filename = "class"
        Case "与"
         Filename = "and"
        Case "或"
         Filename = "or"
    End Select
End Function
'******************************</span>

2、查询语句编写：

<span style="font-size:18px;">'*****************分别将每一行的内容看为一个整体
    strA = Filename(ComboFieldName(0).Text) & Trim(ComboOperator(0).Text) & Trim(txtContent(0).Text) & " "
    </span><span style="font-size:18px;">strB = Filename(ComboRelation(0).Text) & " " & Filename(ComboFieldName(1).Text) & Trim(ComboOperator(1).Text) & Trim(txtContent(1).Text) & " "
    strC = Filename(ComboRelation(1).Text) & " " & Filename(ComboFieldName(2).Text) & Trim(ComboOperator(2).Text) & Trim(txtContent(2).Text)
'***************************************
    
If blnMark(0) = False Then     '如果第一行内容不完整则出现提示
     MsgBox "请选择查询条件！", vbOKOnly, "提醒"
    Exit Sub
Else
    txtSQL = txtSQL & strA     '根据窗体中第一行的内容所对应的查询语句
     If blnMark(1) = True Then
       txtSQL = txtSQL & strB '如果连接第一行和第二行的组合关系被选则需查询窗体中第一行和第二行的组合查询
         If blnMark(2) = True Then
          txtSQL = txtSQL & strC  '如果连接第一行和第二行的组合关系,第二行和第三行的组合关系被选则需查询窗体中三行的组合查询
          End If
     End If
End If</span>

 

3、将查询出来的结果显示在MSFlexGrid1中：

Set mrc = ExecuteSQL(txtSQL, Msgtext) '连接数据库

With MSFlexGrid1
    .Rows = 1
    .CellAlignment = 4
    .TextMatrix(0, 0) = "卡号"
    .TextMatrix(0, 1) = "学号"
    .TextMatrix(0, 2) = "姓名"
    .TextMatrix(0, 3) = "性别"
    .TextMatrix(0, 4) = "系别"
    .TextMatrix(0, 5) = "年级"
    .TextMatrix(0, 6) = "班级"
    .TextMatrix(0, 7) = "金额"
    .TextMatrix(0, 8) = "备注"
    .TextMatrix(0, 9) = "状态"
    .TextMatrix(0, 10) = "日期"
    .TextMatrix(0, 11) = "时间"
    .TextMatrix(0, 12) = "类型"
Do While mrc.EOF = False
    .CellAlignment = 4
    .Rows = .Rows + 1
    .TextMatrix(.Rows - 1, 0) = mrc.Fields(0)
    .TextMatrix(.Rows - 1, 1) = mrc.Fields(1)
    .TextMatrix(.Rows - 1, 2) = mrc.Fields(2)
    .TextMatrix(.Rows - 1, 3) = mrc.Fields(3)
    .TextMatrix(.Rows - 1, 4) = mrc.Fields(4)
    .TextMatrix(.Rows - 1, 5) = mrc.Fields(5)
    .TextMatrix(.Rows - 1, 6) = mrc.Fields(6)
    .TextMatrix(.Rows - 1, 7) = mrc.Fields(7)
    .TextMatrix(.Rows - 1, 8) = mrc.Fields(8)
    .TextMatrix(.Rows - 1, 9) = mrc.Fields(9)
    .TextMatrix(.Rows - 1, 10) = mrc.Fields(12)
    .TextMatrix(.Rows - 1, 11) = mrc.Fields(13)
    .TextMatrix(.Rows - 1, 12) = mrc.Fields(14)
    mrc.MoveNext
Loop
End With

 

4、组合查询完成  PS：不可缺少的会定义相应的变量

    Dim mrc As ADODB.Recordset
    Dim txtSQL As String, Msgtext As String
    Dim strA As String, strB As String, strC As String
    Dim blnMark(0 To 2) As Boolean
    
    txtSQL = "select * from student_Info where "

 

【总结】

一、及时对我们所理解的内容进行总结，当时的感觉是最“新鲜”的！

二、在很多时候，逻辑是关键，把逻辑关系整理清楚后一切问题就会迎刃而解！

三、遇到问题时首先自己要先尝试着去做，自己独立写出自己的逻辑，那样不仅更有成就感，同时收获也是非同一般的！