vb吐槽班07 一套combo带走！

最新推荐文章于 2013-12-02 21:53:40 发布

Rephilo

最新推荐文章于 2013-12-02 21:53:40 发布

阅读量1.3k

点赞数

分类专栏：吐槽班

本文链接：https://blog.csdn.net/u010092167/article/details/12424907

版权

吐槽班专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Hello，各位亲爱的观众，欢迎收看新一期的大型情感类电脑连续节目，vb吐槽班。

这次是晚了两天啊，没关系，都不大点事，博客嘛，写起来要对得起自己，发出来要对得起观众。感觉已经写了好多期了，最近是想着给吐槽班整个口号啥的，这样童鞋们不知道咋回复的时候，把口号复制一遍就可以了嘛。本来想着是吧，就干脆用组织的口号“天王盖地虎”吧，碰上组织中的人还能互粉个啥的，仔细一想这万一有捣乱的人刷“楼主二百五”，那这个节奏就不对了。。。

后来是想着用小满的口号啊“从零开始搞学习，吐槽吐槽我看行。”怎么感觉说出来就已经节操粉碎性骨折。。。长此以往，肯定会从吐槽班变成下限降低班或是节操毁灭班啥的。。。

这两天也是被组合查询烦的不行，后来我也是没办法了，花了点时间，搞了个图，把简单的流程画了一下。画着画着就感觉这仨窗体就能搞到一块去，于是又画了一个搞到一块的图，发现也没多复杂，只要是逻辑清楚了，代码都是水到渠成的。

有人说了：你这说画图画图的，图呢？没图你说个虚空。

没办法，我那图是手画的，想看自己画一个。

首先，作为一个很懒的人我觉得最麻烦的就是如何写SQL语句，一开始的想法就是按照原型做的分三个窗体，然后就select from三个数据库。关键是吧。。。实在是太懒了。。。我就懒得做三个，就想做成一个。后来想到，我能在代码中修改搜索的字段名，为什么不试着修改一下要搜索的数据库。

搞起！

先把选择要查询信息的代码贴了，我的想法就是，当我要查学生信息的时候，下面的字段名里面的内容全都是学生信息的，要查工作日志的时候，字段名就都是工作日志中的。

Private Sub comboInfo_Click()

'选择不同的查询内容，进行不同的修改。。。


     '先给操作符和关系添加内容
    comboName1.Clear
    comboOperator1.Clear
    comboRelation1.Clear
    
    comboOperator1.AddItem "="
    comboOperator1.AddItem ">"
    comboOperator1.AddItem "<"
    comboOperator1.AddItem "<>"

    comboRelation1.AddItem "与"
    comboRelation1.AddItem "或"
    comboRelation1.AddItem ""
    
    txtInfo1.Enabled = True
    
    '根据需要的不同内容，向字段中添加不同的东西
    If Trim(comboInfo.Text) = "学生信息" Then
        '选学生信息时添加
        
        cmdModify.Visible = True
        cmdOutPut.Visible = False
        
        comboName1.AddItem "卡号"
        comboName1.AddItem "学号"
        comboName1.AddItem "姓名"
        comboName1.AddItem "性别"
        comboName1.AddItem "学院"
        comboName1.AddItem "年级"
        comboName1.AddItem "班级"
        
    ElseIf Trim(comboInfo.Text) = "学生上机信息" Then
        '选上机信息时添加
        cmdOutPut.Visible = True
        cmdModify.Visible = False
        
        comboName1.AddItem "卡号"
        comboName1.AddItem "姓名"
        comboName1.AddItem "上机日期"
        comboName1.AddItem "上机时间"
        comboName1.AddItem "下机日期"
        comboName1.AddItem "下机时间"
        comboName1.AddItem "消费金额"
        comboName1.AddItem "余额"
        
    ElseIf Trim(comboInfo.Text) = "工作日志" Then
        '选工作日志时添加
        cmdOutPut.Visible = True
        cmdModify.Visible = False
        
        comboName1.AddItem "用户名"
        comboName1.AddItem "登陆日期"
        comboName1.AddItem "登录时间"
        comboName1.AddItem "注销日期"
        comboName1.AddItem "注销时间"
    Else
        '如果出现什么都没选的情况，清空所有的可选内容
        comboName1.Clear
        comboOperator1.Clear
        comboRelation1.Clear
    End If
