第一次机房收费系统之组合查询

查询思路:
在这里插入图片描述
1.先判断组合关系,如果不使用组合关系,则查询单条内容,如果使用第一个组合关系,则查询前两条内容,如果使用第二个组合关系,则查询后两条内容,如果两个组合关系都使用,则查询三条内容。
2.查询语句是一条SQL语句,所以字段名,操作符和组合关系下拉框中的内容要和数据表中的字段或SQL语言相对应,可以在模块中定义一个转换函数field。
3.组合查询,使用循环语句将查询记录显示在表格上。

重要代码:
模块中定义转换函数:

Public Function field(a As String) As String
'选中的内容统一名字
    Select Case a
        Case "卡号"
            field = "cardno"
        Case "学号"
            field = "studentno"
        Case "姓名"
            field = "studentname"
        Case "性别"
            field = "sex"
        Case "系别"
            field = "department"
        Case "年级"
            field = "grade"
        Case "班级"
            field = "class"
        Case "与"
            field = "and "
        Case "或"
            field = "or "
        Case "="
            field = "="
        Case "<"
            field = "<"
        Case ">"
            field = ">"
        Case "<>"
            field = "<>"
        Case "上机日期"
            field = "ondate"
        Case "上机时间"
            field = "ontime"
        Case "下机日期"
            field = "offdate"
        Case "下机时间"
            field = "offtime"
        Case "消费金额"
            field = "consume"
        Case "余额"
            field = "cash"
        Case "教师"
            field = "UserID"
        Case "注册日期"
            field = "LoginDate"
        Case "注册时间"
            field = "LoginTime"
        Case "注销日期"
            field = "LogoutDate"
        Case "注销时间"
            field = "LogoutTime"
        Case "机器名"
            field = "computer"
    End Select
End Function

根据字段名中选择不同的字段使用不同控件:

Private Sub Combo1_Click(Index As Integer)
	'提示框内容先清空
    Label1.Caption = ""
'循环数组
Dim i As Integer
i = Index     '利用索引判断使用的控件
Text1(i).Text = ""
    '如果字段名是时间日期,则使用时间日期控件
    For i = 0 To 2
        If Combo1(i).Text = "上机日期" Or Combo1(i).Text = "上机时间" Or Combo1(i).Text = "下机日期" Or Combo1(i).Text = "下机时间" Then
            Label1.Caption = "日期查询格式为“yyyy/mm/dd”,时间查询格式为”hh:mm:ss“"
        End If

   If Combo1(i).Text = "上机日期" Or Combo1(i).Text = "下机日期" Then
        DTPicker1(i).Format = dtpLongDate
        DTPicker1(i).Visible = True
        Text1(i).Visible = False
    Else
        If Combo1(i).Text = "上机时间" Or Combo1(i).Text = "下机时间" Then
            DTPicker1(i).Format = dtpTime     '定义日期格式
            DTPicker1(i).Visible = True
            Text1(i).Visible = False
        Else
            DTPicker1(i).Visible = False
            Text1(i).Visible = True
        End If
    End If
    '定义文本框字符串数量
    If Combo1(i).Text = "卡号" Or Combo1(i).Text = "姓名" Then
        Text1(i).MaxLength = 10
    End If
    If Combo1(i).Text = "消费金额" Or Combo1(i).Text = "余额" Then
        Text1(i).MaxLength = 8
    End If
    Next i

End Sub

查询代码:

