VB机房收费系统07——组合查询

VB 前端之家
前端之家收集整理的这篇文章主要介绍了VB机房收费系统07——组合查询，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

前言

我们以“学生基本信息维护”窗体为例，介绍下组合查询


正文


如图，首先让我们来理解下组合查询是如何实现的。

我们把第一排的前三个文本框看作第一个限定条件。

例如"框1"选择"卡号"，“框2”选择“=”，“框3”选择"2",这三个框组成了第一个限定条件。

“卡号=2”

当我们只写一个限定条件时，是不需要组合关系框的。

但当我们填写了第一排和第二排后，两个条件共同筛选信息，这是就需要选择组合关系“与”还是“或”。这就是组合查询。

但我们需要搞清楚，如何依据选择限定条件的个数，让“与”“或”分别生效。


完整代码

Private Sub Form_Load()
'添加下拉框信息
    Combo1.AddItem "卡号"
    Combo1.AddItem "学号"
    Combo1.AddItem "姓名"
    Combo1.AddItem "性别"
    Combo1.AddItem "系别"
    Combo1.AddItem "年级"
    Combo1.AddItem "班级"
    
    Combo2.AddItem "卡号"
    Combo2.AddItem "学号"
    Combo2.AddItem "姓名"
    Combo2.AddItem "性别"
    Combo2.AddItem "系别"
    Combo2.AddItem "年级"
    Combo2.AddItem "班级"
    
    Combo3.AddItem "卡号"
    Combo3.AddItem "学号"
    Combo3.AddItem "姓名"
    Combo3.AddItem "性别"
    Combo3.AddItem "系别"
    Combo3.AddItem "年级"
    Combo3.AddItem "班级"
    
    Combo4.AddItem "<"
    Combo4.AddItem ">"
    Combo4.AddItem "="
    Combo4.AddItem "<>"
    
    Combo5.AddItem "<"
    Combo5.AddItem ">"
    Combo5.AddItem "="
    Combo5.AddItem "<>"
    
    Combo6.AddItem "<"
    Combo6.AddItem ">"
    Combo6.AddItem "="
    Combo6.AddItem "<>"
    
    Combo7.AddItem "与"
    Combo7.AddItem "或"
    
    Combo8.AddItem "与"
    Combo8.AddItem "或"
    
    
    

'如果组合查询框控件为空，不能选择如下查询条件
     If Combo7.Text = "" Then

        Combo2.Enabled = False
        Combo3.Enabled = False
        Combo5.Enabled = False
        Combo6.Enabled = False
        Text2.Enabled = False
        Text3.Enabled = False
        Combo8.Enabled = False
     End If

     If Combo8.Text = "" Then

        Combo3.Enabled = False
        Combo6.Enabled = False
        Text3.Enabled = False
     End If
     

    
End Sub





'激活组合控件
Private Sub Timer1_Timer()
  If Combo7.Text <> "" Then
        Combo2.Enabled = True
        Combo5.Enabled = True
        Combo8.Enabled = True
        Text2.Enabled = True
    End If
End Sub

Private Sub Timer2_Timer()
If Combo8.Text <> Trim("") Then
        Combo3.Enabled = True
        Combo6.Enabled = True
        Text3.Enabled = True
    End If
End Sub



'组合查询
Private Sub cmdinquire_Click()
    Dim mrc As ADODB.Recordset
    Dim txtsql As String
    Dim msgtext As String
    Dim fieldname,fieldname2,fieldname3,fieldname4,fieldname5
    
'第一行
'把人话转换成机器话

    Select Case Combo1.Text
        Case "卡号"
            fieldname = "cardno"
        Case "学号"
            fieldname = "studentNo"
        Case "姓名"
            fieldname = "studentName"
        Case "性别"
            fieldname = "sex"
        Case "系别"
            fieldname = "department"
        Case "年级"
            fieldname = "grade"
        Case "班级"
            fieldname = "class"
     End Select
     
     
