组合查询我们用到了Student_Info,首先你要告诉自己,它一点都不难,稍微用心下,就很容易明
白其中的道理了。
一、代码思路0-3
0.首先,老规矩,先判断是否为空。
1.当没有组合关系时,直接从学生表里查询信息,并显示数据;
值得注意的是,刚开始,当没有选择组合关系时,也可以进行多行条件的组合查询。所以后来,我进行了
优化,就是若未选择组合关系“或”或“与”,后面的combo和text则不能使用;只有选择了第一个组合
关系“或”或“与”时,后面的combo和text才可以使用。
此处的代码很简单,如下:
Private Sub Combo7_Click()
If Combo7.Text = "" Then '当选择了第一个组合关系,限制后面的combo和text不能使用
Combo2.Enabled = False
Combo3.Enabled = False
Combo5.Enabled = False
Combo6.Enabled = False
Combo8.Enabled = False
Text2.Enabled = False
Text3.Enabled = False
Else '否则,第二行条件和第二个组合关系可以输入
Combo2.Enabled = True
Combo5.Enabled = True
Combo8.Enabled = True
Text2.Enabled = True
End If
End Sub
Private Sub Combo8_Click()
If Combo8.Text <> "" Then '当选择了第二个组合关系,第三行的combo和text可以使用
Combo3.Enabled = True
Combo6.Enabled = True
Text3.Enabled = True
End If
End Sub
(注: 我没有使用数组,其中,Combo7为第一个组合关系框,Combo8为第二个组合关系框)
效果如下图:
0).选择组合关系前
1).选择组合关系后
2.当有一个组合关系时,必须填写完整前两行信息,进行组合查询;
3.当有二个组合关系时,必须填写完整三行信息,进行组合查询。
二、组合查询代码:
Private Function fieldname(i As String) As String
Select Case i
'定义一个函数过程,因为数据库在进行工作时使用英文,在这里相当于是进行了翻译
Case "卡号"
fieldname = "cardno"
Case "学号"
fieldname = "studentno"
Case "姓名"
fieldname = "studentname"
Case "性别"
fieldname = "sex"
Case "系别"
fieldname = "department"
Case "年级"
fieldname = "grade"
Case "班级"
fieldname = "class"
Case "与"
fieldname = "and"
Case "或"
fieldname = "or"
End Select
End Function
Public Sub cmdInquire_Click()
Dim mrc As ADODB.Recordset
Dim mrcc As ADODB.Recordset
Dim txtSQL As String
Dim MsgText As String
MSHFlexGrid1.SelectionMode = flexSelectionByRow '单击时选择整行
MSHFlexGrid1.FocusRect = flexFocusNone '在当前但愿的周围画一个焦点框
MSHFlexGrid1.HighLight = flexHighlightWithFocus '该值决定了所选定的单元是否突出显示
txtSQL = "select * from student_Info where "
'组合SQL语句
'判断是否为空,不为空则查询
If Trim(Combo1.Text) = "" Or Trim(Combo4.Text) = "" Or Trim(Text1.Text) = "" Then
MsgBox "请在第一行添加完整查询条件!", vbOKOnly, "苏辙提示您"
Exit Sub
Else
txtSQL = txtSQL & fieldname(Trim(Combo1.Text)) & Trim(Combo4.Text) & "'" _
& Trim(Text1.Text) & "'"
If Combo7 <> "" Then
'若第一个组合关系存在
If Trim(Combo2.Text) = "" Or Trim(Combo5.Text) = "" Or Trim(Text2.Text) = "" Then
MsgBox "您选择了第一个组合关系,请在第二行添加完整查询条件!", vbOKOnly, "苏辙提示您"
Exit Sub
Else
txtSQL = txtSQL & fieldname(Combo7.Text) & " " & fieldname(Trim(Combo2.Text)) & _
"" & (Trim(Combo5.Text)) & "'" & Trim(Text2.Text) & "'"
End If
ElseIf Trim(Combo8.Text <> "") Then
'第二个组合关系存在
If Trim(Combo3.Text) = "" Or Trim(Combo6.Text) = "" Or Trim(Text3.Text) = "" Then
MsgBox "您选择了第二个组合关系,请在第三行添加完整查询条件!", vbOKOnly, "苏辙提示您"
Exit Sub
Else
txtSQL = txtSQL & "" & fieldname(Combo3.Text) & "" & fieldname(Combo2.Text) & Trim(Combo5.Text) & _
"'" & Trim(Text3.Text) & "'"
End If
End If
Set mrcc = ExecuteSQL(txtSQL, MsgText)
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) = "时间"
'0代表表头不空行,从第一行开始显示
If Not (mrcc.EOF Or mrcc.BOF) Then
'判断是否移动到数据集对象的最后一条记录
Do While Not mrcc.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrcc.Fields(0))
.TextMatrix(.Rows - 1, 1) = Trim(mrcc.Fields(1))
.TextMatrix(.Rows - 1, 2) = Trim(mrcc.Fields(2))
.TextMatrix(.Rows - 1, 3) = Trim(mrcc.Fields(3))
.TextMatrix(.Rows - 1, 4) = Trim(mrcc.Fields(4))
.TextMatrix(.Rows - 1, 5) = Trim(mrcc.Fields(5))
.TextMatrix(.Rows - 1, 6) = Trim(mrcc.Fields(6))
.TextMatrix(.Rows - 1, 7) = Trim(mrcc.Fields(7)) & ""
.TextMatrix(.Rows - 1, 8) = Trim(mrcc.Fields(8)) & ""
.TextMatrix(.Rows - 1, 9) = Trim(mrcc.Fields(10)) & ""
.TextMatrix(.Rows - 1, 10) = Trim(mrcc.Fields(12)) & ""
.TextMatrix(.Rows - 1, 11) = Trim(mrcc.Fields(13)) & ""
mrcc.MoveNext
Loop
Else
MsgBox "查询无果!", vbOKOnly, "很遗憾"
End If
End With
mrcc.Close
End If
End Sub