前言:
我们都用过简单的查询,组合查询在这之前也只是简单的了解一下,通过机房的学习,对组合查询有了更深刻的认识。
总结:
机房中用到组合查询的窗体一共有4个,基本流程都一样,下面就以“操作员工作记录查询”为例,分析一下组合查询,纯属个人见解,有问题欢迎评论。
一、窗体登录的时候给各个控件添加内容
cboField1.AddItem "教师"
cboField1.AddItem "注册日期"
cboField1.AddItem "注册时间"
cboField1.AddItem "注销日期"
cboField1.AddItem "注销时间"
cboField1.AddItem "机器名"
cboConnect1.AddItem "与"
cboConnect1.AddItem "或"
其他控件类似。
二、查询的过程,注意转化字段名内容和组合关系内容
1.总体代码
Dim txtSQL As String
Dim mrc As ADODB.Recordset
Dim Msgtext As String
Dim strcboField1, strcboField2, strcboField3 '转换字段名内容
Dim strcboConnect1, strcboConnect2 '转换组合关系内容
'第一行查询,转换汉字为数据库中的字段
Select Case cboField1.Text
Case "教师"
strcboField1 = "UserID"
Case "注册日期"
strcboField1 = "LoginDate"
Case "注册时间"
strcboField1 = "LoginTime"
Case "注销日期"
strcboField1 = "LogoutDate"
Case "注销时间"
strcboField1 = "LogoutTime"
Case "机器名"
strcboField1 = "computer"
End Select
If Trim(cboField1.Text) = "" Or Trim(cboOpe1.Text) = "" Or (Trim(txtInquiry1.Text) = "" And Trim(DTPicker1.Value = "")) Then
MsgBox "请将选项内容填写完整!", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
'在worklog_info这张表中获得整行记录
If DTPicker1.Visible = True Then
txtSQL = "select * from worklog_Info where " & strcboField1 & cboOpe1.Text & "'" & DTPicker1.Value & "'"
Else
txtSQL = "select * from worklog_Info where " & strcboField1 & cboOpe1.Text & "'" & txtInquiry1.Text & "'"
End If
Set mrc = ExecuteSQL(txtSQL, Msgtext)
End If
'实现第二行查询
If cboConnect1.Text = "请选择" Then
GoTo Case1
Else
Select Case cboConnect1.Text
Case "与"
strcboConnect1 = "and"
Case "或"
strcboConnect1 = "or"
End Select
Select Case cboField2.Text
Case "教师"
strcboField2 = "UserID"
Case "注册日期"
strcboField2 = "LoginDate"
Case "注册时间"
strcboField2 = "LoginTime"
Case "注销日期"
strcboField2 = "LogoutDate"
Case "注销时间"
strcboField2 = "LogoutTime"
Case "机器名"
strcboField2 = "computer"
End Select
If Trim(cboField2.Text) = "" Or Trim(cboOpe2.Text) = "" Or (Trim(txtInquiry2.Text) = "" And Trim(DTPicker2.Value = "")) Then
MsgBox "请将选项内容填写完整!", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
'在worklog_info这张表中获得整行记录。
If DTPicker2.Visible = False Then
txtSQL = txtSQL & strcboConnect1 & " " & strcboField2 & cboOpe2.Text & "'" & txtInquiry2.Text & "'"
Else
txtSQL = txtSQL & strcboConnect1 & " " & strcboField2 & cboOpe2.Text & "'" & DTPicker2.Value & "'"
End If
Set mrc = ExecuteSQL(txtSQL, Msgtext)
End If
End If
'实现第三行查询
If cboConnect2.Text = "请选择" Then
GoTo Case1
Else
Select Case cboConnect2.Text
Case "与"
strcboConnect2 = "and"
Case "或"
strcboConnect2 = "or"
End Select
Select Case cboField3.Text
Case "教师"
strcboField3 = "UserID"
Case "注册日期"
strcboField3 = "LoginDate"
Case "注册时间"
strcboField3 = "LoginTime"
Case "注销日期"
strcboField3 = "LogoutDate"
Case "注销时间"
strcboField3 = "LogoutTime"
Case "机器名"
strcboField3 = "computer"
End Select
If Trim(cboField3.Text) = "" Or Trim(cboOpe3.Text) = "" Or (Trim(txtInquiry3.Text) = "" And Trim(DTPicker3.Value = "")) Then
MsgBox "请将选项内容填写完整!", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
If DTPicker3.Visible = False Then
txtSQL = txtSQL & strcboConnect2 & " " & strcboField3 & cboOpe3.Text & "'" & txtInquiry3.Text & "'"
Else
txtSQL = txtSQL & strcboConnect2 & " " & strcboField3 & cboOpe3.Text & "'" & DTPicker3.Value & "'"
End If
Set mrc = ExecuteSQL(txtSQL, Msgtext)
If mrc.EOF Then
MsgBox "该条件的数据不存在!", vbOKOnly + vbInformation, "提示"
Exit Sub
End If
End If
End If
Case1:
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) = "状态"
Do While Not mrc.EOF
.Rows = .Rows + 1
.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) = Trim(mrc.Fields(5)) & ""
.TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6)) & ""
.TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(7)) & ""
.TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(8))
mrc.MoveNext
Loop
End With
mrc.Close
2.注意问题:
查询框上为什么有DTPicker控件?所起的作用是什么?
大家可以看到,cboField里加的内容有注册日期和注销日期等,那如果用户选择注册日期,是不是需要手动输入日期,如果输的日期格式不正确,是否会影响该软件的使用呢?为了给用户一个方便,便在txtInquiry上添加一个DTPicker控件,用代码使它在选择日期时出现DTPicker控件,选择教师之类的出现txtInquiry框。
如果选择教师,操作符里可以出现“<”“>”符号吗?
所以这里就是我们需要完善的地方。
Private Sub cboField1_Click()
cboOpe1.Clear '避免cboOpe1重复添加操作符
Select Case cboField1.Text
Case "教师"
cboOpe1.AddItem "="
cboOpe1.AddItem "<>"
txtInquiry1.Locked = False '选择教师的时候,可以用txtInquiry1,DTPicker1不可见
DTPicker1.Visible = False
Case "注册日期"
cboOpe1.AddItem "="
cboOpe1.AddItem "<"
cboOpe1.AddItem ">"
cboOpe1.AddItem "<>"
txtInquiry1.Locked = True
DTPicker1.Visible = True
DTPicker1.Format = dtpShortDate
Case "注册时间"
cboOpe1.AddItem "="
cboOpe1.AddItem "<"
cboOpe1.AddItem ">"
cboOpe1.AddItem "<>"
txtInquiry1.Locked = True
DTPicker1.Visible = True
DTPicker1.Format = dtpShortDate
Case "注销日期"
cboOpe1.AddItem "="
cboOpe1.AddItem "<"
cboOpe1.AddItem ">"
cboOpe1.AddItem "<>"
txtInquiry1.Locked = True
DTPicker1.Visible = True
DTPicker1.Format = dtpShortDate
Case "注销时间"
cboOpe1.AddItem "="
cboOpe1.AddItem "<"
cboOpe1.AddItem ">"
cboOpe1.AddItem "<>"
txtInquiry1.Locked = True
DTPicker1.Visible = True
DTPicker1.Format = dtpShortDate
Case "机器名"
cboOpe1.AddItem "="
cboOpe1.AddItem "<>"
txtInquiry1.Locked = False
DTPicker1.Visible = False
End Select
End Sub
其他两个cboField类似。
大家是否注意到.TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5)) & ""后面的 & "",那么它起的是什么作用呢?
比如说现在登录的12号,但是还没有注销。所以数据库表里的注销日期和注销时间为Null,如果不加 & "" ,会出现“无效使用Null”的情况。 简单说,就是为了避免为空。
后记:
这是组合查询需要注意的一系列问题,最后想说一下自己的感受:
1.控件命名:一定要规范,这样写代码或者改代码的时候很方便,不会出现改错位置的情况(我的也不是很规范);
2.代码规范:代码的位置一定要规范,不要都挤在一起。推荐使用Tab键;
3.简单说一下组合查询的逻辑吧:(图画的不是很好,有什么疑问欢迎私聊)