'判断查询项是否未选择

    If Trim(Combo1.Text = "") Or Trim(Combo4.Text = "") Or Trim(Text1.Text = "") Then
       MsgBox "请填写完整的查询条件!",vbonly + vbExclamation,"提示"
        Exit Sub
    
    Else
    
        txtsql = "select * from student_Info where " & fieldname & Combo4.Text & "'" & Text1.Text & "'"
        Set mrc = Executesql(txtsql,msgtext)
        
    If mrc.EOF = True Then
        MsgBox "该条件的数据不存在!",vbOKOnly + vbExclamation,"提示"
    Exit Sub

        
End If
End If





'第二行
''组合查询框判断
    If Combo7 = Trim("") Then
        Combo2.Enabled = False
        Combo3.Enabled = False
        Combo5.Enabled = False
        Combo6.Enabled = False
        Combo8.Enabled = False
        Text2.Enabled = False
        Text3.Enabled = False
    Else
    
        Select Case Combo7.Text
        
            Case "与"
                fieldname2 = "and"
            Case "或"
                fieldname2 = "or"
        End Select
        
        
    Select Case Combo2.Text
        Case "卡号"
            fieldname3 = "cardno"
        Case "学号"
            fieldname3 = "studentNo"
        Case "姓名"
            fieldname3 = "studentName"
        Case "性别"
            fieldname3 = "sex"
        Case "系别"
            fieldname3 = "department"
        Case "年级"
            fieldname3 = "grade"
        Case "班级"
            fieldname3 = "class"
     End Select
                
'判断查询项是否未选择

    If Trim(Combo2.Text = "") Or Trim(Combo5.Text = "") Or Trim(Text2.Text = "") Then
       MsgBox "请填写完整的查询条件!","提示"
        Exit Sub
    Else
        txtsql = txtsql & fieldname2 & " " & fieldname3 & Combo5.Text & "'" & Text2.Text & "'"
        Set mrc = Executesql(txtsql,"提示"
        Exit Sub
 End If
 End If
 End If
                
                
'第三行
    If Trim(Combo8.Text) = "" Then
        Combo3.Enabled = False
        Combo6.Enabled = False
        Text3.Enabled = False
'显示上机信息

    
    Else
    
        Select Case Combo8.Text
        
            Case "与"
                fieldname4 = "and"
            Case "或"
                fieldname4 = "or"
        End Select
                
                
        Select Case Combo3.Text
            Case "卡号"
                fieldname5 = "cardno"
            Case "学号"
                fieldname5 = "studentNo"
            Case "姓名"
                fieldname5 = "studentName"
            Case "性别"
                fieldname5 = "sex"
            Case "系别"
                fieldname5 = "department"
            Case "年级"
                fieldname5 = "grade"
            Case "班级"
                fieldname5 = "class"
     End Select
                
                
                
    If Trim(Combo3.Text = "") Or Trim(Combo6.Text = "") Or Trim(Text3.Text = "") Then
       MsgBox "请填写完整的查询条件!","提示"
        Exit Sub
    Else
        txtsql = txtsql & fieldname4 & " " & fieldname5 & Combo6.Text & "'" & Text3.Text & "'"
        Set mrc = Executesql(txtsql,"提示"
        Exit Sub
End If
End If
End If

                
                
'显示上机信息

    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) = "注册时间"
        .TextMatrix(0,13) = "用户类型"
        .TextMatrix(0,14) = "学号"
        
    Do While Not mrc.EOF
        .Rows = .Rows + 1
        .CellAlignment = 4
        .TextMatrix(.Rows - 1,0) = Trim(mrc.Fields(0))
        .TextMatrix(.Rows - 1,1) = Trim(mrc.Fields(2))
        .TextMatrix(.Rows - 1,2) = Trim(mrc.Fields(3))
        .TextMatrix(.Rows - 1,3) = Trim(mrc.Fields(4))
        .TextMatrix(.Rows - 1,4) = Trim(mrc.Fields(5))
        .TextMatrix(.Rows - 1,5) = Trim(mrc.Fields(6))
        .TextMatrix(.Rows - 1,6) = Trim(mrc.Fields(7))
        .TextMatrix(.Rows - 1,7) = Trim(mrc.Fields(8))
        .TextMatrix(.Rows - 1,8) = Trim(mrc.Fields(9))
        .TextMatrix(.Rows - 1,9) = Trim(mrc.Fields(10))
        .TextMatrix(.Rows - 1,10) = Trim(mrc.Fields(11))
        .TextMatrix(.Rows - 1,11) = Trim(mrc.Fields(12))
        .TextMatrix(.Rows - 1,12) = Trim(mrc.Fields(13))
        .TextMatrix(.Rows - 1,13) = Trim(mrc.Fields(14))
        .TextMatrix(.Rows - 1,14) = Trim(mrc.Fields(1)) & ""
        mrc.MoveNext
    Loop
    End With
                

End Sub


Private Sub cmdempty_Click()
'清空选项
    Combo1.Text = ""
    Combo2.Text = ""
    Combo3.Text = ""
    Combo4.Text = ""
    Combo5.Text = ""
    Combo6.Text = ""
    Combo7.Text = ""
    Combo8.Text = ""
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    
'清空上机信息

    With MSFlexGrid1
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0,14) = "学号"
    End With
                

