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

于 2019-12-23 11:36:13 发布
阅读量151 收藏
点赞数 1
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_42678716/article/details/103660807

下面来说查询：

不使用组合查询：

'不使用组合关系
    If comboCbR1(0).Text = "" And comboCbR1(1).Text = "" Then
     '判断字段是否为空
        If combo1(0).Text = "" And combo1(1).Text = "" And combo1(2).Text = "" Then
            MsgBox "字段名为空，请输入字段名！", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
        '使用第一行查询
            If combo1(0).Text <> "" Then        '如果第一行字段不为空
                combo1(1).Text = ""                 '二三行字段为空
                combo1(2).Text = ""
        '判断第一行查询条件是否输入完整
                If comboS1(0).Text = "" Or txtIC1(0).Text = "" Then
                    MsgBox "请将第一行查询条件输入完整", vbOKOnly + vbExclamation, "警告"
                     Exit Sub
                Else
                 '组合查询
                txtsql = txtsql & "" & Field(Trim(combo1(0).Text)) & "" & Trim(comboS1(0).Text) & "'" & Trim(txtIC1(0).Text) & "'"
                End If
            End If
            If combo1(1).Text <> "" Then     '如果第二行字段不为空
                combo1(0).Text = ""                 '一三行字段为空
                combo1(2).Text = ""
            '判断第二行查询条件是否输入完整
                If comboS1(1).Text = "" Or txtIC1(1).Text = "" Then
                    MsgBox "请将第二行查询条件输入完整", vbOKOnly + vbExclamation, "警告"
                    Exit Sub
                Else
                 '组合查询
                txtsql = txtsql & Field(Trim(combo1(1).Text)) & "" & Trim(comboS1(1).Text) & "'" & Trim(txtIC1(1).Text) & "'"
                End If
            End If
            If combo1(2).Text <> "" Then     '如果第三行字段不为空
                combo1(0).Text = ""                 '一二行字段为空
                combo1(1).Text = ""
            '判断第三行查询条件是否输入完整
                If comboS1(2).Text = "" Or txtIC1(2).Text = "" Then
                    MsgBox "请将第三行查询条件输入完整", vbOKOnly + vbExclamation, "警告"
                    Exit Sub
                Else
                 '组合查询
                txtsql = txtsql & Field(Trim(combo1(2).Text)) & "" & Trim(comboS1(2).Text) & "'" & Trim(txtIC1(2).Text) & "'"
                End If
            End If
            With MSHFlexGrid1
            .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) = "上机时间"
              
            End With
             Set mrc = executeSQL(txtsql, msgtext)
            If mrc.EOF = True Then
                MsgBox "没有查询到记录，请重新输入！", vbOKOnly + vbExclamation, "提示"
                Exit Sub
            Else
                Do While mrc.EOF = False
                    With MSHFlexGrid1
                    .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(1))
                    .TextMatrix(.rows - 1, 3) = Trim(mrc.Fields(3))
                    .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(14))
                    .TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(7))
                    .TextMatrix(.rows - 1, 9) = Trim(mrc.Fields(10))
                    .TextMatrix(.rows - 1, 10) = Trim(mrc.Fields(12))
                    .TextMatrix(.rows - 1, 11) = Trim(mrc.Fields(13))
                    End With
                    mrc.MoveNext
                Loop
            End If
           End If
      End If

前两个条件的组合查询：

