机房收费初级篇之组合查询

       机房收费系统中组合查询是机房收费相对来说比较复杂的一块,一开始做到组合查询的时候就有点晕,不知道代码该怎么写,记得是花了好几天的时间去整理思路,在这期间,各种想法都有,想着想着自己就把自己给绕进去了,总是把事情想复杂。机房收费中的组合查询主要有三个窗体:学生基本信息维护 、学生上机统计信息 和操作员工作记录,其实只要整理好思路,想明白了就好了,把其中的一个窗体做出来,其他的都会了。下面就以操作员工作记录为例来说说我的整个思路。 首先:你需要判断各个需要填写的信息是否为空,如果是就提示填写完整。 其次:如果选择了组合关系一定要填写第二行或者第三行信息。 之后就是查询了,这里需要注意的一点是,字段名和组合关系需要转换成数据库能够读懂的信息,需要用一个函数(例如: filedname)来转换。在这其中,用到了一个dtpicker控件,方便输入,提高效率。

        

        但是写好了不能运行,还老出错,经过一些探索,终于知道了原来是出一个很小的错误,就是少了个空格。可见小小的一个空格的力量不容小觑啊 下面的是我的代码展示(仅供参考):

        

Private Function FieldName(strFieldName As String) As String
'是汉字转化成数据库中的字段和关系
   Select Case strFieldName
        Case "教师"
            FieldName = "UserID"
        Case "注册日期"
            FieldName = "logindate"
        Case "注册时间"
            FieldName = "logintime"
        Case "注销日期"
            FieldName = "logoutdate"
        Case "注销时间"
            FieldName = "logouttime"
        Case "机器名"
            FieldName = "computer"
        Case "与"
            FieldName = "and"
        Case "或"
            FieldName = "or"
   End Select
End Function

Private Sub Combo1_Click(Index As Integer)
  Combo2(0).Clear
  Combo2(1).Clear     '清空,否则每选一次都会出现相同的信息
  Combo2(2).Clear
  Select Case Combo1(0).Text
  
    Case "教师"
        Text1(0).Visible = True
        Combo2(0).AddItem "="
        Combo2(0).AddItem "<>"
        Text1(0).Visible = True
'        dtpicker1.Enabled = False
        dtpicker1.Visible = False
    Case "注册日期"               '我在这里用了一个dtpicker控件,然后选择日期的时候自动出现,便于提高效率,选择其他字段的时候显示的是文本框
        Text1(0).Visible = True
        Combo2(0).AddItem ">"
        Combo2(0).AddItem "<"
        Combo2(0).AddItem "="
        Combo2(0).AddItem "<>"
        dtpicker1.Visible = True
'        dtpicker1.Enabled = True
'        Text1(0).Enabled = False
        Text1(0).Visible = False
        Text1(0).Text = dtpicker1.Value
    Case "注册时间"
        Text1(0).Visible = True

        Combo2(0).AddItem ">"
        Combo2(0).AddItem "<"
        Combo2(0).AddItem "="
        Combo2(0).AddItem "<>"
        Text1(0).Visible = True
'        dtpicker1.Enabled = False
        dtpicker1.Visible = False
    Case "注销日期"
        Text1(0).Visible = True
        Combo2(0).AddItem ">"
        Combo2(0).AddItem "<"
        Combo2(0).AddItem "="
        Combo2(0).AddItem "<>"
        dtpicker1.Enabled = True
        Text1(0).Visible = False
        
        Text1(0).Text = dtpicker1.Value
    Case "注销时间"
        Text1(0).Visible = True

        Combo2(0).AddItem ">"
        Combo2(0).AddItem "<"
        Combo2(0).AddItem "="
        Combo2(0).AddItem "<>"
        Text1(0).Visible = True
'        dtpicker1.Enabled = False
        dtpicker1.Visible = False
    Case "机器名"
        Text1(0).Visible = True
'        dtpicker1.Enabled = False
        dtpicker1.Visible = False
        Combo2(0).AddItem "="
        Combo2(0).AddItem "<>"
    
  End Select
    Select Case Combo1(1).Text
  
    Case "教师"
        Text1(1).Visible = True
        Combo2(1).AddItem "="
        Combo2(1).AddItem "<>"
        Text1(1).Visible = True