End Sub

Private Sub cmdexit_Click()
'退出
    Unload Me
End Sub
Private Sub cmdmodify_Click()



'显示选中列的信息
    Dim txtsql As String
    Dim msgtext As String
    Dim mrc As ADODB.Recordset
    

With MSFlexGrid1

    studentinfomodify.cardno.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,0)
    studentinfomodify.studentname.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,1)
    studentinfomodify.combosex.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,8)
    studentinfomodify.dept.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,5)
    studentinfomodify.grade.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,6)
    studentinfomodify.class.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,7)
    studentinfomodify.studentno.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,3)
    studentinfomodify.money.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,4)
    studentinfomodify.status.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,9)
    studentinfomodify.information.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,10)
    studentinfomodify.combostyle.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row,13)
    
 
    Unload Me


'弹出修改窗体
    studentinfomodify.Show
End With
End Sub


结语

我的代码有些繁琐之处，聪明的你能发现更简洁的表示代码吗？

原文链接：https://www.f2er.com/vb/256205.html

猜你在找的VB相关文章

VB Format函数
Format[$] ( expr [ , fmt ] ) format 返回变体型 format$ 强制返回为文本 --------------...
vb6/ASP FORMAT MM/DD/YYYY
VB6或者ASP 格式化时间为 MM/dd/yyyy 格式，竟然没有好的办法， Format 或者FormatDateTi...
VB.net 捕获项目全局异常
在项目中添加如下代码：新建窗口来显示异常信息。 Namespace My ‘全局错误处理，新的...
实现用VB.Net/(C#)开发K/3 BOS 插件的真正可行方法
转了这一篇文章，原来一直想用C#做k3的插件开发，vb没有C#用的爽呀，这篇文章写与2011年，...
vb，wps，excel 分裂
Sub 分列() ‘以空格为分隔符，连续空格只算1个。对所选中的单元格进行处理 Dim m...
VB.NET MYSQL DataGridView 增删改查（INSERT,SELECT,UPDATE,DELETE）
  Imports MySql.Data.MySqlClient Public Class Form1 ‘ GLOBAL DECLARATIONS ...
VB.NET 使用ADODB連接資料庫滙出到EXCEL
‘導入命名空間 Imports ADODB Imports Microsoft.Office.Interop   Private Sub A1() Di...
vb.net 多线程運用 ping
Imports System.IO Imports System.Threading Imports System.Diagnostics Public Class F...
VB等待进程结束
VB运行EXE程序，并等待其运行结束 参考：https://blog.csdn.net/useway/article/details/5...
vb中去掉string数组的一部分
今天碰到一个问题，登陆的时候，如果不需要验证手机号为空，则不去验证手机号 因为登陆的时...
PHPJavaJava SEPythonC#C&C++RubyVBasp.NetGoPerlnettyDjangoDelphiJsp.NET CoreSpringFlaskSpringbootSpringMVCLuaLaravelMybatisAspGroovyThinkPHPYiiswoole
文件时间pythonm相等性PHP Warning时间问题问题解决pcntl_signal采样点wav模块动态文本调用频率限制对外暴露多个访问请求更新数据表模型结构type()方法比较速度手写体sobel算子保存模型Image类nn.Conv2dpytorch1.0kaggleDCGAN交并比range()用法打印模型反卷积卷积