'前两个组合查询
           If comboCbR1(0).Text <> "" And comboCbR1(1).Text = "" Then
                If combo1(0).Text = "" Or combo1(1).Text = "" Or comboS1(0).Text = "" Or comboS1(1).Text = "" Or txtIC1(0).Text = "" Or txtIC1(1).Text = "" Then
                    MsgBox "请将前两行查询条件填写完整！", vbOKOnly + vbExclamation, "警告"
                    Exit Sub
                Else
                txtsql = txtsql & Field(Trim(combo1(0).Text)) & "" & Trim(comboS1(0).Text) & "'" & Trim(txtIC1(0).Text) & "'"
                txtsql = txtsql & " " & relation(comboCbR1(0).Text) & " " & Field(Trim(combo1(1).Text)) & Trim(comboS1(1).Text) & "'" & Trim(txtIC1(1).Text) & "'"
                Set mrc = executeSQL(txtsql, msgtext)
                  With MSHFlexGrid1
            .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) = "上机时间"
              
            End With
            Set mrc = executeSQL(txtsql, msgtext)
            If mrc.EOF = True Then
                MsgBox "没有查询到记录，请重新输入！", vbOKOnly + vbExclamation, "提示"
                Exit Sub
            Else
                Do While mrc.EOF = False
                    With MSHFlexGrid1
                    .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(1))
                    .TextMatrix(.rows - 1, 3) = Trim(mrc.Fields(3))
                    .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(14))
                    .TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(7))
                    .TextMatrix(.rows - 1, 9) = Trim(mrc.Fields(10))
                    .TextMatrix(.rows - 1, 10) = Trim(mrc.Fields(12))
                    .TextMatrix(.rows - 1, 11) = Trim(mrc.Fields(13))
                    End With
                    mrc.MoveNext
                Loop
            End If
           End If
      End If

后两个条件的组合查询：

'后两个组合查询
           If comboCbR1(1).Text <> "" And comboCbR1(0).Text = "" Then
                If combo1(1).Text = "" Or combo1(2).Text = "" Or comboS1(1).Text = "" Or comboS1(2).Text = "" Or txtIC1(1).Text = "" Or txtIC1(2).Text = "" Then
                    MsgBox "请将后两行查询条件填写完整！", vbOKOnly + vbExclamation, "警告"
                    Exit Sub
                Else
                txtsql = txtsql & Field(Trim(combo1(1).Text)) & "" & Trim(comboS1(1).Text) & "" & Trim(txtIC1(1).Text) & "'"
                txtsql = txtsql & " " & relation(comboCbR1(1).Text) & " " & Field(Trim(combo1(2).Text)) & Trim(comboS1(2).Text) & "'" & Trim(txtIC1(2).Text) & "'"
                Set mrc = executeSQL(txtsql, msgtext)
                  With MSHFlexGrid1
            .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) = "上机时间"
              
            End With
           If mrc.EOF = True Then
                MsgBox "没有查询到记录，请重新输入！", vbOKOnly + vbExclamation, "提示"
                Exit Sub
            Else
                Do While mrc.EOF = False
                    With MSHFlexGrid1
                    .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(1))
                    .TextMatrix(.rows - 1, 3) = Trim(mrc.Fields(3))
                    .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(14))
                    .TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(7))
                    .TextMatrix(.rows - 1, 9) = Trim(mrc.Fields(10))
                    .TextMatrix(.rows - 1, 10) = Trim(mrc.Fields(12))
                    .TextMatrix(.rows - 1, 11) = Trim(mrc.Fields(13))
                    End With
                    mrc.MoveNext
                Loop
            End If
           End If
      End If

三行条件的组合查询：