End Sub

之后，当选择了关系“与”还有“或”的时候，第二行查询组合框才会添加内容，也可以选择关系为空，并且清空第二行查询组合框的内容。

Private Sub comboRelation1_Click()
    '当选择为空时，清空第二行组合框中的内容
    If Trim(comboRelation1.Text) <> "" Then
        comboName2.Clear
        comboOperator2.Clear
        comboRelation2.Clear
        
        comboOperator2.AddItem "="
        comboOperator2.AddItem ">"
        comboOperator2.AddItem "<"
        comboOperator2.AddItem "<>"
    
        comboRelation2.AddItem "与"
        comboRelation2.AddItem "或"
        comboRelation2.AddItem ""
        
        txtInfo2.Enabled = True
        '根据需要的不同内容，向字段中添加不同的东西
        If Trim(comboInfo.Text) = "学生信息" Then
            '选学生信息时添加
            
            comboName2.AddItem "卡号"
            comboName2.AddItem "学号"
            comboName2.AddItem "姓名"
            comboName2.AddItem "性别"
            comboName2.AddItem "学院"
            comboName2.AddItem "年级"
            comboName2.AddItem "班级"
            
        ElseIf Trim(comboInfo.Text) = "学生上机信息" Then
            '选上机信息时添加
            
            comboName2.AddItem "卡号"
            comboName2.AddItem "姓名"
            comboName2.AddItem "上机日期"
            comboName2.AddItem "上机时间"
            comboName2.AddItem "下机日期"
            comboName2.AddItem "下机时间"
            comboName2.AddItem "消费金额"
            comboName2.AddItem "余额"
           
            
        ElseIf Trim(comboInfo.Text) = "工作日志" Then
            '选工作日志时添加
            
            comboName2.AddItem "用户名"
            comboName2.AddItem "登陆日期"
            comboName2.AddItem "登录时间"
            comboName2.AddItem "注销日期"
            comboName2.AddItem "注销时间"
            
        
        End If
    Else    '如果出现什么都没选的情况，清空所有的可选内容
        comboName2.Clear
        comboOperator2.Clear
        comboRelation2.Clear
    End If
End Sub

这样基本上就完成了一半了，接着就是查询并把信息写入MSHFlexGrid。刚说了，我很懒，因为前面选择不同的选项，SQL语句就会有不同的修改，每次查询之前都要判断要查询的库和内容，挺麻烦的。就写了两个非主流的自定义函数。。。

Public Function FieldRelation(combobox1 As ComboBox) As String
'用来判断关系组合框中的内容，并返回写入SQL语句的内容
    If Trim(combobox1.Text) = "与" Then
        FieldRelation = "and"
    ElseIf Trim(combobox1.Text) = "或" Then
        FieldRelation = "or"
    Else
        FieldRelation = ""
    End If
End Function

