机房收费系统之组合查询

最新推荐文章于 2019-01-27 13:38:47 发布

Dm同学

最新推荐文章于 2019-01-27 13:38:47 发布

阅读量1.2k

点赞数

分类专栏：【数据库】

本文链接：https://blog.csdn.net/u010176014/article/details/14225035

版权

【数据库】专栏收录该内容

19 篇文章 0 订阅

订阅专栏

总是把它想象的很难。所以才会畏惧。其实只要逻辑清晰，就好了。

首先准备工作是，将数据库中的字段和vb界面的选项对应。

Private Function fieldName(strfieldName As String) As String
'将程序的字对应为数据库
    
    Select Case strfieldName
        Case "卡号"
            fieldName = "cardNo"
        Case "姓名"
            fieldName = "studentName"
        Case "上级时间"
            fieldName = "onTime"
        Case "上级日期"
            fieldName = "onDate"
        Case "与"
            fieldName = "and"
        Case "或"
            fieldName = "or"
        Case "机房号"
            fieldName = "computer"
            
            
        End Select

然后实现过程分三种情况。

Private Sub Command1_Click()
    Dim mrc As adodb.Recordset
    Dim txtSQL As String
    Dim msgString As String
    Dim txtSQL1 As String
    Dim txtSQL2 As String
    Dim txtSQL3 As String
    
    txtSQL1 = "select * from onLine where " & " " & fieldName(cmbLetter1.Text) & cmbOperate1.Text & "'" & txtEnquire1 & "'"
    txtSQL2 = "select * from onLine where " & " " & fieldName(cmbLetter1.Text) & cmbOperate1.Text & "'" & txtEnquire1.Text & "'" + fieldName(cmbCombination1.Text) & " " & fieldName(cmbLetter2.Text) & cmbOperate2.Text & "'" & txtEnquire2 & "'"
     txtSQL3 = "select * from onLine where " & " " & fieldName(cmbLetter1.Text) & cmbOperate1.Text & "'" & txtEnquire1.Text & "'" & " " & fieldName(cmbCombination1.Text) & " " & _
                                                                    fieldName(cmbLetter2.Text) & cmbOperate2.Text & "'" & txtEnquire2.Text & "'" & " " & fieldName(cmbCombination1.Text) & " " & _
                                                                    fieldName(cmbLetter3.Text) & cmbOperate3.Text & "'" & txtEnquire3 & "'"


    '---------------------------------------------当有一种条件时----------
         If Not testtxt(cmbCombination1.Text) Then '
                txtSQL = txtSQL1
          Else ' ------------------------当有两种条件时-----
                 If Not testtxt(cmbLetter2.Text) Or Not testtxt(cmbOperate2.Text) Or Not testtxt(txtEnquire2.Text) Then
                   MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
                 Else
                    If Not testtxt(cmbCombination2.Text) Then '情况2 组合2为空
                            txtSQL = txtSQL2
                     Else '--------------------------以下是：两个组合关系都有内容

                            If Not testtxt(cmbLetter3.Text) Or Not testtxt(cmbOperate3.Text) Or Not testtxt(txtEnquire3.Text) Then
                                  MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
                            Else
                                  txtSQL = txtSQL3
                            End If
                        End If
                    End If
             End If
        End If

最后执行。

  '开始执行sql-------------------------------------------
        Set mrc = ExecuteSQL(txtSQL, msgString)
        If mrc.EOF Then
            MsgBox "尚无记录！", vbOKOnly + vbExclamation, "提示"
        Else
            With MSFlexGrid1
                .Rows = 1
                .Cols = 5
                .CellAlignment = 4
                 .TextMatrix(0, 0) = "卡号"
                 .TextMatrix(0, 1) = "姓名"
                 .TextMatrix(0, 2) = "上机日期"
                 .TextMatrix(0, 3) = "上机时间"
                 .TextMatrix(0, 4) = "机房号"
             End With
        
            Do While Not mrc.EOF
                 With MSFlexGrid1
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 0) = mrc.Fields(0)
                    .TextMatrix(.Rows - 1, 1) = mrc.Fields(3)
                    .TextMatrix(.Rows - 1, 2) = mrc.Fields(6)
                    .TextMatrix(.Rows - 1, 3) = mrc.Fields(7)
                    .TextMatrix(.Rows - 1, 4) = mrc.Fields(8)
                    mrc.MoveNext
                End With
            Loop
    End If
     mrc.Close
End Sub