機房收費系統之查詢系

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、有三種邏輯情況，這樣考慮就會簡單很多。