'        DTPicker2.Enabled = False
        DTPicker2.Visible = False
'        blnShuZi = False
    Case "注册日期"
        Text1(1).Visible = True
        Combo2(1).AddItem ">"
        Combo2(1).AddItem "<"
        Combo2(1).AddItem "="
        Combo2(1).AddItem "<>"
        DTPicker2.Visible = True
        Text1(1).Text = DTPicker2.Value
        Text1(1).Visible = False
    Case "注册时间"
        Text1(1).Visible = True
   
        Combo2(1).AddItem ">"
        Combo2(1).AddItem "<"
        Combo2(1).AddItem "="
        Combo2(1).AddItem "<>"
        Text1(1).Visible = True
'        DTPicker2.Enabled = False
        DTPicker2.Visible = False
    Case "注销日期"
        Text1(1).Visible = True
        Combo2(1).AddItem ">"
        Combo2(1).AddItem "<"
        Combo2(1).AddItem "="
        Combo2(1).AddItem "<>"
        DTPicker2.Visible = True
        Text1(1).Visible = False
        Text1(1).Text = DTPicker2.Value
    Case "注销时间"
        Text1(1).Visible = True

        Combo2(1).AddItem ">"
        Combo2(1).AddItem "<"
        Combo2(1).AddItem "="
        Combo2(1).AddItem "<>"
        Text1(1).Visible = True
'        DTPicker2.Enabled = False
        DTPicker2.Visible = False
    Case "机器名"
        Text1(1).Visible = True

        Combo2(1).AddItem "="
        Combo2(1).AddItem "<>"
        Text1(1).Visible = True
'        DTPicker2.Enabled = False
        DTPicker2.Visible = False
  End Select
    Select Case Combo1(2).Text
  
    Case "教师"
        Text1(2).Visible = True
        Combo2(2).AddItem "="
        Combo2(2).AddItem "<>"
        Text1(2).Visible = True
'        DTPicker3.Visible = False
        DTPicker3.Visible = False
'        blnShuZi = False
    Case "注册日期"
'        Text1(2).Visible = True
        Combo2(2).AddItem ">"
        Combo2(2).AddItem "<"
        Combo2(2).AddItem "="
        Combo2(2).AddItem "<>"
        DTPicker3.Visible = True
        Text1(2).Visible = False
        Text1(2).Text = DTPicker3.Value
    Case "注册时间"
'        Text1(2).Visible = True
   
        Combo2(2).AddItem ">"
        Combo2(2).AddItem "<"
        Combo2(2).AddItem "="
        Combo2(2).AddItem "<>"
        Text1(2).Visible = True
        DTPicker3.Visible = False
    Case "注销日期"
'        Text1(2).Visible = True
        Combo2(2).AddItem ">"
        Combo2(2).AddItem "<"
        Combo2(2).AddItem "="
        Combo2(2).AddItem "<>"
        DTPicker3.Visible = True
        Text1(2).Visible = False
        Text1(2).Text = DTPicker3.Value
    Case "注销时间"
      
        Combo2(2).AddItem ">"
        Combo2(2).AddItem "<"
        Combo2(2).AddItem "="
        Combo2(2).AddItem "<>"
        Text1(2).Visible = True
        DTPicker3.Visible = False
    Case "机器名"
'        Text1(2).Visible = True

        Combo2(2).AddItem "="
        Combo2(2).AddItem "<>"
        Text1(2).Visible = True
        DTPicker3.Visible = False
  End Select
End Sub


Private Sub command1_Click()  '清空所有信息
  Combo1(0).Text = ""
  Combo1(1).Text = ""
  Combo1(2).Text = ""
  Combo2(0).Text = ""
  Combo2(1).Text = ""
  Combo2(2).Text = ""
  Combo3(0).Text = ""
  Combo3(1).Text = ""
  Text1(0).Text = ""
  Text1(1).Text = ""
  Text1(2).Text = ""
  MSFlexGrid1.Clear