Public Function FieldName(combobox1 As ComboBox, combobox2 As ComboBox) As String
'用来判断字段名组合框中的内容，并返回写入SQL语句的内容
    If Trim(combobox1.Text) = "学生信息" Then
        If Trim(combobox2.Text) = "卡号" Then
            FieldName = "CardID"
        ElseIf Trim(combobox2.Text) = "学号" Then
            FieldName = "StuID"
        ElseIf Trim(combobox2.Text) = "姓名" Then
            FieldName = "Name"
        ElseIf Trim(combobox2.Text) = "性别" Then
            FieldName = "Sex"
        ElseIf Trim(combobox2.Text) = "学院" Then
            FieldName = "Department"
        ElseIf Trim(combobox2.Text) = "年级" Then
            FieldName = "Grade"
        ElseIf Trim(combobox2.Text) = "班级" Then
            FieldName = "Class"
        End If
    ElseIf Trim(combobox1.Text) = "学生上机信息" Then
        If Trim(combobox2.Text) = "卡号" Then
            FieldName = "CardID"
        ElseIf Trim(combobox2.Text) = "姓名" Then
            FieldName = "Name"
        ElseIf Trim(combobox2.Text) = "上机日期" Then
            FieldName = "Logindate"
        ElseIf Trim(combobox2.Text) = "上机时间" Then
            FieldName = "LoginTime"
        ElseIf Trim(combobox2.Text) = "下机日期" Then
            FieldName = "LogoutDate"
        ElseIf Trim(combobox2.Text) = "下机时间" Then
            FieldName = "LogoutTime"
        ElseIf Trim(combobox2.Text) = "消费金额" Then
            FieldName = "Consume"
        ElseIf Trim(combobox2.Text) = "余额" Then
            FieldName = "cash"
        End If
    ElseIf Trim(combobox1.Text) = "工作日志" Then
        If Trim(combobox2.Text) = "用户名" Then
            FieldName = "UserID"
        ElseIf Trim(combobox2.Text) = "登陆日期" Then
            FieldName = "LoginDate"
        ElseIf Trim(combobox2.Text) = "登录时间" Then
            FieldName = "LoginTime"
        ElseIf Trim(combobox2.Text) = "注销日期" Then
            FieldName = "LogoutDate"
        ElseIf Trim(combobox2.Text) = "注销时间" Then
            FieldName = "LogoutTime"
        End If
    End If
End Function

怎么都感觉这俩货给自定义函数这么高端大气上档次的名字丢脸了。。。

接下来的事情就简单了，就是查询然后写数据了。

