【机房收费系统】组合查询

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

机房组合查询我感觉是在这上面时间最多的一个，不是太难但就是有很多的不容易让人想到的地方。下面我就来总结一下。

1、查询条件strA单独查询，组合关系comboZh1符合的时候strA和StrB一起查询，组合关系comboZh2符合的时候strA、StrB、StrC三行条件一起查询。

2、当字段名是性别的时候操作符中的符号只能是“=”和“< >”并且提示只能输入“男” 或“女”。当字段名为时间或者是日期的时候，txtYc1要有限制只能输入日期或者是时间

3、组合查询ComboZh1为空的时候StrB不能用或者是隐藏。只有单击ComboZh1的时候ComboZh2才能用。否则无效。（这个想着很是费劲，一定要好好的思考。。。）



接下来就是展示一下我的代码。

先应该在新建的模块中写到（这会为你省掉很多的麻烦。。。）

<span style="font-size:14px;">Option Explicit

Public Function FiledName(strFiledName As String) As String
'将字段名转化成数据库表中的字段名
    Select Case strFiledName
        Case "卡号"
            FiledName = "cardno"
        Case "学号"
            FiledName = "studentNo"
        Case "姓名"
            FiledName = "studentName"
        Case "性别"
            FiledName = "sex"
        Case "系别"
            FiledName = "department"
        Case "年级"
            FiledName = "grade"
        Case "班级"
            FiledName = "class"
        
    End Select
End Function

'将操作符转化成数据库表中的字段名
Public Function Operate(strOperate As String)
   Select Case strOperate
      Case "="
         Operate = "="
      Case "<"
         Operate = "<"
      Case ">"
         Operate = ">"
      Case "<>"
         Operate = "<>"
   End Select
End Function

'将连接符转化成数据库表中的字段名
Public Function Connect(strConnect As String) As String
    Select Case strConnect
       Case "或"
          Connect = "or"
       Case "与"
          Connect = "and"
       Case ""
          Connect = ""
    End Select
End Function

Function chkkey(t As String,k As Integer) As Integer   '只能输入数字
    chkkey = k
    If k = 46 And InStr(t,".") = 0 Then
        Exit Function
    End If
    If k = 8 Then
        Exit Function
    End If
    If k < 48 Or k > 57 Then
        chkkey = 0
    End If
    
    
End Function</span>

 
<span style="font-size:14px; font-family: Arial,Helvetica,sans-serif; background-color: rgb(255,255,255);"><span style="white-space:pre">	<span style="font-size:14px; white-space: pre;">之后</span></span>就是要在窗体中写你的代码了。</span>
<span style="font-size:14px;">Option Explicit
Dim nowrow As Integer

Private Sub ComboZd1_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd1.Text
       Case "性别"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem "<>"
       Case "姓名"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem "<>"
        Case "系别"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem "<>"
        Case "卡号"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem ">"
            ComboOpt1.AddItem "<"
            ComboOpt1.AddItem "<>"
        Case "学号"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem ">"
            ComboOpt1.AddItem "<"
            ComboOpt1.AddItem "<>"
        Case "年级"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem ">"
            ComboOpt1.AddItem "<"
            ComboOpt1.AddItem "<>"
        Case "班级"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem ">"
            ComboOpt1.AddItem "<"
            ComboOpt1.AddItem "<>"
         Case ""
            ComboOpt1.Clear
            ComboOpt1.AddItem ""
End Select
'性别选择男女
If ComboZd1.Text = "性别" Then
    MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ",vbOKOnly + vbExclamation,"提示"
End If
End Sub
Private Sub ComboZd2_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd2.Text
       Case "性别"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem "<>"
       Case "姓名"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem "<>"
        Case "系别"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem "<>"
        Case "卡号"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem ">"
            ComboOpt2.AddItem "<"
            ComboOpt2.AddItem "<>"
        Case "学号"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem ">"
            ComboOpt2.AddItem "<"
            ComboOpt2.AddItem "<>"
        Case "年级"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem ">"
            ComboOpt2.AddItem "<"
            ComboOpt2.AddItem "<>"
        Case "班级"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem ">"
            ComboOpt2.AddItem "<"
            ComboOpt2.AddItem "<>"
        Case ""
            ComboOpt2.Clear
            ComboOpt2.AddItem ""
End Select
'性别选择男女
If ComboZd2.Text = "性别" Then
    MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ","提示"
End If
End Sub
Private Sub ComboZd3_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd3.Text
       Case "性别"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem "<>"
       Case "姓名"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem "<>"
        Case "系别"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem "<>"
        Case "卡号"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem ">"
            ComboOpt3.AddItem "<"
            ComboOpt3.AddItem "<>"
        Case "学号"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem ">"
            ComboOpt3.AddItem "<"
            ComboOpt3.AddItem "<>"
        Case "年级"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem ">"
            ComboOpt3.AddItem "<"
            ComboOpt3.AddItem "<>"
        Case "班级"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem ">"
            ComboOpt3.AddItem "<"
            ComboOpt3.AddItem "<>"
        Case ""
            ComboOpt3.Clear
            ComboOpt3.AddItem ""
