机房收费系统中还涉及到了对信息的多条件查询,多条件查询需要弄清楚各个条件之间复杂的关系。
1.在这个功能块中重要的就是将文本框(也可以是其他的文字显示的控件)中的信息转换为数据库能够识别的字符,在这里可以定义一个字符转换的函数,通过条件选择来对应数据库中不同的字符,而我在自己的系统中就是通过strexchange函数来实现这一功能的;
2.同时需要注意的是对不同条件完整性的判断,只有条件齐全了才能够实现基本的查询功能;
3.最后想说的就是各个条件关系和数据库的对应,在这里以系统中上机状态查看为例;
Private Sub Command1_Click()
Dim txtsql As String
Dim msgtext As String
Dim dd(4) As Boolean
Dim mrc As ADODB.Recordset
Dim strexchange(2) As String
Dim relationexchange(1) As String
'-----------------------------------字段名的对应-----------------------------------------------
For Index = 0 To 2
Select Case Combo1(Index).Text
Case "卡号"
strexchange(Index) = "cardno"
Case "姓名"
strexchange(Index) = "studentname"
Case "上机日期"
strexchange(Index) = "ondate"
Case "上机时间"
strexchange(Index) = "ontime"
Case "机房号"
strexchange(Index) = "computer"
End Select
Next Index
'----------------------------------操作符的对应-------------------------------------------------
For Index = 0 To 1
Select Case Combo3(Index).Text
Case "与"
relationexchange(Index) = "and"
Case "或"
relationexchange(Index) = "or"
End Select
Next Index
txtsql = "select*from online_info where "
'Set mrc = ExecuteSQL(txtsql, msgtext)
以下分别对一组条件中的所有信息进行核查,确保一组信息的完整性!
If testtxt(Combo1(0).Text) = True Then '字段名是否为空
If testtxt(Combo2(0).Text) = True Then '操作符是否为空
If testtxt(Text1(0).Text) = True Then '内容是否为空
dd(0) = True '第一条条件被标记
Else
MsgBox "第一行查询内容为空,请输入查询内容。", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
Else
MsgBox "第一行操作符为空,请选择操作符。", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
End If
If testtxt(Combo1(1).Text) = True Then '字段名是否为空
If testtxt(Combo2(1).Text) = True Then '操作符是否为空
If testtxt(Text1(1).Text) = True Then '内容是否为空
dd(1) = True '第二个条件被标记
Else
MsgBox "第二行查询内容为空,请输入查询内容。", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
Else
MsgBox "第二行操作符为空,请选择操作符。", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
End If
If testtxt(Combo1(2).Text) = True Then '字段名是否为空
If testtxt(Combo2(2).Text) = True Then '操作符是否为空
If testtxt(Text1(2).Text) = True Then '内容是否为空
dd(2) = True '第三个条件被标记
Else
MsgBox "第三行查询内容为空,请输入查询内容。", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
Else
MsgBox "第三行操作符为空,请选择操作符。", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
End If
If dd(0) = False And dd(1) = False And dd(2) = False Then
'-------------------判断条件选择是否为空-------------------------------------------------
MsgBox "您还没有选择查询条件,请输入查询条件", vbOKOnly + vbExclamation, "提示"
Exit Sub
Combo1(0).SetFocus '如果条件选择为空,则第一个条件得到焦点
End If
'-----------------首先判断第一个条件是否被选中,如果被选中,则补充完整查询限制条件-----------------
If dd(0) = True Then
txtsql = txtsql & " " & strexchange(0) & " " & Combo2(0).Text & " '" & Text1(0).Text & "' "
End If
If dd(1) = True Then '判断第二个条件是否被选中
If dd(0) = True Then ' 再判断第一个条件是否被选中
txtsql = txtsql & " " & relationexchange(0) & " " & strexchange(1) & " " & Combo2(1).Text & " '" & Text1(1).Text & "' "
Else '(条件2,1)
txtsql = txtsql & " " & strexchange(1) & " " & Combo2(1).Text & " '" & Text1(1).Text & "' "
End If '(条件2)
End If
If dd(2) = True Then '判断第三个条件是否被选中
If dd(0) = True Or dd(1) = True Then '判断前两个条件是否被选中
txtsql = txtsql & " " & relationexchange(1) & " " & strexchange(2) & " " & Combo2(2).Text & " '" & Text1(2).Text & "' "
Else '(条件3,1) 或 (条件3,2) 或 (条件3,2,1)
txtsql = txtsql & " " & strexchange(2) & " " & Combo2(2).Text & " '" & Text1(2).Text & "' "
End If '条件(3)
End If
If Not (dd(0) Or dd(1) Or dd(2)) Then '如果没有选择查询条件
MsgBox "您还没有选择查询方式,请选择查询方式。", vbOKOnly + vbExclamation, "提示"
Combo1(0).SetFocus
End If
'txtsql = txtsql & "order by cardno"
Set mrc = ExecuteSQL(txtsql, msgtext)
With MSHFlexGrid1
.Cols = 5
.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) = mrc.Fields(0)
.TextMatrix(.Rows - 1, 1) = mrc.Fields(3)
.TextMatrix(.Rows - 1, 2) = mrc.Fields(6)
.TextMatrix(.Rows - 1, 3) = mrc.Fields(7)
.TextMatrix(.Rows - 1, 4) = mrc.Fields(8)
mrc.MoveNext
Loop
mrc.Close
End With
End Sub
Private Sub Form_Load()
Dim i As Long
Dim j As Long
Dim n As Long
For i = 0 To 2
Combo1(i).AddItem "卡号"
Combo1(i).AddItem "姓名"
Combo1(i).AddItem "上机日期"
Combo1(i).AddItem "上机时间"
Combo1(i).AddItem "机房号"
Next i
For j = 0 To 2
Combo2(j).AddItem "="
Combo2(j).AddItem "<"
Combo2(j).AddItem ">"
Combo2(j).AddItem "<>"
Next j
For n = 0 To 1
Combo3(n).AddItem "与"
Combo3(n).AddItem "或"
Next n
End Sub
以上就是自己代码部分,希望对大家有所帮助!