在做《机房收费系统》的时候多次用到组合查询,对于组合查询关键的有两步:
(1)对组合查询的各个查询条件做标记
因为查询条件有很多,所以很难像以前一样把条件直接写到链接语句中查询。又由于vb中的内容往往和数据库中的字段不一样,这就需要对查询条件做一下标记(这算是第一次真正的用到给vb中的内容做标记,虽然不难,但还是值得提一下)
下面用的是控件的tag属性来达到vb与数据库内容之间的转换,这个方法有很多种,总之是比较简单的代码编写
Private Sub cboFields1_Click() '把查询条件转换成SQL可识别的字段
If cboFields1.Text = "卡号" Then cboFields1.Tag = "CardNO"
If cboFields1.Text = "学号" Then cboFields1.Tag = "StudentNO"
If cboFields1.Text = "姓名" Then cboFields1.Tag = "StudentName"
If cboFields1.Text = "性别" Then cboFields1.Tag = "Sex"
If cboFields1.Text = "系别" Then cboFields1.Tag = "Department"
If cboFields1.Text = "年级" Then cboFields1.Tag = "Grade"
If cboFields1.Text = "班号" Then cboFields1.Tag = "Class"
End Sub
(2)查询语句的编写
这一块儿是组合查询的核心部分,代码中对txtSQL进行了多次添加修改。我出错的部分基本上都是对txtSQL的编写上,主要在“()、""、 & ”这三个符号上
'连接数据库表Student_Info
txtSQL = "select * from Student_Info where ( " & cboFields1.Tag & cboOperator1.Text & "'" & txtContent1.Text & "'"
txtSQL = txtSQL & " " & cboUnit1.Tag & " " & cboFields2.Tag & cboOperator2.Text & "'" & txtContent2.Text & "'"
txtSQL = txtSQL & " " & cboUnit2.Tag & " " & cboFields3.Tag & cboOperator3.Text & "'" & txtContent3.Text & "'"
txtSQL = txtSQL & ")" '把txtsql语句补充完整
下面是txtSQL语句完整的代码过程
'定义连接数据库表Student_Info的变量
Dim txtSQL As String
Dim MsgString As String
Dim mrt As ADODB.Recordset
'连接数据库表Student_Info
txtSQL = "select * from Student_Info where ( " & cboFields1.Tag & cboOperator1.Text & "'" & txtContent1.Text & "'"
'检验第一组查询信息是否为空
If cboFields1.Text = "" Then
MsgBox "请输入字段名!", vbOKOnly + vbExclamation, "警告"
Exit Sub
ElseIf cboOperator1.Text = "" Then
MsgBox "请输入操作符!", vbOKOnly + vbExclamation, "警告"
Exit Sub
ElseIf txtContent1.Text = "" Then
MsgBox "请输入查询内容!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
'检验第二组查询信息是否为空
If cboUnit1.Text <> "" Then
If cboFields2.Text = "" Then
MsgBox "请输入字段名!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
If cboOperator2.Text = "" Then
MsgBox "请输入操作符!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
If txtContent2.Text = "" Then
MsgBox "请输入查询内容!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
'第二组条件无误,添加信息
txtSQL = txtSQL & " " & cboUnit1.Tag & " " & cboFields2.Tag & cboOperator2.Text & "'" & txtContent2.Text & "'"
End If
'检验第三组查询信息是否为空
If cboUnit2.Text <> "" Then
If cboFields3.Text = "" Then
MsgBox "请输入字段名!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
If cboOperator3.Text = "" Then
MsgBox "请输入操作符!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
If txtContent3.Text = "" Then
MsgBox "请输入查询内容!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
'第三组条件无误,添加信息
txtSQL = txtSQL & " " & cboUnit2.Tag & " " & cboFields3.Tag & cboOperator3.Text & "'" & txtContent3.Text & "'"
End If
txtSQL = txtSQL & ")" '把txtsql语句补充完整
接下来就可以按照我们以往的查询方法进行代码编写了。