'三个组合查询
           If comboCbR1(0).Text <> "" And comboCbR1(1).Text <> "" Then
                If combo1(0).Text = "" Or combo1(1).Text = "" Or combo1(2).Text = "" Or comboS1(0).Text = "" Or comboS1(1).Text = "" Or comboS1(2).Text = "" Or txtIC1(0).Text = "" Or txtIC1(1).Text = "" Or txtIC1(2).Text = "" Then
                    MsgBox "请将三查询条件填写完整！", vbOKOnly + vbExclamation, "警告"
                    Exit Sub
                Else
                txtsql = txtsql & Field(combo1(2).Text) & "" & Trim(comboS1(2).Text) & "'" & Trim(txtIC1(2).Text) & "'"
                txtsql = txtsql & " " & relation(comboCbR1(0).Text) & " " & Field(Trim(combo1(1).Text)) & Trim(comboS1(1).Text) & "'" & Trim(txtIC1(1).Text) & "'"
                txtsql = txtsql & " " & relation(comboCbR1(1).Text) & " " & Field(Trim(combo1(2).Text)) & Trim(comboS1(2).Text) & "'" & Trim(txtIC1(2).Text) & "'"
               
               
                Set mrc = executeSQL(txtsql, msgtext)
                  With MSHFlexGrid1
            .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) = "上机时间"
              
            End With
            Set mrc = executeSQL(txtsql, msgtext)
            If mrc.EOF = True Then
                MsgBox "没有查询到记录，请重新输入！", vbOKOnly + vbExclamation, "提示"
                Exit Sub
            Else
                Do While mrc.EOF = False
                    With MSHFlexGrid1
                    .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(1))
                    .TextMatrix(.rows - 1, 3) = Trim(mrc.Fields(3))
                    .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(14))
                    .TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(7))
                    .TextMatrix(.rows - 1, 9) = Trim(mrc.Fields(10))
                    .TextMatrix(.rows - 1, 10) = Trim(mrc.Fields(12))
                    .TextMatrix(.rows - 1, 11) = Trim(mrc.Fields(13))
                    End With
                    mrc.MoveNext
                Loop
            End If
           End If
      End If
End Sub

本窗体有修改按钮，里面还嵌套一个窗体

确认修改代码：

txtsql = "select * from student_Info where cardno='" & Trim(tmpcardno) & "'"
    Set mrcst = executeSQL(txtsql, msgtext)
    If Not (mrcst.BOF Or mrcst.EOF) Then
        mrcst.Fields(0) = Trim(txtcardno.Text)
        mrcst.Fields(1) = Trim(txtstudentno.Text)
        mrcst.Fields(2) = Trim(txtstudentname.Text)
        mrcst.Fields(3) = Trim(Combosex.Text)
        mrcst.Fields(4) = Trim(txtdept.Text)
        mrcst.Fields(5) = Trim(txtgrade.Text)
        mrcst.Fields(6) = Trim(txtclass.Text)
'        mrcst.Fields(7) = Trim(txtcash.Text)
        mrcst.Fields(8) = Trim(txtExplain.Text)
        mrcst.Fields(10) = Trim(txtstatus.Text)
        mrcst.Fields(14) = Trim(Combotype.Text)
        mrcst.Update
        
        Do While Not mrcst.EOF
        With BasicInfoMaintain.MSHFlexGrid1
        .rows = .rows + 1
        .CellAlignment = 4
        .TextMatrix(.rows - 1, 0) = Trim(mrcst.Fields(0))
        .TextMatrix(.rows - 1, 1) = Trim(mrcst.Fields(1))
        .TextMatrix(.rows - 1, 2) = Trim(mrcst.Fields(2))
        .TextMatrix(.rows - 1, 3) = Trim(mrcst.Fields(3))
        .TextMatrix(.rows - 1, 4) = Trim(mrcst.Fields(4))
        .TextMatrix(.rows - 1, 5) = Trim(mrcst.Fields(5))
        .TextMatrix(.rows - 1, 6) = Trim(mrcst.Fields(6))
        .TextMatrix(.rows - 1, 7) = Trim(mrcst.Fields(7))
        .TextMatrix(.rows - 1, 8) = Trim(mrcst.Fields(8))
        .TextMatrix(.rows - 1, 9) = Trim(mrcst.Fields(10))
        .TextMatrix(.rows - 1, 10) = Trim(mrcst.Fields(14))

        mrcst.MoveNext
    End With
       Loop
       MsgBox "修改信息成功！", vbOKOnly, "警告"
        '修改信息后，重新加载到基本信息维护窗体的查询结果中
        BasicInfoMaintain.cmdmodify.Value = True
       
        mrcst.Close
     End If
    Me.Hide

这就是组合查询学生基本信息维护的代码了。做之前不要给自己设限哦！相信自己！

评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值