Private Sub cmdInquire_Click()
'貌似最难得一部分来了，爆发吧！小宇宙！
    
    '定义用来查询数据的sql语句，记录集
    Dim txtSQL As String
    Dim Msgtext As String
    Dim mrc As Recordset
    
    Dim Name As String          '用来记录字段名
    Dim Relation As String      '用来记录关系
    Dim Data As String          '用来记录使用的数据库
    
    '喜闻乐见的检查输入环节
    If Trim(comboInfo.Text) = "" Then
        MsgBox "请选择要查询的信息", vbOKOnly + vbExclamation, "警告"
        comboInfo.SetFocus
        Exit Sub
    End If
    
    If Trim(comboName1.Text) = "" Then
        MsgBox "请选择要查询的字段", vbOKOnly + vbExclamation, "警告"
        comboName1.SetFocus
        Exit Sub
    End If
    
    If Trim(comboOperator1.Text) = "" Then
        MsgBox "请选择运算符", vbOKOnly + vbExclamation, "警告"
        comboOperator1.SetFocus
        Exit Sub
    End If
    
    If Trim(txtInfo1.Text) = "" Then
        MsgBox "请输入要查询的内容", vbOKCancel + vbExclamation, "警告"
        txtInfo1.SetFocus
        Exit Sub
    End If
    
    '检查第二条输入
    If Trim(comboRelation1.Text) <> "" Then
        If Trim(comboName2.Text) = "" Then
            MsgBox "请选择第二条要查询的字段", vbOKOnly + vbExclamation, "警告"
            comboName2.SetFocus
            Exit Sub
        ElseIf Trim(txtInfo2.Text) = "" Then
            MsgBox "请输入第二条要查询的内容", vbOKCancel + vbExclamation, "警告"
            txtInfo2.SetFocus
            Exit Sub
        ElseIf Trim(comboOperator2.Text) = "" Then
            MsgBox "请选择运算符", vbOKOnly + vbExclamation, "警告"
            comboOperator2.SetFocus
            Exit Sub
        End If
    End If
    
    '检查第三条输入
    If Trim(comboRelation2.Text) <> "" Then
        If Trim(comboName3.Text) = "" Then
            MsgBox "请选择第三条要查询的字段", vbOKOnly + vbExclamation, "警告"
            comboName3.SetFocus
            Exit Sub
        ElseIf Trim(txtInfo3.Text) = "" Then
            MsgBox "请输入第三条要查询的内容", vbOKCancel + vbExclamation, "警告"
            txtInfo3.SetFocus
            Exit Sub
        ElseIf Trim(comboOperator3.Text) = "" Then
            MsgBox "请选择运算符", vbOKOnly + vbExclamation, "警告"
            comboOperator3.SetFocus
            Exit Sub
        End If
    End If
    
    '给data赋值，用来写入SQL语句
    If Trim(comboInfo.Text) = "学生信息" Then
        Data = "Student_info"
    ElseIf Trim(comboInfo.Text) = "学生上机信息" Then
        Data = "Linelog_info"
    ElseIf Trim(comboInfo.Text) = "工作日志" Then
        Data = "Worklog_info"
    End If
    
    '给字段名和关系赋值
    Name = FieldName(comboInfo, comboName1)
    Relation = FieldRelation(comboRelation1)
    
    '感觉最麻烦的地方。。。
    txtSQL = "select * from " & Data & " where " & Name & Trim(comboOperator1.Text) & "'" & Trim(txtInfo1.Text) & "'" & Relation & ""
    
    '如果选择了关系，改变SQL语句
    If Relation <> "" Then
        Name = FieldName(comboInfo, comboName2)
        Relation = FieldRelation(comboRelation2)
        
        txtSQL = txtSQL & " " & Name & Trim(comboOperator2.Text) & "'" & Trim(txtInfo2.Text) & "'" & Relation
        
        '同上。。。
        If Relation <> "" Then
            Name = FieldName(comboInfo, comboName3)
            txtSQL = txtSQL & " " & Name & Trim(comboOperator3.Text) & "'" & Trim(txtInfo3.Text) & "'"
        End If
    End If
    
    '得到记录集
    
    Set mrc = ExecuteSQL(txtSQL, Msgtext)
    
    '写入学生信息数据
    If Data = "Student_info" Then
                
        With MyFlexGrid
            .Rows = 2
            .CellAlignment = 4
            .TextMatrix(1, 0) = "卡号"
            .TextMatrix(1, 1) = "学号"
            .TextMatrix(1, 2) = "姓名"
            .TextMatrix(1, 3) = "性别"
            .TextMatrix(1, 4) = "学院"
            .TextMatrix(1, 5) = "年级"
            .TextMatrix(1, 6) = "班级"
            
            Do While Not mrc.EOF
                .Rows = .Rows + 1
                .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))
                mrc.MoveNext
            Loop
        End With
    '写入上机记录的数据
    ElseIf Data = "Line_info" Then
        With MyFlexGrid
            .Rows = 2
            .CellAlignment = 4
            .TextMatrix(1, 0) = "卡号"
            .TextMatrix(1, 1) = "姓名"
            .TextMatrix(1, 2) = "上机日期"
            .TextMatrix(1, 3) = "上机时间"
            .TextMatrix(1, 4) = "下机日期"
            .TextMatrix(1, 5) = "下机时间"
            .TextMatrix(1, 6) = "消费金额"
            .TextMatrix(1, 7) = "余额"
            
            Do While Not mrc.EOF
                .Rows = .Rows + 1
                .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))
                .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3))
                .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(4))
                .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(5))
                .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(6))
                .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(7))
                .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(11))
                .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(12))
                mrc.MoveNext
            Loop
        End With
    '写入工作日志的数据
    ElseIf Data = "Worklog_info" Then
        With MyFlexGrid
        .Rows = 2
        .CellAlignment = 4
        .TextMatrix(1, 0) = "用户名"
        .TextMatrix(1, 1) = "登录日期"
        .TextMatrix(1, 2) = "登陆时间"
        .TextMatrix(1, 3) = "注销日期"
        .TextMatrix(1, 4) = "注销时间"
        
            Do While Not mrc.EOF
                .Rows = .Rows + 1
                .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))
                .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3))
                .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(4))
                .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(5))
                .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(6))
                mrc.MoveNext
            Loop
        End With
    End If
            
    mrc.Close
    
End Sub

写完这个玩意，真是感觉。。。活着真好。。。

其实写的时候还是挺乱的，一不小心就忘了该添加什么，该让哪改，尤其是下午来了迷迷糊糊指不定就把哪给改乱了，乱了然后就越改越乱，死循环。。。这个时候，我只能停下来，趁着米老师不在看一集银魂。。。咳咳。。。连银桑这货都还能厚颜无耻的存活在动画界，我还有什么不能！

好吧。。。我能说下面我又不知道要怎么搞了吗。。。

以上