1、查询
查询操作不会对数据库中的表进行修改,只要显示出符合条件的记录即可。同时查询操作还有一个特点,查询出的信息是用控件MSHFlexGrid控件显示。MSHFlexGrid控件显示和操作表格数据。其对包含字符串的表格提供了灵活的排序、插入数据和格式编排功能,使用简单操作方便同时满足系统功能的要求。
请看窗体:
下边是代码:
Dim mrcs As ADODB.Recordset
If Not texttxt(txtcardno.Text) Then
MsgBox "请输入卡号!", vbOKOnly + vbExclamation, "提示"
txtcardno.SetFocus
Exit Sub
End If
Txtsql = "select * from student_info where status='使用' and cardno='" & txtcardno.Text & "'"
Set mrcs = ExecuteSQl(Txtsql, msgTxt)
If mrcs.EOF Then
MsgBox "该卡没有注册或者已经退卡", vbOKOnly + vbExclamation, "提示"
txtcardno.Text = ""
txtcardno.SetFocus
Exit Sub
End If
Txtsql = "select * from line_info where cardno='" & txtcardno.Text & "' and offdate<>'" & Null & "'"
'双重选择,可以避免查找到的卡号正在上机,一些内容为空,报错
Set mrc = ExecuteSQl(Txtsql, msgTxt)
If mrc.EOF Then
MsgBox "没有该数据或者该卡号不存在", vbOKOnly + vbExclamation, "提示"
txtcardno.Text = ""
txtcardno.SetFocus
Exit Sub
End If
With myflexgrid
.Rows = 1 '不能为0,导致下面的为无效的行值
.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) = "备注"
Do While mrc.EOF = False
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = mrc.Fields(1)
.TextMatrix(.Rows - 1, 1) = mrc.Fields(6)
.TextMatrix(.Rows - 1, 2) = mrc.Fields(7)
.TextMatrix(.Rows - 1, 3) = mrc.Fields(8)
.TextMatrix(.Rows - 1, 4) = mrc.Fields(9)
.TextMatrix(.Rows - 1, 5) = mrc.Fields(11)
.TextMatrix(.Rows - 1, 6) = mrc.Fields(12)
.TextMatrix(.Rows - 1, 7) = mrc.Fields(13)
mrc.MoveNext '必要的代码
Loop
End With
End Sub
这样查询工作就完成了。
2、组合查询:
下边是代码:
Private SubcmdInquire_Click()
Dim Up_Rst AsADODB.Recordset
Dim strtxtSQL AsString
Dim strMsg AsString
On Error GoToError_Handle
'如果仅有一个查询条件
If cboRelation1.Text = "" Then
cboRelation2.Enabled = False
'查询
strtxtSQL = "select * from Up where" & Trim(cboFileName1.Tag) & " " &Trim(cboSign1.Text) & " '" & Trim(txtInquire1.Text) &"'"
Set Up_Rst = QuerySQL(strtxtSQL, strMsg)
Else
If cboRelation2.Text="" Then
'查询
strtxtSQL = "select * from Up where" & Trim(cboFileName1.Tag) & " " &Trim(cboSign1.Text) & "'" & Trim(txtInquire1.Text) &"
'" & Trim(cboRelation1.Tag) & "" &Trim(cboFileName2.Tag) & " " & Trim(cboSign2.Text) &"
'" & Trim(txtInquire2.Text) & "'"
Set Up_Rst = QuerySQL(strtxtSQL, strMsg)
Else
'查询并显示符合三个条件的记录
strtxtSQL = "select * fromUp where " & Trim(cboFileName1.Tag) & " " &Trim(cboSign1.Text) & "'" & Trim(txtInquire1.Text) &"
' " & Trim(cboRelation1.Tag) & "" &Trim(cboFileName2.Tag) & " " & Trim(cboSign2.Text) &"
'" & Trim(txtInquire2.Tag) & "'" &Trim(cboRelation2.Tag) & "" & Trim(cboFileName3.Tag) &"
" & Trim(cboSign3.Text) & " '" &Trim(txtInquire3.Text) & "'"
Set Up_Rst = QuerySQL(strtxtSQL,strMsg)
End If
End If ```
逻辑查询根据逻辑“与” 、“或”、“空的”总共分为3种情况进行考虑:1、有一个查询条件;2、有两个
查询条件;3、有三种逻辑情况,这样考虑就会简单很多。