End Sub

Private Sub command2_Click()   '这个是查询

  Dim txtsql As String
  Dim mrc As ADODB.Recordset
  Dim msgtext As String
  Dim a
    txtsql = "select * from worklog_info where "
    Set mrc = ExecuteSQL(txtsql, msgtext)
  '编辑第一行
    If Combo1(0).Text = "" Then
      MsgBox "请输入第一行字段名", vbOKOnly, "提示"
      Combo1(0).SetFocus
      Exit Sub
    ElseIf Combo2(0).Text = "" Then
      MsgBox "请输入第一行操作符", vbOKOnly, "提示"
      Combo2(0).SetFocus
      Exit Sub
    ElseIf Text1(0).Text = "" And dtpicker1.Value = "" Then
      MsgBox "请输入第一行要查找的内容", vbOKOnly, "提示"
      Exit Sub
    Else
      '从数据库中选择符合条件的内容
      txtsql = txtsql & FieldName(Combo1(0).Text) & " " & Combo2(0).Text & "'" & Trim(Text1(0).Text) & "'"
           Set mrc = ExecuteSQL(txtsql, msgtext)
             If mrc.EOF Then  '判断是否为空
                MsgBox "没有记录,请重新选择!", vbOKOnly, "提示"
                Combo1(0).SetFocus
                Exit Sub
             Else
               If Combo3(0).Text = "" Then
                 GoTo case1
               End If
             End If
             
'End If
    End If

    

    If Combo1(1).Text = "" Then
      MsgBox "请输入第二行字段名", vbOKOnly, "提示"
      Combo1(1).SetFocus
      Exit Sub
    End If
    If Combo2(1).Text = "" Then
      MsgBox "请选择第二行操作符~", vbOKOnly, "提示"
      Combo2(1).SetFocus
      Exit Sub
    End If
    If Text1(1).Text = "" Then
      MsgBox "请输入第二行要查找的内容^^", vbOKOnly, "提示"
      Text1(1).SetFocus
      Exit Sub
    End If
    If Combo1(1).Text <> "" And Combo2(1).Text <> "" And Text1(1).Text <> "" And Combo2(0).Text = "" Then
       MsgBox "请选择组合关系", vbOKOnly, "提示"
    Else
       
'    txtSQL = "select * from worklog_info where status='False'" & "And" & " " & FieldName(Combo1(0).Text) & Combo2(0).Text & Trim(Text1(0).Text) & "(" & a & " " & Trim(Combo1(1).Text) & Combo2(1).Text & "'" & Text1(1).Text & "'" & ")"
    txtsql = txtsql & FieldName(Combo3(0).Text) & " " & FieldName(Combo1(1).Text) & Combo2(1).Text & "'" & Text1(1).Text & "'"
    Set mrc = ExecuteSQL(txtsql, msgtext)
    If mrc.EOF Then
      MsgBox "没有记录哦~", vbOKOnly, "提示"
      Combo1(1).SetFocus
      Exit Sub
    Else
      If Combo3(1).Text = "" Then
        GoTo case1
      End If
    End If
    End If
    '第三行
    If Combo1(2).Text = "" Then
      MsgBox "请选择第三行字段名~", vbOKOnly, "提示"
      Combo1(2).SetFocus
      Exit Sub
    End If
    If Combo2(2).Text = "" Then
      MsgBox "请选择第三行"
      Combo2(2).SetFocus
      Exit Sub
    End If
    If Text1(2).Text = "" Then
      MsgBox "请输入第三行要查询的内容", vbOKOnly, "提示"
      Text1(2).SetFocus
      Exit Sub
    End If
'    txtsql = "select * from worklog_info where status='" & "False" & "'" _
'            & "and" & " " & " " & FieldName(Combo1(0).Text) & " " & Combo2(0).Text & "'" & Text1(0).Text & "'" _
'            & a & " " & FieldName(Combo1(1).Text) & " " & Combo2(1).Text & "'" & Text1(1).Text & "'" _
'            & a & " " & FieldName(Combo1(2).Text) & " " & Combo2(2).Text & "'" & Text1(2).Text & "'" & ")"
    txtsql = txtsql & FieldName(Combo3(1).Text) & " " & FieldName(Combo1(2).Text) & Combo2(2).Text & "'" & Text1(2).Text & "'"
    Set mrc = ExecuteSQL(txtsql, msgtext)
    If mrc.EOF Then
            MsgBox "当前没有该内容", vbOKOnly, "提示"
            Exit Sub
    Else
            GoTo case1
    End If


