机房收费系统中组合查询是机房收费相对来说比较复杂的一块,一开始做到组合查询的时候就有点晕,不知道代码该怎么写,记得是花了好几天的时间去整理思路,在这期间,各种想法都有,想着想着自己就把自己给绕进去了,总是把事情想复杂。机房收费中的组合查询主要有三个窗体:学生基本信息维护 、学生上机统计信息 和操作员工作记录,其实只要整理好思路,想明白了就好了,把其中的一个窗体做出来,其他的都会了。下面就以操作员工作记录为例来说说我的整个思路。 首先:你需要判断各个需要填写的信息是否为空,如果是就提示填写完整。 其次:如果选择了组合关系一定要填写第二行或者第三行信息。 之后就是查询了,这里需要注意的一点是,字段名和组合关系需要转换成数据库能够读懂的信息,需要用一个函数(例如: filedname)来转换。在这其中,用到了一个dtpicker控件,方便输入,提高效率。
但是写好了不能运行,还老出错,经过一些探索,终于知道了原来是出一个很小的错误,就是少了个空格。可见小小的一个空格的力量不容小觑啊 下面的是我的代码展示(仅供参考):
Private Function FieldName(strFieldName As String) As String
'是汉字转化成数据库中的字段和关系
Select Case strFieldName
Case "教师"
FieldName = "UserID"
Case "注册日期"
FieldName = "logindate"
Case "注册时间"
FieldName = "logintime"
Case "注销日期"
FieldName = "logoutdate"
Case "注销时间"
FieldName = "logouttime"
Case "机器名"
FieldName = "computer"
Case "与"
FieldName = "and"
Case "或"
FieldName = "or"
End Select
End Function
Private Sub Combo1_Click(Index As Integer)
Combo2(0).Clear
Combo2(1).Clear '清空,否则每选一次都会出现相同的信息
Combo2(2).Clear
Select Case Combo1(0).Text
Case "教师"
Text1(0).Visible = True
Combo2(0).AddItem "="
Combo2(0).AddItem "<>"
Text1(0).Visible = True
' dtpicker1.Enabled = False
dtpicker1.Visible = False
Case "注册日期" '我在这里用了一个dtpicker控件,然后选择日期的时候自动出现,便于提高效率,选择其他字段的时候显示的是文本框
Text1(0).Visible = True
Combo2(0).AddItem ">"
Combo2(0).AddItem "<"
Combo2(0).AddItem "="
Combo2(0).AddItem "<>"
dtpicker1.Visible = True
' dtpicker1.Enabled = True
' Text1(0).Enabled = False
Text1(0).Visible = False
Text1(0).Text = dtpicker1.Value
Case "注册时间"
Text1(0).Visible = True
Combo2(0).AddItem ">"
Combo2(0).AddItem "<"
Combo2(0).AddItem "="
Combo2(0).AddItem "<>"
Text1(0).Visible = True
' dtpicker1.Enabled = False
dtpicker1.Visible = False
Case "注销日期"
Text1(0).Visible = True
Combo2(0).AddItem ">"
Combo2(0).AddItem "<"
Combo2(0).AddItem "="
Combo2(0).AddItem "<>"
dtpicker1.Enabled = True
Text1(0).Visible = False
Text1(0).Text = dtpicker1.Value
Case "注销时间"
Text1(0).Visible = True
Combo2(0).AddItem ">"
Combo2(0).AddItem "<"
Combo2(0).AddItem "="
Combo2(0).AddItem "<>"
Text1(0).Visible = True
' dtpicker1.Enabled = False
dtpicker1.Visible = False
Case "机器名"
Text1(0).Visible = True
' dtpicker1.Enabled = False
dtpicker1.Visible = False
Combo2(0).AddItem "="
Combo2(0).AddItem "<>"
End Select
Select Case Combo1(1).Text
Case "教师"
Text1(1).Visible = True
Combo2(1).AddItem "="
Combo2(1).AddItem "<>"
Text1(1).Visible = True
' DTPicker2.Enabled = False
DTPicker2.Visible = False
' blnShuZi = False
Case "注册日期"
Text1(1).Visible = True
Combo2(1).AddItem ">"
Combo2(1).AddItem "<"
Combo2(1).AddItem "="
Combo2(1).AddItem "<>"
DTPicker2.Visible = True
Text1(1).Text = DTPicker2.Value
Text1(1).Visible = False
Case "注册时间"
Text1(1).Visible = True
Combo2(1).AddItem ">"
Combo2(1).AddItem "<"
Combo2(1).AddItem "="
Combo2(1).AddItem "<>"
Text1(1).Visible = True
' DTPicker2.Enabled = False
DTPicker2.Visible = False
Case "注销日期"
Text1(1).Visible = True
Combo2(1).AddItem ">"
Combo2(1).AddItem "<"
Combo2(1).AddItem "="
Combo2(1).AddItem "<>"
DTPicker2.Visible = True
Text1(1).Visible = False
Text1(1).Text = DTPicker2.Value
Case "注销时间"
Text1(1).Visible = True
Combo2(1).AddItem ">"
Combo2(1).AddItem "<"
Combo2(1).AddItem "="
Combo2(1).AddItem "<>"
Text1(1).Visible = True
' DTPicker2.Enabled = False
DTPicker2.Visible = False
Case "机器名"
Text1(1).Visible = True
Combo2(1).AddItem "="
Combo2(1).AddItem "<>"
Text1(1).Visible = True
' DTPicker2.Enabled = False
DTPicker2.Visible = False
End Select
Select Case Combo1(2).Text
Case "教师"
Text1(2).Visible = True
Combo2(2).AddItem "="
Combo2(2).AddItem "<>"
Text1(2).Visible = True
' DTPicker3.Visible = False
DTPicker3.Visible = False
' blnShuZi = False
Case "注册日期"
' Text1(2).Visible = True
Combo2(2).AddItem ">"
Combo2(2).AddItem "<"
Combo2(2).AddItem "="
Combo2(2).AddItem "<>"
DTPicker3.Visible = True
Text1(2).Visible = False
Text1(2).Text = DTPicker3.Value
Case "注册时间"
' Text1(2).Visible = True
Combo2(2).AddItem ">"
Combo2(2).AddItem "<"
Combo2(2).AddItem "="
Combo2(2).AddItem "<>"
Text1(2).Visible = True
DTPicker3.Visible = False
Case "注销日期"
' Text1(2).Visible = True
Combo2(2).AddItem ">"
Combo2(2).AddItem "<"
Combo2(2).AddItem "="
Combo2(2).AddItem "<>"
DTPicker3.Visible = True
Text1(2).Visible = False
Text1(2).Text = DTPicker3.Value
Case "注销时间"
Combo2(2).AddItem ">"
Combo2(2).AddItem "<"
Combo2(2).AddItem "="
Combo2(2).AddItem "<>"
Text1(2).Visible = True
DTPicker3.Visible = False
Case "机器名"
' Text1(2).Visible = True
Combo2(2).AddItem "="
Combo2(2).AddItem "<>"
Text1(2).Visible = True
DTPicker3.Visible = False
End Select
End Sub
Private Sub command1_Click() '清空所有信息
Combo1(0).Text = ""
Combo1(1).Text = ""
Combo1(2).Text = ""
Combo2(0).Text = ""
Combo2(1).Text = ""
Combo2(2).Text = ""
Combo3(0).Text = ""
Combo3(1).Text = ""
Text1(0).Text = ""
Text1(1).Text = ""
Text1(2).Text = ""
MSFlexGrid1.Clear
End Sub
Private Sub command2_Click() '这个是查询
Dim txtsql As String
Dim mrc As ADODB.Recordset
Dim msgtext As String
Dim a
txtsql = "select * from worklog_info where "
Set mrc = ExecuteSQL(txtsql, msgtext)
'编辑第一行
If Combo1(0).Text = "" Then
MsgBox "请输入第一行字段名", vbOKOnly, "提示"
Combo1(0).SetFocus
Exit Sub
ElseIf Combo2(0).Text = "" Then
MsgBox "请输入第一行操作符", vbOKOnly, "提示"
Combo2(0).SetFocus
Exit Sub
ElseIf Text1(0).Text = "" And dtpicker1.Value = "" Then
MsgBox "请输入第一行要查找的内容", vbOKOnly, "提示"
Exit Sub
Else
'从数据库中选择符合条件的内容
txtsql = txtsql & FieldName(Combo1(0).Text) & " " & Combo2(0).Text & "'" & Trim(Text1(0).Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF Then '判断是否为空
MsgBox "没有记录,请重新选择!", vbOKOnly, "提示"
Combo1(0).SetFocus
Exit Sub
Else
If Combo3(0).Text = "" Then
GoTo case1
End If
End If
'End If
End If
If Combo1(1).Text = "" Then
MsgBox "请输入第二行字段名", vbOKOnly, "提示"
Combo1(1).SetFocus
Exit Sub
End If
If Combo2(1).Text = "" Then
MsgBox "请选择第二行操作符~", vbOKOnly, "提示"
Combo2(1).SetFocus
Exit Sub
End If
If Text1(1).Text = "" Then
MsgBox "请输入第二行要查找的内容^^", vbOKOnly, "提示"
Text1(1).SetFocus
Exit Sub
End If
If Combo1(1).Text <> "" And Combo2(1).Text <> "" And Text1(1).Text <> "" And Combo2(0).Text = "" Then
MsgBox "请选择组合关系", vbOKOnly, "提示"
Else
' txtSQL = "select * from worklog_info where status='False'" & "And" & " " & FieldName(Combo1(0).Text) & Combo2(0).Text & Trim(Text1(0).Text) & "(" & a & " " & Trim(Combo1(1).Text) & Combo2(1).Text & "'" & Text1(1).Text & "'" & ")"
txtsql = txtsql & FieldName(Combo3(0).Text) & " " & FieldName(Combo1(1).Text) & Combo2(1).Text & "'" & Text1(1).Text & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF Then
MsgBox "没有记录哦~", vbOKOnly, "提示"
Combo1(1).SetFocus
Exit Sub
Else
If Combo3(1).Text = "" Then
GoTo case1
End If
End If
End If
'第三行
If Combo1(2).Text = "" Then
MsgBox "请选择第三行字段名~", vbOKOnly, "提示"
Combo1(2).SetFocus
Exit Sub
End If
If Combo2(2).Text = "" Then
MsgBox "请选择第三行"
Combo2(2).SetFocus
Exit Sub
End If
If Text1(2).Text = "" Then
MsgBox "请输入第三行要查询的内容", vbOKOnly, "提示"
Text1(2).SetFocus
Exit Sub
End If
' txtsql = "select * from worklog_info where status='" & "False" & "'" _
' & "and" & " " & " " & FieldName(Combo1(0).Text) & " " & Combo2(0).Text & "'" & Text1(0).Text & "'" _
' & a & " " & FieldName(Combo1(1).Text) & " " & Combo2(1).Text & "'" & Text1(1).Text & "'" _
' & a & " " & FieldName(Combo1(2).Text) & " " & Combo2(2).Text & "'" & Text1(2).Text & "'" & ")"
txtsql = txtsql & FieldName(Combo3(1).Text) & " " & FieldName(Combo1(2).Text) & Combo2(2).Text & "'" & Text1(2).Text & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF Then
MsgBox "当前没有该内容", vbOKOnly, "提示"
Exit Sub
Else
GoTo case1
End If
case1: '定义case1,之后用到了就调用case1就好,避免代码重复,
With MSFlexGrid1
.Rows = 1
.CellAlignment = 4
' .ColAlignment = 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) = "状态"
Do While Not mrc.EOF
With MSFlexGrid1
.Rows = .Rows + 1
.CellAlignment = 4
' .ColAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))
.TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(1))
.TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(2))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(3))
.TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))
.TextMatrix(.Rows - 1, 5) = "" & mrc.Fields(5).Value
.TextMatrix(.Rows - 1, 6) = "" & Trim(mrc.Fields(6))
.TextMatrix(.Rows - 1, 7) = "" & Trim(mrc.Fields(7))
' .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(8))
' If Trim(mrc.Fields(i, j)) = Null Then
' TextMatrix(.Rows - 1, j) = ""
' End If
End With
mrc.MoveNext
Loop
End With
End Sub
Private Sub Command3_Click() '这个是导出excel
Dim i As Integer
Dim j As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.add
Set xlSheet = xlBook.Worksheets(1)
For i = 0 To MSFlexGrid1.Rows - 1
For j = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.Row = i
MSFlexGrid1.Col = j
xlSheet.Cells(i + 1, j + 1) = Trim(MSFlexGrid1.Text)
Next
Next
End Sub
Private Sub Command4_Click() '退出
Unload Me
End Sub
Private Sub Form_Load() '加载字段名,操作符和组合关系
Combo1(0).AddItem "教师"
Combo1(0).AddItem "注册日期"
Combo1(0).AddItem "注册时间"
Combo1(0).AddItem "注销日期"
Combo1(0).AddItem "注销时间"
Combo1(0).AddItem "机器名"
Combo2(0).AddItem "="
Combo2(0).AddItem "<"
Combo2(0).AddItem ">"
Combo2(0).AddItem "<>"
Combo3(0).AddItem "或"
Combo3(0).AddItem "与"
Combo1(1).AddItem "教师"
Combo1(1).AddItem "注册日期"
Combo1(1).AddItem "注册时间"
Combo1(1).AddItem "注销日期"
Combo1(1).AddItem "注销时间"
Combo1(1).AddItem "机器名"
Combo2(1).AddItem "="
Combo2(1).AddItem "<"
Combo2(1).AddItem ">"
Combo2(1).AddItem "<>"
' Combo3(1).AddItem ""
Combo3(1).AddItem "或"
Combo3(1).AddItem "与"
Combo1(2).AddItem "教师"
Combo1(2).AddItem "注册日期"
Combo1(2).AddItem "注册时间"
Combo1(2).AddItem "注销日期"
Combo1(2).AddItem "注销时间"
Combo1(2).AddItem "机器名"
Combo2(2).AddItem "="
Combo2(2).AddItem "<"
Combo2(2).AddItem ">"
Combo2(2).AddItem "<>"
End Sub