End Select
'性别选择男女
If ComboZd3.Text = "性别" Then
    MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ","提示"
End If
End Sub
Private Sub ComboZh1_Click()
'组合关系是1个时候第2行的选择显示
If ComboZh1.Text <> "" Then
   ComboZd2.Enabled = True
   ComboOpt2.Enabled = True
   txtYc2.Enabled = True
   ComboZh2.Enabled = True
Else
    ComboZd2.Enabled = False
    ComboOpt2.Enabled = False
    txtYc2.Enabled = False
    ComboZh2.Enabled = False
    ComboZd3.Enabled = False
   ComboOpt3.Enabled = False
   txtYc3.Enabled = False
    txtYc2.Text = ""
    txtYc3.Text = ""
End If


End Sub


Private Sub ComboZh2_Click()
'组合关系是2个时候第3行的选择显示
If ComboZh2.Text <> "" Then
   ComboZd3.Enabled = True
   ComboOpt3.Enabled = True
   txtYc3.Enabled = True
End If
End Sub
te Sub Command1_Click()
'Command1是清空
    ComboOpt1.Text = ""
    ComboOpt2.Text = ""
    ComboOpt3.Text = ""
    ComboZd1.Text = ""
    ComboZd2.Text = ""
    ComboZd3.Text = ""
    ComboZh1.Text = ""
    ComboZh2.Text = ""
    txtYc1.Text = ""
    txtYc2.Text = ""
    MSHFGd.Clear
   
End Sub

Private Sub Command2_Click()
'Command2是查询
    Dim txtsql As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset    'mrc是student数据库
    Dim strA As String   '定义三个保存sql语句的字符串 代表第一种查询条件
    Dim strB As String  '代表第二种
    Dim strC As String  '代表第三种
    
    txtsql = "select * from student_Info where "
 
    
    strA = txtsql & FiledName(ComboZd1.Text) & Operate(ComboOpt1.Text) & "'" & Trim(txtYc1.Text) & "'"
    strB = strA & "" & Connect(ComboZh1.Text) & " " & FiledName(ComboZd2.Text) & Operate(ComboOpt2.Text) & "'" & Trim(txtYc2.Text) & "'"
    strC = strB & Connect(ComboZh2.Text) & " " & FiledName(ComboZd2.Text) & Operate(ComboOpt3.Text) & "'" & Trim(txtYc3.Text) & "'"

    If Trim(ComboZh1.Text) = "" Then   '没有组合关系的时候就是一行查询条件
        If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Then
            MsgBox "请输入完整的查询条件","提示"
            Exit Sub
        Else
            txtsql = strA
        Set mrc = Executesql(txtsql,MsgText)
        End If
    End If
    
    If Trim(ComboZh1.Text) <> "" Then  '有组合关系的时候是两行和起来的查询条件
        If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Or Trim(ComboZd2.Text) = "" Or Trim(ComboOpt2.Text) = "" Or Trim(txtYc2.Text) = "" Then
            MsgBox "请输入完整的查询条件","提示"
            Exit Sub
        Else
            txtsql = strB
            Set mrc = Executesql(txtsql,MsgText)
        End If
    End If
    
    If Trim(ComboZh2.Text) <> "" Then   '两个组合关系时候的查询条件 三行查询条件一起
        If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Or Trim(ComboZd2.Text) = "" Or Trim(ComboOpt2.Text) = "" Or Trim(txtYc2.Text) = "" Or Trim(ComboZd3.Text) = "" Or Trim(ComboOpt3.Text) = "" Or Trim(txtYc3.Text) = "" Then
            MsgBox "请输入完整的查询条件","提示"
            Exit Sub
        Else
            txtsql = strC
            Set mrc = Executesql(txtsql,MsgText)
        End If
    End If
    

    If mrc.EOF Then
        MSHFGd.Clear
        MsgBox "暂无查询结果","提示"
        Exit Sub
    End If
    
    With MSHFGd     '表示的是设置MSHFlexGrid控件第i行，第j列的文本内容（注意标题算第一行）
        .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 Not mrc.EOF
        .Rows = .Rows + 1   '防止空白行的出现
        .CellAlignment = 4
        .TextMatrix(.Rows - 1,0) = Trim(mrc.Fields(1))   '与数据库进行对应
        .TextMatrix(.Rows - 1,1) = Trim(mrc.Fields(2))
        .TextMatrix(.Rows - 1,2) = Trim(mrc.Fields(0))
        .TextMatrix(.Rows - 1,3) = Trim(mrc.Fields(7))
        .TextMatrix(.Rows - 1,4) = Trim(mrc.Fields(4))
        .TextMatrix(.Rows - 1,5) = Trim(mrc.Fields(5))
        .TextMatrix(.Rows - 1,6) = Trim(mrc.Fields(6))
        .TextMatrix(.Rows - 1,7) = Trim(mrc.Fields(3))
        .TextMatrix(.Rows - 1,8) = Trim(mrc.Fields(10))
        .TextMatrix(.Rows - 1,9) = Trim(mrc.Fields(8))
        .TextMatrix(.Rows - 1,10) = Trim(mrc.Fields(14))
        .TextMatrix(.Rows - 1,11) = mrc.Fields(12) & ""
        .TextMatrix(.Rows - 1,12) = mrc.Fields(13) & ""
        mrc.MoveNext
        Loop
        End With
        AdjustColWidth frmcxsjbxxwh,MSHFGd  '调节列宽
        mrc.Close
    