case1:    '定义case1,之后用到了就调用case1就好,避免代码重复,
    With MSFlexGrid1
        .Rows = 1
        .CellAlignment = 4
'        .ColAlignment = 4
        .TextMatrix(0, 0) = "序列号"
        .TextMatrix(0, 1) = "教师"
        .TextMatrix(0, 2) = "级别"
        .TextMatrix(0, 3) = "注册日期"
        .TextMatrix(0, 4) = "注册时间"
        .TextMatrix(0, 5) = "注销日期"
        .TextMatrix(0, 6) = "注销时间"
        .TextMatrix(0, 7) = "机器名"
        .TextMatrix(0, 8) = "状态"
        Do While Not mrc.EOF
           With MSFlexGrid1
            .Rows = .Rows + 1
            .CellAlignment = 4
'            .ColAlignment = 4
            .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))
            .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(1))
            .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(2))
            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(3))
            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))
            .TextMatrix(.Rows - 1, 5) = "" & mrc.Fields(5).Value
            .TextMatrix(.Rows - 1, 6) = "" & Trim(mrc.Fields(6))
            .TextMatrix(.Rows - 1, 7) = "" & Trim(mrc.Fields(7))
'            .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(8))
'            If Trim(mrc.Fields(i, j)) = Null Then
'               TextMatrix(.Rows - 1, j) = ""
'            End If
           End With
            mrc.MoveNext
        Loop
    End With
End Sub

Private Sub Command3_Click()   '这个是导出excel
    
    Dim i As Integer
    Dim j As Integer
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.add
    Set xlSheet = xlBook.Worksheets(1)
    For i = 0 To MSFlexGrid1.Rows - 1
        For j = 0 To MSFlexGrid1.Cols - 1
            MSFlexGrid1.Row = i
            MSFlexGrid1.Col = j
            xlSheet.Cells(i + 1, j + 1) = Trim(MSFlexGrid1.Text)
        Next
    Next
End Sub
Private Sub Command4_Click()   '退出
 Unload Me
End Sub


Private Sub Form_Load()   '加载字段名,操作符和组合关系
  Combo1(0).AddItem "教师"
  Combo1(0).AddItem "注册日期"
  Combo1(0).AddItem "注册时间"
  Combo1(0).AddItem "注销日期"
  Combo1(0).AddItem "注销时间"
  Combo1(0).AddItem "机器名"

  Combo2(0).AddItem "="
  Combo2(0).AddItem "<"
  Combo2(0).AddItem ">"
  Combo2(0).AddItem "<>"

  Combo3(0).AddItem "或"
  Combo3(0).AddItem "与"

  Combo1(1).AddItem "教师"
  Combo1(1).AddItem "注册日期"
  Combo1(1).AddItem "注册时间"
  Combo1(1).AddItem "注销日期"
  Combo1(1).AddItem "注销时间"
  Combo1(1).AddItem "机器名"

  Combo2(1).AddItem "="
  Combo2(1).AddItem "<"
  Combo2(1).AddItem ">"
  Combo2(1).AddItem "<>"

'  Combo3(1).AddItem ""
  Combo3(1).AddItem "或"
  Combo3(1).AddItem "与"

  Combo1(2).AddItem "教师"
  Combo1(2).AddItem "注册日期"
  Combo1(2).AddItem "注册时间"
  Combo1(2).AddItem "注销日期"
  Combo1(2).AddItem "注销时间"
  Combo1(2).AddItem "机器名"

  Combo2(2).AddItem "="
  Combo2(2).AddItem "<"
  Combo2(2).AddItem ">"
  Combo2(2).AddItem "<>"


End Sub

评论 48
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值