机房收费系统之组合查询
组合查询这一块也是很费脑筋的一部分,这里主要注意Exit sub 的使用就容易多了。
整体的逻辑:(代码仅为部分代码,并不完整,不能直接连接使用)
1、看第一行是否输入完整
2、如果点击了第一个组合查询框,看第二行是否输入完整
3、如果点击了第二个组合查询框,看第三行是否输入完整。
但是这里调用数据库当中的数据的时候需要将vb中的字符转化为sql可以识别的语句。
4、显示数据
5、一些小的细节优化。
首先将combo控件中的那些汉字转化一下:
Private Function sqlName(vbName As String) As String '由于sql语句不能识别vb中的汉字,需要定义一个过程进行转换,括号中的内容为定义的参数
Select Case vbName
Case "卡号"
sqlName = "cardno"
Case "姓名"
sqlName = "studentName"
Case "上机日期"
sqlName = "ondate"
Case "上机时间"
sqlName = "ontime"
Case "下机日期"
sqlName = "offdate"
Case "下机时间"
sqlName = "offtime"
Case "消费金额"
sqlName = "consume"
Case "余额"
sqlName = "cash"
Case "备注"
sqlName = "status"
End Select
End Function
Private Function Anothername(vbanothername As String) As String
Select Case vbanothername
Case "与"
Anothername = "and"
Case "或"
Anothername = "or"
End Select
End Function
将组合combo和选择combo这里分开写了,其实写一起也没有问题的。
1、看第一行是否输入完整
txtsql = "select * from line_info where"
If Combo1.Text = "" Or Combo4.Text = "" Or Text1.Text = "" Then
MsgBox "请将第一行内容输入完整!", 48, "提示"
Exit Sub
End If
txtsql = txtsql & " " & sqlName(Combo1.Text) & " " & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'"
2、如果点击了第一个组合查询框,看第二行是否输入完整
If Combo10 <> "" Then
If Combo2.Text = "" Or Combo5 = "" Or Text2.Text = "" Then
MsgBox "请将第二行内容输入完整!", 48, "提示"
Exit Sub
Else
txtsql = txtsql & " " & Anothername(Combo10.Text) & " " & sqlName(Combo2.Text) & " " & Trim(Combo5.Text) & "'" & Trim(Text2.Text) & "'"
End If
3、如果点击了第二个组合查询框,看第三行是否输入完整
If Combo11 <> "" Then
If Combo3.Text = "" Or Combo6 = "" Or Text3.Text = "" Then
MsgBox "请将第三行内容输入完整!", 48, "提示"
Exit Sub
Else
txtsql = txtsql & " " & Anothername(Combo11.Text) & " " & sqlName(Combo3.Text) & " " & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
End If
End If
4、显示数据
set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF = True Then
MsgBox "无此项信息,请重新输入", 48, "提示"
Exit Sub
End If
With MSFlexGrid1
Do While Not mrc.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1)) & ""
.TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3)) & ""
.TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6)) & ""
.TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7)) & ""
.TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8)) & ""
.TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(9)) & ""
.TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(11)) & ""
.TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(12)) & ""
.TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(13)) & ""
mrc.MoveNext
Loop
End With
5、一些小的细节优化。
1、如果combo的文本是 姓名 则大于,小于不可用
Private Sub Text1_Change()
If Combo1.Text = "姓名" Then
If Combo4.Text = "<" Or Combo4.Text = ">" Then
Text1.Text = disabled
End If
End If
End Sub
2、如果是combo的文本是日期或者时间则显示提示框
Private Sub Combo1_click()
If Combo1.Text = "上机日期" Or Combo1.Text = "上机时间" Or Combo1.Text = "下机时间" Or Combo1.Text = "下机日期" Then
MsgBox "日期请输入 yyyy-mm-d格式,时间请输入 hh:mm:ss格式", , "提示"
Exit Sub
End If
End Sub
3、如果时间是日期或者时间则限制只能输入特殊的符号(日期只能输入/,时间只能输入:)这里用日历会更好。
Private Sub text1_KeyPress(KeyAscii As Integer)
If Combo1.Text = "上机日期" Or Combo1.Text = "下机日期" Then
If (KeyAscii >= 48 And KeyAscii <= 57) Or KeyAscii = 8 Or KeyAscii = 47 Then
Else
KeyAscii = 0
End If
End If
If Combo1.Text = "下机时间" Or Combo1.Text = "上机时间" Then
If (KeyAscii >= 48 And KeyAscii <= 57) Or KeyAscii = 8 Or KeyAscii = 58 Then
Else
KeyAscii = 0
End If
End If
End Sub
附上一个ascii表。