Private Sub Command1_Click()
Dim mrc As ADODB.Recordset
Dim txtsql As String
Dim msgtext As String
    MSFlexGrid1.rows = 2
    MSFlexGrid1.Clear
    Label1.Caption = ""
    '把日历控件的值赋给文本框
    Dim i As Integer
    For i = 0 To 2
        If DTPicker1(i).Visible = True Then
            Text1(i).Text = DTPicker1(i).Value
        End If
    Next i
    '组合查询
    txtsql = "select * from line_info where "
    '判断是否为空
    '不使用组合关系
    If Combo3(0).Text = "" And Combo3(1).Text = "" Then         '判断是否组合查询
        If Combo1(0).Text = "" And Combo1(1).Text = "" And Combo1(2).Text = "" Then   '判断字段是否输入
            Label1.Caption = "请输入字段名"
            Exit Sub
        Else
            If Combo1(0).Text <> "" Then      '如果第一行输入
                Combo1(1).Text = ""           '其他两行清空
                Combo1(2).Text = ""
                '判断是否为空
                If Combo2(0).Text = "" Or Text1(0).Text = "" Then
                    Label1.Caption = "请将第一行内容填写完整!"
                    Exit Sub
                Else
                    '判断数据类型
                    If Combo1(0).Text = "消费金额" Or Combo1(0).Text = "余额" Then
                        If Not IsNumeric(Text1(0).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(0).Text = ""
                            Text1(0).SetFocus
                            Exit Sub
                        End If
                    End If
                    '组合查询
                    txtsql = txtsql & field(Combo1(0).Text) & " " & field(Trim(Combo2(0).Text)) & "'" & Trim(Text1(0).Text) & "'"
                End If
             End If
             If Combo1(1).Text <> "" Then      '如果第二行输入
                Combo1(0).Text = ""            '其他两行清空
                Combo1(2).Text = ""
                '判断是否为空
                If Combo2(1).Text = "" And Text1(1).Text = "" Then
                    Label1.Caption = "请将第二行内容填写完整!"
                    Exit Sub
                Else
                '判断数据类型
                If Combo1(1).Text = "消费金额" Or Combo1(1).Text = "余额" Then
                        If Not IsNumeric(Text1(1).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(1).Text = ""
                            Text1(1).SetFocus
                            Exit Sub
                        End If
                    End If
                    '组合查询
                    txtsql = txtsql & field(Combo1(1).Text) & " " & field(Trim(Combo2(1).Text)) & "'" & Trim(Text1(1).Text) & "'"
                End If
            End If
             If Combo1(2).Text <> "" Then      '如果第三行输入
                Combo1(1).Text = ""            '其他两行清空
                Combo1(0).Text = ""
                If Combo2(2).Text = "" And Text1(2).Text = "" Then
                    Label1.Caption = "请将第三行内容填写完整!"
                    Exit Sub
                Else
                    '判断数据类型
                    If Combo1(2).Text = "消费金额" Or Combo1(2).Text = "余额" Then
                        If Not IsNumeric(Text1(2).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(2).Text = ""
                            Text1(2).SetFocus
                            Exit Sub
                        End If
                    End If
                    '组合查询
                    txtsql = txtsql & field(Combo1(2).Text) & " " & field(Trim(Combo2(2).Text)) & "'" & Trim(Text1(2).Text) & "'"
                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) = "备注"
        End With
        Set mrc = ExecuteSQL(txtsql, msgtext)
        If mrc.EOF = True Then
            Label1.Caption = "查询不到记录,请重新输入!"
            Exit Sub
        Else
            Do While mrc.EOF = False
                With MSFlexGrid1
                    .rows = .rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.rows - 1, 0) = Trim(mrc.Fields(1))
                    .TextMatrix(.rows - 1, 1) = Trim(mrc.Fields(3))
                    .TextMatrix(.rows - 1, 2) = Trim(mrc.Fields(6))
                    .TextMatrix(.rows - 1, 3) = Trim(mrc.Fields(7))
                    .TextMatrix(.rows - 1, 4) = Trim(mrc.Fields(8)) & ""
                    .TextMatrix(.rows - 1, 5) = Trim(mrc.Fields(9)) & ""
                    .TextMatrix(.rows - 1, 6) = Trim(mrc.Fields(11)) & ""
                    .TextMatrix(.rows - 1, 7) = Trim(mrc.Fields(12)) & ""
                    .TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(13))
                End With
                mrc.MoveNext
                
            Loop
        End If
        End If
        End If
    '前两个组合查询
    If Combo3(0).Text <> "" And Combo3(1).Text = "" Then
        '判断是否为空
        If Combo1(0).Text = "" Or Combo1(1).Text = "" Or Combo2(0).Text = "" Or Combo2(1).Text = "" Or Text1(0).Text = "" Or Text1(1).Text = "" Then
            Label1.Caption = "请将前两行内容填写完整"
            Exit Sub
        Else
                    '判断数据类型
                    If Combo1(0).Text = "消费金额" Or Combo1(0).Text = "余额" Then
                        If Not IsNumeric(Text1(0).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(0).Text = ""
                            Text1(0).SetFocus
                            Exit Sub
                        End If
                    End If
                    If Combo1(1).Text = "消费金额" Or Combo1(1).Text = "余额" Then
                        If Not IsNumeric(Text1(1).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(1).Text = ""
                            Text1(1).SetFocus
                            Exit Sub
                        End If
                    End If
            '组合查询
            txtsql = txtsql & field(Combo1(0).Text) & "" & field(Trim(Combo2(0).Text)) & "'" & Trim(Text1(0).Text) & "'"
            txtsql = txtsql & field(Combo3(0).Text) & field(Combo1(1).Text) & field(Trim(Combo2(1).Text)) & "'" & Trim(Text1(1).Text) & "'"
             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) = "备注"
        End With
        If mrc.EOF = True Then
            Label1.Caption = "查询不到记录,请重新输入!"
            Exit Sub
        Else
            Do While mrc.EOF = False
                With MSFlexGrid1
                    .rows = .rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.rows - 1, 0) = Trim(mrc.Fields(1))
                    .TextMatrix(.rows - 1, 1) = Trim(mrc.Fields(3))
                    .TextMatrix(.rows - 1, 2) = Trim(mrc.Fields(6))
                    .TextMatrix(.rows - 1, 3) = Trim(mrc.Fields(7))
                    .TextMatrix(.rows - 1, 4) = Trim(mrc.Fields(8)) & ""
                    .TextMatrix(.rows - 1, 5) = Trim(mrc.Fields(9)) & ""
                    .TextMatrix(.rows - 1, 6) = Trim(mrc.Fields(11)) & ""
                    .TextMatrix(.rows - 1, 7) = Trim(mrc.Fields(12)) & ""
                    .TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(13))
                End With
                mrc.MoveNext
                
            Loop
        End If
      End If
    End If
    
    '后两个组合查询
    If Combo3(1).Text <> "" And Combo3(0).Text = "" Then
        '判断是否为空
        If Combo1(1).Text = "" Or Combo1(2).Text = "" Or Combo2(2).Text = "" Or Combo2(1).Text = "" Or Text1(2).Text = "" Or Text1(1).Text = "" Then
            Label1.Caption = "请将后两行内容填写完整"
            Exit Sub
        Else
            '判断数据类型
            If Combo1(1).Text = "消费金额" Or Combo1(1).Text = "余额" Then
                        If Not IsNumeric(Text1(1).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(1).Text = ""
                            Text1(1).SetFocus
                            Exit Sub
                        End If
                    End If
                    If Combo1(2).Text = "消费金额" Or Combo1(2).Text = "余额" Then
                        If Not IsNumeric(Text1(2).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(2).Text = ""
                            Text1(2).SetFocus
                            Exit Sub
                        End If
                    End If
            '组合查询
            txtsql = txtsql & field(Combo1(1).Text) & "" & field(Trim(Combo2(1).Text)) & "'" & Trim(Text1(1).Text) & "'"
            txtsql = txtsql & field(Combo3(1).Text) & field(Combo1(2).Text) & field(Trim(Combo2(2).Text)) & "'" & Trim(Text1(2).Text) & "'"
             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) = "备注"
        End With
        If mrc.EOF = True Then
            Label1.Caption = "查询不到记录,请重新输入!"
            Exit Sub
        Else
            Do While mrc.EOF = False
                With MSFlexGrid1
                    .rows = .rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.rows - 1, 0) = Trim(mrc.Fields(1))
                    .TextMatrix(.rows - 1, 1) = Trim(mrc.Fields(3))
                    .TextMatrix(.rows - 1, 2) = Trim(mrc.Fields(6))
                    .TextMatrix(.rows - 1, 3) = Trim(mrc.Fields(7))
                    .TextMatrix(.rows - 1, 4) = Trim(mrc.Fields(8)) & ""
                    .TextMatrix(.rows - 1, 5) = Trim(mrc.Fields(9)) & ""
                    .TextMatrix(.rows - 1, 6) = Trim(mrc.Fields(11))
                    .TextMatrix(.rows - 1, 7) = Trim(mrc.Fields(12))
                    .TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(13))
                End With
                mrc.MoveNext
                
            Loop
        End If
      End If
      End If
      
      '三个组合查询
      If Combo3(1).Text <> "" And Combo3(0).Text <> "" Then
            '判空
            If Combo1(0).Text = "" Or Combo2(0).Text = "" Or Text1(0).Text = "" Or Combo1(1).Text = "" _
                Or Combo2(1).Text = "" Or Text1(1).Text = "" Or Combo1(2).Text = "" _
                Or Combo2(2).Text = "" Or Text1(2).Text = "" Then
                Label1.Caption = "请输入相关内容"
                Exit Sub
            Else
            '数据类型
            If Combo1(0).Text = "消费金额" Or Combo1(0).Text = "余额" Then
                        If Not IsNumeric(Text1(0).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(0).Text = ""
                            Text1(0).SetFocus
                            Exit Sub
                        End If
                    End If
                    If Combo1(1).Text = "消费金额" Or Combo1(1).Text = "余额" Then
                        If Not IsNumeric(Text1(1).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(1).Text = ""
                            Text1(1).SetFocus
                            Exit Sub
                        End If
                    End If
                    If Combo1(2).Text = "消费金额" Or Combo1(2).Text = "余额" Then
                        If Not IsNumeric(Text1(2).Text) Then
                            Label1.Caption = "请输入数字!"
                            Text1(2).Text = ""
                            Text1(2).SetFocus
                            Exit Sub
                        End If
                    End If
                    '组合查询
                txtsql = txtsql & field(Combo1(0).Text) & " " & field(Trim(Combo2(0).Text)) & "'" & Trim(Text1(0).Text) & "'"
            
                txtsql = txtsql & field(Combo3(0).Text) & field(Combo1(1).Text) & _
                field(Trim(Combo2(1).Text)) & "'" & Trim(Text1(1).Text) & "'"
            
                txtsql = txtsql & field(Combo3(1).Text) & field(Combo1(2).Text) & _
                field(Trim(Combo2(2).Text)) & "'" & Trim(Text1(2).Text) & "'"
            
                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) = "备注"
                End With
                If mrc.EOF = True Then
                    Label1.Caption = "无记录"
                    Exit Sub
                Else
                    Do While mrc.EOF = False
                        With MSFlexGrid1
                            .rows = .rows + 1
                            .CellAlignment = 4
                            .TextMatrix(.rows - 1, 0) = Trim(mrc.Fields(1))
                            .TextMatrix(.rows - 1, 1) = Trim(mrc.Fields(3))
                            .TextMatrix(.rows - 1, 2) = Trim(mrc.Fields(6))
                            .TextMatrix(.rows - 1, 3) = Trim(mrc.Fields(7))
                            .TextMatrix(.rows - 1, 4) = Trim(mrc.Fields(8)) & ""
                            .TextMatrix(.rows - 1, 5) = Trim(mrc.Fields(9)) & ""
                            .TextMatrix(.rows - 1, 6) = Trim(mrc.Fields(11)) & ""
                            .TextMatrix(.rows - 1, 7) = Trim(mrc.Fields(12))
                            .TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(13))
                        End With
                        mrc.MoveNext
            
                    Loop
              End If
        End If
    End If
 
End Sub
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 4
    评论
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

孙正嗯大正

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值