背景:相对于学生来说,这是我第二次接触到组合查询了,不过我的第一次的方法有点麻烦了,但是很容易理解。有的代码重复了,构造一个函数中就 好 了。总的来说,组合查询的逻辑很简单,难就难在有些小的细节。
需求分析:1.两种组合查询的实现。
2.修改选中MSHFlexGrid中的数据,在新的窗体中显示。
思路分析:1.突破点为组合关系,可以确定两种组合关系。
2.第一种组合查询在基本查询添加限制条件。第二种组合查询在第一种组合查询添加限制条件。
3.将文字转换为数据库中的字段,否则无法进行筛选。
学习的知识:
1.组合查询简单的运用。
2.如何选中MSHFlexGrid中的数据,并将选中的数据添加到新的窗体中。
代码部分:
Private Function FieldName(strFieldName As String) As String'汉字转化成数据库中的字段
Select Case strFieldName
Case "卡号"
FieldName = "cardno"
Case "学号"
FieldName = "studentNo"
Case "姓名"
FieldName = "studentName"
Case "性别"
FieldName = "sex"
Case "系别"
FieldName = "department"
Case "年级"
FieldName = "grade"
Case "班级"
FieldName = "class"
End Select
End Function
Private Sub CmdQuery_Click()
Dim txtSQL As String
Dim msgtext As String
Dim mrc As ADODB.Recordset
If (Cbofield(0).Text = "") Then '构建基本查询的条件
MsgBox "请输入字段名", 48, "警告"
Cbofield(0).SetFocus
Exit Sub
ElseIf (CboOperate(0).Text = "") Then
MsgBox "请输入操作符", 48, "警告"
CboOperate(0).SetFocus
Exit Sub
ElseIf (TxtContent(0).Text = "") Then
MsgBox "请输入要查询的内容", 48, "警告"
TxtContent(0).SetFocus
Exit Sub
End If
txtSQL = "select * from student_Info where " 'where后面连接条件,要有空格。
If (CboRelation(0).Text = "") Then '从组合关系入手,先进行基本查询。
txtSQL = txtSQL & FieldName(Cbofield(0).Text) & Trim(CboOperate(0).Text) & "'" & Trim(TxtContent(0).Text) & "'"
Set mrc = ExecuteSql(txtSQL, msgtext) '建立执行,判断数据库中有无数据。
If (mrc.EOF) Then
MsgBox "该条件的数据不存在", 48, "警告"
End If
ElseIf (CboRelation(1).Text = "") Then '建立第一条组合查询。
If (Cbofield(1).Text = "") Then
MsgBox "请输入字段名", 48, "警告"
Cbofield(1).SetFocus
Exit Sub
ElseIf (CboOperate(1).Text = "") Then
MsgBox "请输入操作符", 48, "警告"
CboOperate(1).SetFocus
Exit Sub
ElseIf (TxtContent(1).Text = "") Then
MsgBox "请输入要查询的内容", 48, "警告"
TxtContent(1).SetFocus
Exit Sub
Else
txtSQL = txtSQL & FieldName(Cbofield(0).Text) & Trim(CboOperate(0).Text) & "'" & Trim(TxtContent(0).Text) & "'"
Select Case CboRelation(0).Text
Case "或"
txtSQL = txtSQL & " Or " & FieldName(Cbofield(1).Text) & Trim(CboOperate(1).Text) & "'" & Trim(TxtContent(1).Text) & "'"
Case "与"
txtSQL = txtSQL & " And " & FieldName(Cbofield(1).Text) & Trim(CboOperate(1).Text) & "'" & Trim(TxtContent(1).Text) & "'"
End Select
Set mrc = ExecuteSql(txtSQL, msgtext)
If (mrc.EOF) Then
MsgBox "该条件的数据不存在", 48, "警告"
End If
End If
Else '建立第二个组合查询。
If (Cbofield(2).Text = "") Then
MsgBox "请输入字段名", 48, "警告"
Cbofield(2).SetFocus
Exit Sub
ElseIf (CboOperate(2).Text = "") Then
MsgBox "请输入操作符", 48, "警告"
CboOperate(2).SetFocus
Exit Sub
ElseIf (TxtContent(2).Text = "") Then
MsgBox "请输入要查询的内容", 48, "警告"
TxtContent(2).SetFocus
Exit Sub
Else
txtSQL = txtSQL & FieldName(Cbofield(0).Text) & Trim(CboOperate(0).Text) & "'" & Trim(TxtContent(0).Text) & "'"
Select Case CboRelation(0).Text
Case "或"
txtSQL = txtSQL & " Or " & FieldName(Cbofield(1).Text) & Trim(CboOperate(1).Text) & "'" & Trim(TxtContent(1).Text) & "'"
Case "与"
txtSQL = txtSQL & " And " & FieldName(Cbofield(1).Text) & Trim(CboOperate(1).Text) & "'" & Trim(TxtContent(1).Text) & "'"
End Select
'在基本查询以及第一个组合查询中进行第二个组合查询。
Select Case CboRelation(1).Text
Case "或"
txtSQL = txtSQL & " Or " & FieldName(Cbofield(2).Text) & Trim(CboOperate(2).Text) & "'" & Trim(TxtContent(2).Text) & "'"
Case "与"
txtSQL = txtSQL & " And " & FieldName(Cbofield(2).Text) & Trim(CboOperate(2).Text) & "'" & Trim(TxtContent(2).Text) & "'"
End Select
Set mrc = ExecuteSql(txtSQL, msgtext)
<span style="white-space:pre"> </span> If (mrc.EOF) Then
<span style="white-space:pre"> </span>MsgBox "该条件的数据不存在", 48, "警告"
End If
End If
End If
MSHFlexGrid选中的数据添加到新的窗体中。
<pre name="code" class="html">Private Function FieldName(strFieldName As String) As String'汉字转化成数据库中的字段
Select Case strFieldName
Case "卡号"
FieldName = "cardno"
Case "学号"
FieldName = "studentNo"
Case "姓名"
FieldName = "studentName"
Case "性别"
FieldName = "sex"
Case "系别"
FieldName = "department"
Case "年级"
FieldName = "grade"
Case "班级"
FieldName = "class"
End Select
End Function
Private Sub CmdQuery_Click()
Dim txtSQL As String
Dim msgtext As String
Dim mrc As ADODB.Recordset
If (Cbofield(0).Text = "") Then '构建基本查询的条件
MsgBox "请输入字段名", 48, "警告"
Cbofield(0).SetFocus
Exit Sub
ElseIf (CboOperate(0).Text = "") Then
MsgBox "请输入操作符", 48, "警告"
CboOperate(0).SetFocus
Exit Sub
ElseIf (TxtContent(0).Text = "") Then
MsgBox "请输入要查询的内容", 48, "警告"
TxtContent(0).SetFocus
Exit Sub
End If
txtSQL = "select * from student_Info where " 'where后面连接条件,要有空格。
If (CboRelation(0).Text = "") Then '从组合关系入手,先进行基本查询。
txtSQL = txtSQL & FieldName(Cbofield(0).Text) & Trim(CboOperate(0).Text) & "'" & Trim(TxtContent(0).Text) & "'"
Set mrc = ExecuteSql(txtSQL, msgtext) '建立执行,判断数据库中有无数据。
If (mrc.EOF) Then
MsgBox "该条件的数据不存在", 48, "警告"
End If
ElseIf (CboRelation(1).Text = "") Then '建立第一条组合查询。
If (Cbofield(1).Text = "") Then
MsgBox "请输入字段名", 48, "警告"
Cbofield(1).SetFocus
Exit Sub
ElseIf (CboOperate(1).Text = "") Then
MsgBox "请输入操作符", 48, "警告"
CboOperate(1).SetFocus
Exit Sub
ElseIf (TxtContent(1).Text = "") Then
MsgBox "请输入要查询的内容", 48, "警告"
TxtContent(1).SetFocus
Exit Sub
Else
txtSQL = txtSQL & FieldName(Cbofield(0).Text) & Trim(CboOperate(0).Text) & "'" & Trim(TxtContent(0).Text) & "'"
Select Case CboRelation(0).Text
Case "或"
txtSQL = txtSQL & " Or " & FieldName(Cbofield(1).Text) & Trim(CboOperate(1).Text) & "'" & Trim(TxtContent(1).Text) & "'"
Case "与"
txtSQL = txtSQL & " And " & FieldName(Cbofield(1).Text) & Trim(CboOperate(1).Text) & "'" & Trim(TxtContent(1).Text) & "'"
End Select
Set mrc = ExecuteSql(txtSQL, msgtext)
If (mrc.EOF) Then
MsgBox "该条件的数据不存在", 48, "警告"
End If
End If
Else '建立第二个组合查询。
If (Cbofield(2).Text = "") Then
MsgBox "请输入字段名", 48, "警告"
Cbofield(2).SetFocus
Exit Sub
ElseIf (CboOperate(2).Text = "") Then
MsgBox "请输入操作符", 48, "警告"
CboOperate(2).SetFocus
Exit Sub
ElseIf (TxtContent(2).Text = "") Then
MsgBox "请输入要查询的内容", 48, "警告"
TxtContent(2).SetFocus
Exit Sub
Else
txtSQL = txtSQL & FieldName(Cbofield(0).Text) & Trim(CboOperate(0).Text) & "'" & Trim(TxtContent(0).Text) & "'"
Select Case CboRelation(0).Text
Case "或"
txtSQL = txtSQL & " Or " & FieldName(Cbofield(1).Text) & Trim(CboOperate(1).Text) & "'" & Trim(TxtContent(1).Text) & "'"
Case "与"
txtSQL = txtSQL & " And " & FieldName(Cbofield(1).Text) & Trim(CboOperate(1).Text) & "'" & Trim(TxtContent(1).Text) & "'"
End Select
'在基本查询以及第一个组合查询中进行第二个组合查询。
Select Case CboRelation(1).Text
Case "或"
txtSQL = txtSQL & " Or " & FieldName(Cbofield(2).Text) & Trim(CboOperate(2).Text) & "'" & Trim(TxtContent(2).Text) & "'"
Case "与"
txtSQL = txtSQL & " And " & FieldName(Cbofield(2).Text) & Trim(CboOperate(2).Text) & "'" & Trim(TxtContent(2).Text) & "'"
End Select
Set mrc = ExecuteSql(txtSQL, msgtext)
<span style="white-space:pre"> </span> If (mrc.EOF) Then
<span style="white-space:pre"> </span> MsgBox "该条件的数据不存在", 48, "警告"
End If
End If
End If
MSHFlexGrid选中的数据添加到新的窗体中。
Private Sub cmdChange_Click()
<span style="white-space:pre"> </span>If (MSHFlexGrid1.TextMatrix(MSHFlexGrid1.row, 0) = "" Or MSHFlexGrid1.TextMatrix(MSHFlexGrid1.row, 0) = "学号") Then
<span style="white-space:pre"> </span> MsgBox "请选择学生!", 48, "警告"
<span style="white-space:pre"> </span>Else
<span style="white-space:pre"> </span>With MSHFlexGrid1
<span style="white-space:pre"> </span>frmChangeStudent.txtStudentno.Text = .TextMatrix(.row, 0)
<span style="white-space:pre"> </span>frmChangeStudent.txtStudentName.Text = .TextMatrix(.row, 1)
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>frmChangeStudent.txtCardno.Text = .TextMatrix(.row, 2)
<span style="white-space:pre"> </span>frmChangeStudent.txtCash = .TextMatrix(.row, 3)
<span style="white-space:pre"> </span>frmChangeStudent.txtdepartment = .TextMatrix(.row, 4)
<span style="white-space:pre"> </span>frmChangeStudent.txtGrade.Text = .TextMatrix(.row, 5)
<span style="white-space:pre"> </span>frmChangeStudent.txtClass.Text = .TextMatrix(.row, 6)
<span style="white-space:pre"> </span>frmChangeStudent.txtStatus.Text = .TextMatrix(.row, 8) frmChangeStudent.txtexplain.Text = .TextMatrix(.row, 9)
<span style="white-space:pre"> </span>frmChangeStudent.txtType.Text = .TextMatrix(.row, 10)
frmChangeStudent.txtSex.Text = .TextMatrix(.row, 7)
End With
frmChangeStudent.Show
<span style="white-space:pre"> </span>End If
End Sub