机房管理系统（二） 之组合查询

前言

机房已经接近尾声了，目前进行的是组合查询，进行这个窗体的确费了很大的力气， 但是同过向师哥师姐和我们13 期的小伙伴学习，问题得到了逐个突破

构架问题

1.1 窗体的构建

           窗体如下所示，

         从图中我们知道，条件的个数是通过组合关系来实现调控的，而且我们也需要知道这个窗体是和数据库中line_info  相关连的，那么条件就会从这个表中的字段中选择。

1.2  代码的实现

代码如下

Private Sub Form_Load()
   With mylexGrid1
   .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) = "状态"

Combo1.AddItem "卡号"
Combo1.AddItem "学号"
Combo1.AddItem "上机时间"
Combo1.AddItem "下机时间"
Combo1.AddItem "上机日期"
Combo1.AddItem "下机日期"
Combo1.AddItem "消费金额"
Combo1.AddItem "余额"
Combo1.AddItem "状态"


Combo3.AddItem "卡号"
Combo3.AddItem "学号"
Combo3.AddItem "上机时间"
Combo3.AddItem "下机时间"
Combo3.AddItem "上机日期"
Combo3.AddItem "下机日期"
Combo3.AddItem "消费金额"
Combo3.AddItem "余额"
Combo3.AddItem "状态"


Combo5.AddItem "卡号"
Combo5.AddItem "学号"
Combo5.AddItem "上机时间"
Combo5.AddItem "下机时间"
Combo5.AddItem "上机日期"
Combo5.AddItem "下机日期"
Combo5.AddItem "消费金额"
Combo5.AddItem "余额"
Combo5.AddItem "状态"


Combo2.AddItem "="
Combo2.AddItem "<"
Combo2.AddItem ">"
Combo2.AddItem "<>"


Combo4.AddItem "="
Combo4.AddItem "<"
Combo4.AddItem ">"
Combo4.AddItem "<>"


Combo6.AddItem "="
Combo6.AddItem "<"
Combo6.AddItem ">"
Combo6.AddItem "<>"


Combo7.AddItem "与"
Combo7.AddItem "或"

Combo8.AddItem "与"
Combo8.AddItem "或"


Combo1.Text = ""
Combo2.Text = ""
Combo3.Text = ""
Combo4.Text = ""
Combo5.Text = ""
Combo6.Text = ""
Combo7.Text = ""
Combo8.Text = ""

Text1.Text = ""
Text2.Text = ""
Text3.Text = ""



Combo3.Enabled = False
Combo4.Enabled = False
Combo5.Enabled = False
Combo6.Enabled = False
Combo7.Enabled = False
Combo8.Enabled = False
End With


End Sub

这一段代码是初始化一些控件，使mylexgrid1 的第一行显示出“卡号，姓名，上机日期，上机时间，下机时间，等

”，使combo 控件添加一些选择项，使第一条件完成时候能够让第二条件输入开放（可以输入）。

解决相关问题

2.1 识别问题

         这里说的识别问题指的是，select * line_info where   "学号"        不能识别问题，我们知道“学号”这里本该是   studentno ， 所以会出现识别问题。

         解决方法： 调用一个函数，使得能够使  汉字与 表中字段进行转化。代码如下

Public Function field(a As String) As String
'定义一个函数过程
    Select Case a         '字段名与数据库中的列名相对应
            Case "学号"   '学生基本信息维护要用到的字段
        field = "studentno"
            Case "姓名"
        field = "studentName"
            Case "卡号"
        field = "cardno"
            Case "金额"
        field = "cash"
            Case "系别"
        field = "department"
            Case "年级"
        field = "grade"
            Case "班级"
        field = "class"
            Case "性别"
        field = "sex"
            Case "状态"
        field = "status"
            Case "备注"
        field = "explain"
            Case "类型"
        field = "type"
            Case "日期"
        field = "date"
            Case "时间"
        field = "time"

            Case "上机日期"     '学生上机统计信息维护
        field = "ondate"
            Case "上机时间"
        field = "ontime"
            Case "下机日期"
        field = "offdate"
            Case "下机时间"
        field = "offtime"
            Case "消费金额"
        field = "consume"
            Case "余额"
        field = "cash"


            Case "教师"      '操作员工作记录  worklog
        field = "UserID"
            Case "注册日期"
        field = "LoginDate"
            Case "注册时间"
        field = "LoginTime"
            Case "注销日期"
        field = "LogoutDate"
            Case "注销时间"
        field = "LogoutTime"
            Case "机器名"
        field = "computer"

            Case "或"
        field = "or "
            Case "与"
        field = "and "
   End Select
End Function

这样就解决了这一个问题！

2.2  组合关系的实现

   相关代码：

                        txtSQL = txtSQL & " " & field(Combo7.Text) & field(Combo3.Text) & Combo4.Text & Trim(Text2.Text)
                        Set mrc = ExecuteSQL(txtSQL, msgtext)

这里的txtSQL是指的是 select * from line_info where cardno= 2016 and studentno = 11

 通过一个and  连接了俩个选择条件，实现了功能！