机房收费系统之查询系

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 =                       '不能为0，导致下面的为无效的行值
        .CellAlignment = 
        .TextMatrix(, ) = "卡号"
        .TextMatrix(, ) = "上机日期"
        .TextMatrix(, ) = "上机时间"
        .TextMatrix(, ) = "下机日期"
        .TextMatrix(, ) = "下机时间"
        .TextMatrix(, ) = "消费金额"
        .TextMatrix(, ) = "余额"
        .TextMatrix(, ) = "备注"
        Do While mrc.EOF = False
            .Rows = .Rows + 
            .CellAlignment = 
            .TextMatrix(.Rows - , ) = mrc.Fields()
            .TextMatrix(.Rows - , ) = mrc.Fields()
            .TextMatrix(.Rows - , ) = mrc.Fields()
            .TextMatrix(.Rows - , ) = mrc.Fields()
            .TextMatrix(.Rows - , ) = mrc.Fields()
            .TextMatrix(.Rows - , ) = mrc.Fields()
            .TextMatrix(.Rows - , ) = mrc.Fields()
            .TextMatrix(.Rows - , ) = mrc.Fields()
            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、有三种逻辑情况，这样考虑就会简单很多。