End Sub

Private Sub Command3_Click()
'Command3_是退出
  Unload Me
End Sub

Private Sub Command4_Click()
'修改信息
If MSHFGd.MouseRow = 0 Then
    MsgBox "请选择学生","警告"
Else
    frmcxsjbxxwh.Hide
    SetParent frmcModifySI.hWnd,frmmain.Picture1.hWnd   '‘显示修改学生信息窗体
    frmcModifySI.Width = 12180
    frmcModifySI.Height = 9255
 End If
End Sub


Private Sub Form_Activate()
'把当前不是活动的窗体最小化
Dim a As Form
For Each a In Forms
    If a.Name <> frmmain.Name And a.Name <> Me.Name Then
        a.WindowState = 1
    End If
Next
    
End Sub
Private Sub Form_Load()

    With MSHFGd      '表示的是设置MSHFlexGrid控件第i行，第j列的文本内容（注意标题算第一行）
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0,12) = "时间"
        End With
    

    ComboZd1.AddItem "学号"     '添加字段名
    ComboZd1.AddItem "姓名"
    ComboZd1.AddItem "卡号"
    ComboZd1.AddItem "系别"
    ComboZd1.AddItem "年级"
    ComboZd1.AddItem "班级"
    ComboZd1.AddItem "性别"
    ComboZd1.AddItem ""

    ComboZd2.AddItem "学号"
    ComboZd2.AddItem "姓名"
    ComboZd2.AddItem "卡号"
    ComboZd2.AddItem "系别"
    ComboZd2.AddItem "年级"
    ComboZd2.AddItem "班级"
    ComboZd2.AddItem "性别"
      ComboZd2.AddItem ""
      
    ComboZd3.AddItem "学号"
    ComboZd3.AddItem "姓名"
    ComboZd3.AddItem "卡号"
    ComboZd3.AddItem "系别"
    ComboZd3.AddItem "年级"
    ComboZd3.AddItem "班级"
    ComboZd3.AddItem "性别"
      ComboZd3.AddItem ""

     
    ComboZh1.AddItem "或"
    ComboZh1.AddItem "与"
    ComboZh1.AddItem ""
    
    ComboZh2.AddItem "或"       '添加组合关系
    ComboZh2.AddItem "与"
    ComboZh2.AddItem ""
    
    ComboZd2.Enabled = False
    ComboZd3.Enabled = False
    ComboOpt2.Enabled = False
    ComboOpt3.Enabled = False
    txtYc2.Enabled = False
    txtYc3.Enabled = False
    ComboZh2.Enabled = False
End Sub

Public Sub MSHFgd_Mousedown(button As Integer,Shift As Integer,X As Single,Y As Single)
    With MSHFGd
    .Row = .MouseRow  '当前活动单元就是鼠标按下的单元
    nowrow = .Row   '把单元传给nowrow
    End With
End Sub
Public Sub MSHFgd_mouseup(button As Integer,Y As Single)
    With MSHFGd
        .RowSel = nowrow
    End With
    
End Sub



Private Sub txtYc1_Click()
   txtYc1.SetFocus
End Sub


Private Sub txtYc2_Click()
   txtYc2.SetFocus
End Sub


Private Sub txtYc3_Click()
   txtYc3.SetFocus
End Sub

 
Private Sub txtYc1_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd1.Text = "卡号") Then
    KeyAscii = chkkey(txtYc1,KeyAscii)
Else
    If (ComboZd1.Text = "学号") Then
    KeyAscii = chkkey(txtYc1,KeyAscii)
End If
End If

End Sub
 
Private Sub txtYc2_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd2.Text = "卡号") Then
    KeyAscii = chkkey(txtYc2,KeyAscii)
Else
    If (ComboZd2.Text = "学号") Then
    KeyAscii = chkkey(txtYc2,KeyAscii)
End If
End If
End Sub
 
Private Sub txtYc3_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd3.Text = "卡号") Then
    KeyAscii = chkkey(txtYc3,KeyAscii)
Else
    If (ComboZd3.Text = "学号") Then
    KeyAscii = chkkey(txtYc3,KeyAscii)
End If
End If

End Sub

</span>
好了这就是我的代码了。虽然过程很是艰辛但是结果还是很美好的。

猜你在找的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()用法打印模型反卷积卷积