机房收费系统之 组合查询

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

首先准备工作是，将 数据库中的字段和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.CloseEnd Sub