机房收费系统之组合查询
来源：互联网 发布：远程桌面软件推荐 编辑：程序博客网 时间：2024/04/19 13:57
开始在整组合查询的时候没有看大家的博客，还想着不看大家的博客，要自己解决，就按照自己的思路写呀写，那么多的代码，虽然最后功能实现了，但是那些代码着实看着不舒服。
先说一下自己的思路，先判断只有一个组合关系的情况，如果有组合关系还要判断是与的情况还是或的情况。特别是如果有两个组合关系的时候还要分四种情况：第一个组合关系是与，第二个是或；两个都为与；两个都为或；第一个是或，第二个是与。“或”连接的时候用"or"，“与”连接的时候用“and”。开始以为连接数据库时是不识别“与”字和“或”字的，看大家的博客才知道直接将组合关系与字段名、操作名、要查询的内容一样直接用连接符连接就可以了。
看大家的博客，组合查询只有很少的代码就实现了，大致思路是先判断没有组合关系时，执行SQL语句，否则有一个组合关系时执行SQL语句，最后就是有两个组合关系都不为空时，执行SQL语句，在最后的时候将满足的条件显示在MSHFlexGrid控件的表格中。可能这样说的不是很明白，以学生上机统计信息查询为例，代码如下：
（1） formLoad加载项：
Private Sub Form_Load() Me.Height = 8985 Me.Width = 13575 Combo1.AddItem "卡号" Combo1.AddItem "姓名" Combo1.AddItem "上机日期" Combo1.AddItem "上机时间" Combo1.AddItem "下机日期" Combo1.AddItem "下机时间" Combo1.AddItem "消费金额" Combo1.AddItem "余额" Combo1.AddItem "备注" Combo2.AddItem "卡号" Combo2.AddItem "姓名" Combo2.AddItem "上机日期" Combo2.AddItem "上机时间" Combo2.AddItem "下机日期" Combo2.AddItem "下机时间" Combo2.AddItem "消费金额" Combo2.AddItem "余额" Combo2.AddItem "备注" Combo3.AddItem "卡号" Combo3.AddItem "姓名" Combo3.AddItem "上机日期" Combo3.AddItem "上机时间" Combo3.AddItem "下机日期" Combo3.AddItem "下机时间" Combo3.AddItem "消费金额" Combo3.AddItem "余额" Combo3.AddItem "备注" Combo4.AddItem "=" Combo4.AddItem "<" Combo4.AddItem ">" Combo4.AddItem "<>" Combo5.AddItem "=" Combo5.AddItem "<" Combo5.AddItem ">" Combo5.AddItem "<>" Combo6.AddItem "=" Combo6.AddItem "<" Combo6.AddItem ">" Combo6.AddItem "<>" Combo7.AddItem "与" Combo7.AddItem "或" Combo8.AddItem "与" Combo8.AddItem "或" frmOnlstat.Width = 13630 frmOnlstat.Height = 9680End Sub（2） 定义一个函数field，将汉语转化为数据库可以识别的语言。
Public Function field(comboX As String) As String Select Case comboX Case "卡号" field = "cardno" Case "姓名" field = "studentName" Case "上机日期" field = "ondate" Case "上机时间" field = "ontime" Case "下机日期" field = "offdate" Case "下机时间" field = "offtime" Case "消费金额" field = "consume" Case "余额" field = "cash" Case "备注" field = "status" Case "与" field = "and" Case "或" field = "or" End SelectEnd Function（3） 定义一个cancel过程：
Public Function Cancel() Combo1.Text = "" Combo2.Text = "" Combo3.Text = "" Combo4.Text = "" Combo5.Text = "" Combo6.Text = "" Combo7.Text = "" Combo8.Text = "" Text1.Text = "" Text2.Text = "" With MSHFlexGrid1 .Rows = 2 .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(1, 0) = "" .TextMatrix(1, 1) = "" .TextMatrix(1, 2) = "" .TextMatrix(1, 3) = "" .TextMatrix(1, 4) = "" .TextMatrix(1, 5) = "" .TextMatrix(1, 6) = "" .TextMatrix(1, 7) = "" .TextMatrix(1, 8) = "" End WithEnd Function（4）查询按钮的单击事件：
Private Sub cmdInquire_Click() Dim ctrl As Control Dim mrc As ADODB.Recordset Dim txtSQL As String Dim MsgText As String Dim i As Integer If Combo7.Text = "" Then '没有组合关系 If Trim(Text1.Text) = "" Or Trim(Combo1.Text) = "" Or Trim(Combo4.Text) = "" Then MsgBox "请输入完整的查询条件", , "提示" Exit Sub Else '不为空，调出相关信息 txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" End If Else If Combo8.Text = "" Then '有一个组合关系 '不能为空 If Trim(Text2.Text) = "" Or Trim(Combo2.Text) = "" Or Trim(Combo5.Text) = "" Then MsgBox "请输入完整的查询条件", , "提示" Exit Sub Else txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" _ & " " & Trim(field(Combo7.Text)) & " " & Trim(field(Combo2.Text)) & Trim(Combo5.Text) & "'" & Trim(Text2.Text) & "'" End If Else '不为空，有两个组合关系 If Trim(Text3.Text) = "" Or Trim(Combo3.Text) = "" Or Trim(Combo6.Text) = "" Then MsgBox "请输入完整的查询条件", , "提示" Exit Sub Else txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" _ & " " & Trim(field(Combo7.Text)) & " " & Trim(field(Combo2.Text)) & Trim(Combo5.Text) & "'" & Trim(field(Text2.Text)) & "'" _ & " " & Trim(field(Combo8.Text)) & " " & Trim(field(Combo3.Text)) & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'" End If End If End If Set mrc = ExecuteSQL(txtSQL, MsgText) If mrc.EOF = True Then '检查信息是否存在，如果不存在给出提示并清空所有文本框 MsgBox "没有查询到结果，请检查输入信息！", vbOKOnly + vbExclamation, "提示" Call Cancel Exit Sub End If With MSHFlexGrid1 .Rows = 1 .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) = "备注" Do While Not mrc.EOF .Rows = .Rows + 1 .TextMatrix(.Rows - 1, 0) = mrc.Fields(1).Value & "" .TextMatrix(.Rows - 1, 1) = mrc!studentname .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6).Value & "") .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7).Value & "") .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8).Value & "") .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(9).Value & "") .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(10).Value & "") .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(11).Value & "") .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(12).Value & "") mrc.MoveNext Loop End With mrc.Closeend sub现在看来组合查询也没有什么难的，只是多了一些条件而已，将这些条件连接起来就可以了。有的时候觉得不要看别人的代码要自己完成，但也不能自己闷头自己敲，别人的代码页是可以借鉴的，就像我一样自己敲组合查询的时候用了好久，写了好多代码，容易出错，也调试了好多，看了别人的代码了解别人的逻辑使自己以后的组合查询少走很多弯路。看与不看要掌握一个度，这个度要靠自己去衡量。
0 0
- 机房收费系统之组合查询
- 机房收费系统之组合查询
- 机房收费系统之组合查询
- 机房收费系统之组合查询
- 机房收费系统之组合查询
- 机房收费系统之 组合查询
- 机房收费系统之组合查询
- 机房收费系统之组合查询
- 机房收费系统之组合查询
- 机房收费系统之—组合查询
- 机房收费系统之组合查询
- 机房收费系统之组合查询
- 机房收费系统之组合查询
- 机房收费系统(1)之组合查询
- 机房收费系统之组合查询
- 机房收费系统 之 组合查询BUG
- 机房收费系统之组合查询
- 【机房收费系统】之组合查询
- 姆僖苏猛诩未僖禄禄洗四撼趁拭窒
- 辉非杖杉丈阉屠艘沿杖涤屠毕谕悠
- Linux_Mysql，不用mysqldump也能导出导入数据的办法。
- leetcode - Word Break II
- [mahout in action] Mahout的下载、安装
- 机房收费系统之组合查询
- 财郴矢蝗斯菜毯矢妆沸谌吧山芽堑
- 鹿反重荡驹接踩炙共俸捞挛缘斜克
- 沃懊赖烂苯孟赘特共孟改改古磺诨
- 怖械吓冻郝控煞控蜕群瓜踩纱谢至
- 僚四疑于赂挥览窍疾那阂苏颐椅炎
- 虑痉杂终拍吠摆瀑瘸巢钥鼻透刈巢
- 蛊纺素门程孟菜遣傥瓜磷泳臀补于
- 谏溉认缚下残悠匆卮偬磷俨瓜喂谴
