最近一直在弄机房收费系统,前天晚上开始着手机房收费系统组合查询这一块,刚看到的时候,觉得不知从何下手,上网查了一些资料,还是不太懂,于是自己就慢慢开始敲,慢慢摸索着,代码从一开始的杂乱无章,慢慢变成了分层次的较短的代码,虽然仍有缺陷,不过我会随着时间慢慢进步的。
组合查询可以说是多条件查询,我们可以将条件分层次进行,通过“组合关系”来控制层次,在进行条件分层时,首先要有一些先前的准备,准备工作如下:
先前的准备:
Private Sub Form_Load()
Dim a, b, c As Integer
Me.Width = 19000
Me.Height = 15000
For a = 0 To 2
Combo1(a).AddItem "卡号"
Combo1(a).AddItem "学号"
Combo1(a).AddItem "姓名"
Combo1(a).AddItem "性别"
Combo1(a).AddItem "系别"
Combo1(a).AddItem "年级"
Combo1(a).AddItem "班级"
Next a
For b = 0 To 2
Combo2(b).AddItem "="
Combo2(b).AddItem "<"
Combo2(b).AddItem ">"
Combo2(b).AddItem "<>"
Next b
For c = 0 To 1
Combo3(c).AddItem "与"
Combo3(c).AddItem "或"
Next c
Combo1(1).Enabled = False
Combo1(2).Enabled = False
Combo2(1).Enabled = False
Combo2(2).Enabled = False
Text1(1).Enabled = False
Text1(2).Enabled = False
Combo3(1).Enabled = False
End Sub
这段代码增加了条件的选项。combo1是字符段的选择,combo2是操作符的选择,combo3是组合关系的选择。
Private Sub Combo3_Click(Index As Integer)
If Testtxt(Trim(Combo3(0).Text)) Then
Combo1(1).Enabled = True
Combo2(1).Enabled = True
Text1(1).Enabled = True
Combo1(2).Enabled = False
Combo2(2).Enabled = False
Combo3(1).Enabled = True
Text1(2).Enabled = False
End If
If Testtxt(Trim(Combo3(1).Text)) Then
Combo1(2).Enabled = True
Combo2(2).Enabled = True
Text1(2).Enabled = True
End If
这段代码是通过组合关系combo3的值来控制其他的控件是否可用。
为了减少代码的重复,我们可以使用一个过程,定义一个过程:
Sub viewData()
With MSHFlexGrid1
.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) = "状态"
.TextMatrix(0, 9) = "备注"
.TextMatrix(0, 10) = "类型"
.TextMatrix(0, 11) = "日期"
.TextMatrix(0, 12) = "时间"
End With
End Sub
通过前期的准备,下面我们就可以通过编写查询按钮的代码来实现组合查询了:
第一行的查询:
Dim mrc As ADODB.Recordset
Dim txtSQL As String
Dim MsgText As String
Dim strStdinfo(3) As String ‘定义字段名
Dim strRelationship(2) As String ’定义组合关系
Dim D ’定义标签
Dim a, b As Integer
For a = 0 To 2
Select Case Combo1(a).ListIndex '设置字段名的选项
Case 0
strStdinfo(a) = "cardno"
Case 1
strStdinfo(a) = "studentNo"
Case 2
strStdinfo(a) = "studentName"
Case 3
strStdinfo(a) = "sex"
Case 4
strStdinfo(a) = "department"
Case 5
strStdinfo(a) = "grade"
Case 6
strStdinfo(a) = "class"
End Select
Next a
For b = 0 To 1
Select Case Combo3(b).ListIndex ‘设置组合关系
Case 0
strRelationship(b) = "and"
Case 1
strRelationship(b) = "or"
End Select
Next b
txtSQL = "select * from student_Info where " ’连接所需数据库
Set mrc = ExecuteSQL(txtSQL, MsgText)
' 第一行
If Not Testtxt(Combo3(0).Text) Then ‘通过组合关系combo3是否为空,来进行条件分层进行,组合关系都为空
If Not Testtxt(Combo1(0).Text) Then
MsgBox "请选择第一行的字段名!", vbOKOnly + vbExclamation, "警告"
Combo1(0).SetFocus
Exit Sub
End If
If Not Testtxt(Combo2(0).Text) Then
MsgBox "请选择第一行的操作符!", vbOKOnly + vbExclamation, "警告"
Combo2(0).SetFocus
Exit Sub
End If
If Not Testtxt(Text1(0).Text) Then
MsgBox "请输入第一行要查询的内容!", vbOKOnly + vbExclamation, "警告"
Text1(0).SetFocus
Exit Sub
End If
txtSQL = txtSQL & Trim(strStdinfo(0)) & Trim(Combo2(0).Text) & "'" & Trim(Text1(0).Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "没有此记录!", vbOKOnly + vbExclamation, "警告"
MSHFlexGrid1.Clear
Call viewData
Exit Sub
Else
Call viewData '调用过程viewdata()
GoTo D ' D是一个标签,在下面的代码中有写到
mrc.Close
Exit Sub
End If
End If
第二行条件查询:
'第二行
If Testtxt(Combo3(0).Text) And Not Testtxt(Combo3(1).Text) Then '组合关系为一个不为空,一个为空
If Not Testtxt(Combo1(1).Text) Then
MsgBox "请选择第二行的字段名!", vbOKOnly + vbExclamation, "警告"
Combo1(1).SetFocus
Exit Sub
End If
If Not Testtxt(Combo2(1).Text) Then
MsgBox "请选择第二行操作符!", vbOKOnly + vbExclamation, "警告"
Combo2(1).SetFocus
Exit Sub
End If
If Not Testtxt(Text1(1).Text) Then
MsgBox "请输入第二行查询内容!", vbOKOnly + vbExclamation, "警告"
Text1(1).SetFocus
Exit Sub
End If
txtSQL = txtSQL & Trim(strStdinfo(0)) & Trim(Combo2(0).Text) & "'" & Trim(Text1(0).Text) & "'" _
& Trim(strRelationship(0)) & " " & Trim(strStdinfo(1)) & Trim(Combo2(1).Text) & "'" & Trim(Text1(1).Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "没有此记录!", vbOKOnly + vbExclamation, "警告"
MSHFlexGrid1.Clear
Call viewData
Exit Sub
Else
Call viewData
GoTo D
mrc.Close
Exit Sub
End If
End If
第三行条件查询:
‘第三行
If Testtxt(Combo3(0).Text) And Testtxt(Combo3(1).Text) Then
If Not Testtxt(Combo1(2).Text) Then
MsgBox "请选择第三行的字段名!", vbOKOnly + vbExclamation, "警告"
Combo1(2).SetFocus
Exit Sub
End If
If Not Testtxt(Combo2(2).Text) Then
MsgBox "请选择第三行的操作符!", vbOKOnly + vbExclamation, "警告"
Combo2(2).SetFocus
Exit Sub
End If
If Not Testtxt(Text1(2).Text) Then
MsgBox "请输入第三行要查询的内容!", vbOKOnly + vbExclamation, "警告"
Text1(2).SetFocus
Exit Sub
End If
txtSQL = txtSQL & Trim(strStdinfo(0)) & Trim(Combo2(0).Text) & "'" & Trim(Text1(0).Text) & "'" _
& Trim(strRelationship(0)) & " " & Trim(strStdinfo(1)) & Trim(Combo2(1).Text) & "'" & Trim(Text1(1).Text) & "'" _
& Trim(strRelationship(1)) & " " & Trim(strStdinfo(2)) & Trim(Combo2(2).Text) & "'" & Trim(Text1(2).Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "此记录不存在!", vbOKOnly + vbExclamation, "警告"
MSHFlexGrid1.Clear
Call viewData
Exit Sub
Else
Call viewData
GoTo D
mrc.Close
Exit Sub
End If
End If
’标签
D: Do While Not mrc.EOF
With MSHFlexGrid1
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))
.TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(2))
.TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(0))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
.TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))
.TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5))
.TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6))
.TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(3))
.TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(10))
.TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(8))
.TextMatrix(.Rows - 1, 10) = Trim(mrc.Fields(14))
.TextMatrix(.Rows - 1, 11) = Trim(mrc.Fields(12))
.TextMatrix(.Rows - 1, 12) = Trim(mrc.Fields(13))
mrc.MoveNext
End With
Loop
组合查询重要的是要弄清楚逻辑关系,分清楚条件之间存在的逻辑关系,然后进行慢慢编写,慢慢改善,到最后条件之间的关系就明确了,不管做什么事,我们都要勇于尝试,不断的尝试,最终就会成功,不要遇到问题就畏惧,不要看到困难就止步不前,勇于向前吧!