机房收费系统之组合查询

 

开始的时候觉得组合查询这个模块挺难实现的，所以开始的时候是将这个功能跳过了，是在最后的时候研究的大概用了一天多的时间吧。主要的逻辑关系如图所示：(使用忆图专家画的)

 

然后就是判断和查询了，因为很多地方要用到组合查询，所以首先应该集成一个函数，以防止总是写重复的代码：

第一个是判断文本框是否为空：

Public Function TxtIsEmpty(strTxt As String) As Boolean
'判断是否为空
    If strTxt = "" Then
        TxtIsEmpty = True
    Else
        txtisempyt = False
    End If
End Function

 

第二个是将人类语言转换成计算机可以懂的语言，以便于查询

Private Function FieldName(strFieldName As String) As String
'是汉字转化成数据库中的字段
   Select Case strFieldName
        Case "卡号"
            FieldName = "cardno"
        Case "姓名"
            FieldName = "stuname"
        Case "上机时间"
           FieldName = "ontime"
        Case "上机日期"
            FieldName = "ondate"
    End Select
End Function

 

有了这两个函数最后需要完成就是根据上图的关系图判断查询条件

Private Sub CmdQuery_Click()
    Dim strSQL As String
    Dim strMsg As String
    Dim RstOnline As ADODB.Recordset
    Dim Blnempty As Boolean
    '查询在线用户是否存在
    '组合查询
    strSQL = "select * from online_info where "
    
    Blnempty = TxtIsEmpty(CboField(0).Text) Or TxtIsEmpty(CboOperate(0).Text) Or TxtIsEmpty(TxtContent(0).Text)
    '判断第一个查询条件是否填写完整
    If TxtIsEmpty(CboRelation(0).Text) And TxtIsEmpty(CboRelation(1).Text) Then
          '判断是否需要组合查询
          If Blnempty Then
               MsgBox "请将查询条件填写完整", 48, "警告"
               Exit Sub
          Else
               strSQL = strSQL & FieldName(CboField(0).Text) & CboOperate(0).Text & "'" & TxtContent(0).Text & "'"
          End If
    Else
         If Not TxtIsEmpty(CboRelation(0).Text) And TxtIsEmpty(CboRelation(1).Text) Then
              '如果两个条件组合
              strSQL = strSQL & FieldName(CboField(0).Text) & CboOperate(0).Text & "'" & TxtContent(0).Text & "'"
              Blnempty = Blnempty Or TxtIsEmpty(CboField(1).Text) Or TxtIsEmpty(CboOperate(1).Text) Or TxtIsEmpty(TxtContent(1).Text)
              '确定所有查询条件填写完整
              If Blnempty Then
                   MsgBox "请将查询条件填写完整", 48, "警告"
                   Exit Sub
              Else
                  '判断是哪种组合
                  Select Case CboRelation(0).Text
                       Case "或"
                          strSQL = strSQL & "or " & FieldName(CboField(1).Text) & CboOperate(1).Text & "'" & TxtContent(1).Text & "'"
                           
                       Case "与"
                           strSQL = strSQL & " and " & FieldName(CboField(1).Text) & CboOperate(1).Text & "'" & TxtContent(1).Text & "'"
                  End Select
              End If
          Else
              If Not TxtIsEmpty(CboRelation(0).Text) And Not TxtIsEmpty(CboRelation(1).Text) Then
                  Blnempty = Blnempty Or TxtIsEmpty(CboField(2).Text) Or TxtIsEmpty(CboOperate(2).Text) Or TxtIsEmpty(TxtContent(2).Text)
                '如果是三条件组合
                  If Blnempty Then
                       MsgBox "请将查询条件填写完整", 48, "警告"
                       Exit Sub
                   Else
               
               '判断组合条件
                     Select Case CboRelation(0).Text
                         Case "或"
                              strSQL = strSQL & "or " & FieldName(CboField(1).Text) & CboOperate(1).Text & "'" & TxtContent(1).Text & "'"
                              If CboRelation(1).Text = "或" Then
                                  strSQL = strSQL & "or" & FieldName(CboField(2).Text) & CboOperate(2).Text & "'" & TxtContent(2).Text & "'"
                                  
                              Else
                                  strSQL = strSQL & "and" & FieldName(CboField(2).Text) & CboOperate(2).Text & "'" & TxtContent(2).Text & "'"
                             End If
                         Case "与"
                              strSQL = strSQL & "and" & FieldName(CboField(1).Text) & CboOperate(1).Text & "'" & TxtContent(1).Text & "'"
                              If CboRelation(1).Text = "或" Then
                                  strSQL = strSQL & "or" & FieldName(CboField(2).Text) & CboOperate(2).Text & "'" & TxtContent(2).Text & "'"
                                  
                              Else
                                  strSQL = strSQL & "and" & FieldName(CboField(2).Text) & CboOperate(2).Text & "'" & TxtContent    (2).Text & "'"
                              End If
                    End Select
                End If
           Else
               MsgBox "请依次填写查询条件", 48, "警告"
               Exit Sub
           End If
      End If
     
  End If