机房收费系统之组合查询

来源:互联网 发布:电吉他音响效果器软件 编辑:程序博客网 时间:2024/03/29 05:00
 

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

 

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

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

Public Function TxtIsEmpty(strTxt As String) As Boolean'判断是否为空    If strTxt = "" Then        TxtIsEmpty = True    Else        txtisempyt = False    End IfEnd 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 SelectEnd 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


 

原创粉丝点击