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

前言

机房已经接近尾声了，目前进行的是组合查询，进行这个窗体的确费了很大的力气， 但是同过向师哥师姐和我们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 = FalseCombo4.Enabled = FalseCombo5.Enabled = FalseCombo6.Enabled = FalseCombo7.Enabled = FalseCombo8.Enabled = FalseEnd WithEnd 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 SelectEnd 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  连接了俩个选择条件，实现了功能！