大家好,又见面了,我是你们的朋友全栈君。组合查询也是我们在机房收费系统中接触到的一个新东西,一开始无从下手,后来也在摸爬滚打中实现了。首先思路要清晰,明白这些功能的内在逻辑关系,代码实现起来才会更顺畅。
一.判断第1行查询条件是否为空,若都不为空而且组合关系4 为空,查询到的结果符合第一行查询条件。
’现在发现自己敲的时候没注意明明规范,大家可能看不太懂
'combo1:字段名
'combo2:操作符
'combo3:组合关系
Private Sub Form_Load()
Dim i, j, k
Me.Height = 10185
Me.Width = 15135
'加载窗体时依次填充comboBox的下拉列表
For i = 0 To 2
With Combo1(i)
.AddItem "教师"
.AddItem "注册时间"
.AddItem "注册日期"
.AddItem "注销时间"
.AddItem "注销日期"
.AddItem "机器名"
End With
Next i
For j = 0 To 2
With Combo2(j)
.AddItem "="
.AddItem "<"
.AddItem ">"
.AddItem "<>"
End With
Next j
For k = 0 To 1
With Combo3(k)
.AddItem "与"
.AddItem "或"
End With
Next k
’如果组合查询框为空(不是“与”也不是“或”),不能添加下面的查询条件
If Combo3(0).Text = Trim("") Then
Combo1(1).Enabled = False
Combo1(2).Enabled = False
Combo2(1).Enabled = False
Combo2(2).Enabled = False
Text2.Enabled = False
Text3.Enabled = False
Combo3(1).Enabled = False
End If
If Combo3(1).Text = Trim("") Then
Combo1(2).Enabled = False
Combo2(2).Enabled = False
Text3.Enabled = False
End If
End Sub
'激活
Private Sub Combo3_Click(Index As Integer)
If Combo3(0).Text <> Trim("") Then
Combo1(1).Enabled = True
Combo2(1).Enabled = True
Combo3(1).Enabled = True
Text2.Enabled = True
End If
If Combo3(1).Text <> Trim("") Then
Combo1(2).Enabled = True
Combo2(2).Enabled = True
Text3.Enabled = True
End If
End Sub
Private Sub cmdInquire_Click()
Dim MsgText As String
Dim txtSQL As String
Dim mrc As ADODB.Recordset
Dim strCon1, strCon2, strcon3, strcon4, strcon5
’把汉字转换成数据库中的字段名,实现第一行查询
Select Case Combo1(0).Text
Case "教师"
strCon1 = " UserID"
Case "注册时间"
strCon1 = " LoginTime"
Case "注册日期"
strCon1 = " LoginDate"
Case "注销时间"
strCon1 = "LogoutTime"
Case "注销日期"
strCon1 = "logoutDate"
Case "机器名"
strCon1 = " Computer"
End Select
If Not Testtxt(Combo1(0).Text) Then
MsgBox "请选择字段名", vbOKOnly + vbExclamation, "提示"
Combo1(0).SetFocus
Exit Sub
ElseIf Not Testtxt(Combo2(0).Text) Then
MsgBox "请选择操作符", vbOKOnly + vbExclamation, "提示"
Combo2(0).SetFocus
Exit Sub
ElseIf Not Testtxt(Text1.Text) Then
MsgBox "请输入要查询的内容"
Text1.SetFocus
Exit Sub
Else
txtSQL = "select * from worklog_Info where " & strCon1 & Combo2(0).Text & "'" & Text1.Text & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "该条件的数据不存在", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
End If
'实现第二行查询
If Not Testtxt(Combo3(0).Text) Then
GoTo Case1
Else
Select Case Combo3(0).Text
Case "与"
strCon2 = "and"
Case "或"
strCon2 = "or"
End Select
Select Case Combo1(1).Text
Case "教师"
strcon3 = "UserID"
Case "注册时间"
strcon3 = "LoginTime"
Case "注册日期"
strcon3 = "LoginDate"
Case "注销时间"
strcon3 = " LogoutTime"
Case "注销日期"
strcon3 = " logoutDate"
Case "机器名"
strcon3 = "Computer"
End Select
If Not Testtxt(Combo1(1).Text) Then
MsgBox "请选择字段名", vbOKOnly + vbExclamation, "提示"
Combo1(1).SetFocus
Exit Sub
ElseIf Not Testtxt(Combo2(1).Text) Then
MsgBox "请选择操作符", vbOKOnly + vbExclamation, "提示"
Combo2(1).SetFocus
Exit Sub
ElseIf Not Testtxt(Text2.Text) Then
MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "提示"
Text2.SetFocus
Exit Sub
ElseIf Combo1(1).ListIndex = 1 Or 2 Or 3 Or 4 Then
MonthView1.Visible = True
Text2.Text = MonthView1.Value
Else
txtSQL = txtSQL & strCon2 & " " & strcon3 & Combo2(1).Text & "'" & Text2.Text & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "该条件的数据不存在", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
End If
End If
’实现第三行查询
If Trim(Combo3(1).Text) = "" Then
GoTo Case1
Else
Select Case Combo3(1).Text
Case "与"
strcon4 = "and"
Case "或"
strcon4 = "or"
End Select
Select Case Combo1(2).Text
Case "教师"
strcon5 = "UserID"
Case "注册时间"
strcon5 = "LoginTime"
Case "注册日期"
strcon5 = "LoginDate"
Case "注销时间"
strcon5 = " LogoutTime"
Case "注销日期"
strcon5 = " logoutDate"
Case "机器名"
strcon5 = "Computer"
End Select
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(Text3.Text) Then
MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "提示"
Text3.SetFocus
Exit Sub
End If
If Combo1(2).ListIndex = 1 Or 2 Or 3 Or 4 Then
MonthView1.Visible = True
Text3.Text = MonthView1.Value
End If
txtSQL = txtSQL & strcon4 & " " & strcon5 & Combo2(2) & "'" & Text3.Text & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "该条件的数据不存在", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
End If
Case1:
With myflexgrid
.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
.CellAlignment = 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) = Format(mrc.Fields(5))
.TextMatrix(.Rows - 1, 6) = Format(mrc.Fields(6))
.TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(7))
.TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(8))
mrc.MoveNext
Loop
End With
End Sub
Private Function FieldName(strFieldName as String) As String
Select Case strFieldName
Case "教师"
FieldName = "UserID"
Case "注册时间"
FieldName = "LoginTime"
Case "注册日期"
FieldName = "LoginDate"
Case "注销时间"
FieldName = " LogoutTime"
Case "注销日期"
FieldName = " logoutDate"
Case "机器名"
FieldName = "Computer"
End Select
End Sub
'以条件1和2组合为例,相应的连接数据库的代码可改为
Private Sub cmdInquire_Click()
txtSQL="select * from worklog_Info where " & FieldName(combo1(0).Text)
& combo2(0).Text & "'" & text1.Text & "'"
Select Case combo3(0).Text
Case "或"
txtSQL=txtSQL & "or" & FieldName(combo1(1).Text)
& combo2(1).Text & "'" & text2.Text & "'"
Case "与"
txtSQL=txtSQL & "and" & FieldName(combo1(1).Text)
& combo2(1).Text & "'" & text2.Text & "'"
End Select
End Sub
Private Function FieldName1(strFieldName as String)As String
Select Case strFieldName
Case "与"
FieldName = "and"
Case "或"
FieldName = "or"
End Select
End Sub
'或者也可以这么写
Private Sub cmdInquire_Click()
txtSQL="select * from worklog_Info where "
strA= FieldName(combo1(0).Text) & combo2(0).Text & "'" & text1.Text & "'"
strB=FieldName1(combo3(0).Text) & FieldName(combo1(1).Text) & combo2(1).Text
& "'" & text2.Text & "'"
strC=FieldName1(combo3(1).Text) & FieldName(combo1(2).Text) & combo2(2).Text
& "'" & text3.Text & "'"
txtSQL=txtSQL & strA & strB & strC
End Sub
优化2:
Public Function Testtxt(txt As String) As Boolean
If Trim(txt) = "" Then
Testtxt = False
Else
Testtxt = True
End If
End Function
Private Sub cmdInquire_Click()
isEmpty= Testtxt(combo1(0).Text) or Testtxt(combo2(0).Text) or Testtxt(text1.Text)
If isEmpty=False Then
Msgbox"请输入查询条件",48,"提示"
End If
End Sub
用这种方法使代码精简了不少,但是与最初的相比,用户不能直接明了的看出具体是哪个查询条件没选,各有各的好处。
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/105791.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...