对于这个窗体来说,主要的功能就是实现“组合查询”,什么是组合查询?
组合查询:
多数SQL查询都只包含从一个或多个表中返回数据的单条 SELECT 语句。MySQL也允许执行多个查询(多条 SELECT 语句),并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。
有两种基本情况,其中需要使用组合查询:
1.在单个查询中从不同的表返回类似结构的数据;
2.对单个表执行多个查询,按单个查询返回数据。
我们这个窗体实现的就是: 对单个表执行多个查询
对于“学生基本信息维护”窗体,我实现的方法是借助于“组合关系”控件是否为空,若为空则不存在组合关系,若不为空即存在组合关系,那么需要再次输入一行的查询条件,
运用俩次的 if trim(if releation.text=””) 判断语句,来判断是否需要进行组合查询;
其实在敲这个窗体之前我是处于蒙蔽状态的,但是多亏了流程图帮助我理清了实现这个窗体的逻辑关系。
下面是我的流程图:
实现这个窗体我代码的主要框架是利用了if .. then...end if
下面是我的代码,我感觉有些代码量大的,但是很清晰,欢迎大家指点;
Private Sub cmdOk_Click()
Dim mrc As ADODB.Recordset
Dim txtsql As String
Dim msgtxt As String
Dim cola As String
Dim colb As String
Dim colc As String
'判断选择条件是否为空
If Trim(col1.Text = "") Then
MsgBox "请输入要查询的字段名字", vbOKOnly + vbExclamation, "警告"
Else
If Trim(fuhao1.Text = "") Then
MsgBox "请输入操作符", vbOKOnly + vbExclamation, "警告"
Else
If Trim(txt1.Text = "") Then
MsgBox "请输入有查询的内容", vbOKOnly + vbExclamation, "警告"
Else
If col1.Text = "卡号" Then
cola = " cardno"
Else
If col1.Text = "学号" Then
cola = " studentno"
Else
If col1.Text = "姓名" Then
cola = " studentName"
Else
If col1.Text = "性别" Then
cola = " sex"
Else
If col1.Text = "系别" Then
cola = " department"
Else
If col1.Text = "年级" Then
cola = " grade"
Else
If col1.Text = "班级" Then
cola = " class"
End If
End If
End If
End If
End If
End If
End If
Y1 = True
txtsql = "select * from student_info where" & cola & fuhao1.Text & "'" & txt1.Text & "'"
End If
End If
End If
'如果用户选择了关系操作符,说明要有组合查询,那么组合查询的信息需要验证是否完整
If Not Trim(relation1.Text = "") Then '如果第一个组合查询不为空,说明用户已经选择
a = True '用来判断已经选择了第一个关系操作符号
If Trim(col2.Text = "") Then
MsgBox "请输入要查询的字段名字"
Else
If Trim(fuhao2.Text = "") Then
MsgBox "请输入操作符"
Else
If Trim(txt2.Text = "") Then
MsgBox "请输入要查询的内容"
Else
If col2.Text = "卡号" Then
colb = " cardno"
Else
If col2.Text = "学号" Then
colb = " studentno"
Else
If col2.Text = "姓名" Then
colb = " studentName"
Else
If col2.Text = "性别" Then
colb = " sex"
Else
If col2.Text = "系别" Then
colb = " department"
Else
If col2.Text = "年级" Then
colb = " grade"
Else
If col2.Text = "班级" Then
colb = " class"
End If
End If
End If
End If
End If
End If
End If
If Trim(relation1.Text) = "与" Then
txtsql = txtsql & "and" & colb & fuhao2.Text & "'" & txt2.Text & "'"
Else
txtsql = txtsql & "or" & colb & fuhao2.Text & "'" & txt2.Text & "'"
End If
End If
End If
End If
End If
If Not Trim(relation2.Text = "") Then
b = True '用来判断已经选择了第二个关系操作符号
If Trim(col3.Text = "") Then
MsgBox "请输入要查询的字段名字"
Else
If Trim(fuhao3.Text = "") Then
MsgBox "请输入操作符号"
Else
If Trim(txt2.Text = "") Then
MsgBox "请输入要查询的内容"
Else
If col3.Text = "卡号" Then
colc = " cardno"
Else
If col3.Text = "学号" Then
colc = " studentno"
Else
If col3.Text = "姓名" Then
colc = " studentName"
Else
If col3.Text = "性别" Then
colc = " sex"
Else
If col3.Text = "系别" Then
colc = " department"
Else
If col3.Text = "年级" Then
colc = " grade"
Else
If col3.Text = "班级" Then
colc = " class"
End If
End If
End If
End If
End If
End If
End If
If Trim(relation2.Text) = "与" Then
txtsql = txtsql & "and" & colc & fuhao3.Text & "'" & txt3.Text & "'"
Else
txtsql = txtsql & "or" & colc & fuhao3.Text & "'" & txt3.Text & "'"
End If
Y3 = True
End If
End If
End If
End If
If a = True Or b = True Or Y1 = True Then
Set mrc = ExecuteSQL(txtsql, msgtxt)
If mrc.EOF Then
MsgBox "不存在该信息", vbOKOnly + vbExclamation, "警告"
Else
With myFlexGrid1
.Rows = 2
.CellAlignment = 4
.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) = "操作老师"
End With
End If
Do While Not mrc.EOF
i = i + 1
With myFlexGrid1
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(i, 1) = Trim(mrc.Fields(0))
.TextMatrix(i, 2) = Trim(mrc.Fields(1))
.TextMatrix(i, 3) = Trim(mrc.Fields(2))
.TextMatrix(i, 4) = Trim(mrc.Fields(3))
.TextMatrix(i, 5) = Trim(mrc.Fields(4))
.TextMatrix(i, 6) = Trim(mrc.Fields(5))
.TextMatrix(i, 7) = Trim(mrc.Fields(6))
.TextMatrix(i, 8) = Trim(mrc.Fields(7))
.TextMatrix(i, 9) = Trim(mrc.Fields(9))
mrc.MoveNext
End With
Loop
Else
MsgBox "没有输入条件无法查询,请先输入查询内容", vbOKOnly + vbExclamation, "警告"
End If
End Sub