组合查询--机房收费系统

来源：互联网发布：24和26最小公倍数算法编辑：程序博客网时间：2024/05/02 04:41

机房收费系统用到的组合查询是比较多的，一般用户中的出意外是查看上机状态、操作员中的查询工作记录、查询学生上机统计信息、学生基本信息维护。

下面附上查询学生上机统计信息的代码：

Private Sub cmdquery_Click()    Dim mrc As ADODB.Recordset    Dim txtsql As String    Dim msgtext As String           txtsql = "select * from Line_Info where "        '判断字段的选择是否为空    If Not testtxt(Combfield1.Text) Then        MsgBox "请选择字段！", vbOKOnly + vbExclamation, "警告"        Combfield1.SetFocus        Exit Sub    End If    '判断操作符的选择是否为空    If Not testtxt(Comboperator1.Text) Then        MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"        Comboperator1.SetFocus        Exit Sub    End If    '判断要查询的内容是否为空    If Not testtxt(txtcontent1.Text) Then        MsgBox "请在输入要查询的内容", vbOKOnly + vbExclamation, "警告"        txtcontent1.SetFocus        Exit Sub    End If

    '利用模版函数查看是否是组合查询第一行为空时，查询无效    txtsql = txtsql & FieldName(Combfield1.Text) & "" & Comboperator1.Text & "'" & txtcontent1.Text & "'"            '开始组合查询            If Trim(Combrelation1.Text <> "") Then        '选择了第一个组合关系后，第二行不能为空            If Trim(Combfield2.Text) = "" Or Trim(Comboperator2.Text) = "" Or Trim(txtcontent2.Text) = "" Then            MsgBox "您选择了组合关系，请输入数据之后再查询", vbExclamation + vbOKOnly, "提示信息"            Exit Sub        Else                  txtsql = txtsql & FieldName(Combrelation1.Text) & " " & "" & FieldName(Combfield2.Text) & Comboperator2.Text & "'" & Trim(txtcontent2.Text) & "'"        End If           End If            If Trim(Combrelation2.Text) <> "" Then        '选择了第二个组合关系后，第三行不能为空            If Trim(Combfield3.Text) = "" Or Trim(Comboperator3.Text) = "" Or Trim(txtcontent3.Text) = "" Then            MsgBox "您选择了第二个组合，请输入数据之后再查询", vbOKOnly, "提示"            Exit Sub        Else            txtsql = txtsql & FieldName(Combrelation2.Text) & " " & "" & FieldName(Combfield3.Text) & Comboperator3.Text & "'" & Trim(txtcontent3.Text) & "'"        End If    End If     '开始进行查找    Set mrc = executesql(txtsql, msgtext)    If mrc.RecordCount = 0 Then           MsgBox "没有您要查找的学生上机记录！", vbOKOnly + vbCritical, "查询提示"        Combfield1.SetFocus       MSFlexGrid1.Rows = 1        '存在上机记录查询成功，填充到表格    Else        With MSFlexGrid1            .Rows = 1            .CellAlignment = 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                            .Rows = .Rows + 1                .TextMatrix(.Rows - 1, 0) = mrc.Fields(1)                .TextMatrix(.Rows - 1, 1) = mrc.Fields(2)                .TextMatrix(.Rows - 1, 2) = mrc.Fields(6)                .TextMatrix(.Rows - 1, 3) = mrc.Fields(7)                .TextMatrix(.Rows - 1, 4) = mrc.Fields(8) & ""                .TextMatrix(.Rows - 1, 5) = mrc.Fields(9) & ""                 .TextMatrix(.Rows - 1, 6) = mrc.Fields(11) & ""                .TextMatrix(.Rows - 1, 7) = mrc.Fields(12) & ""                                .TextMatrix(.Rows - 1, 8) = mrc.Fields(13) & ""                mrc.MoveNext            Loop        End With             End IfEnd Sub

 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 = "offtime"        Case "下机日期"            FieldName = "offdate"        Case "消费金额"            FieldName = "consume"        Case "余额"            FieldName = "cash"        Case "备注"            FieldName = "status"        Case "与"            FieldName = "and"        Case "或"            FieldName = "or"                End SelectEnd Function

师傅验收的时候提出了一个比较人性化的观点：就是当用户输入比如上机时间和日期时给它附上一个日期控件，当输入其他东西时日期控件不可用，下面是代码举例：

Private Sub Combfield3_Click() If Combfield3.Text = "上机时间" Or Combfield3.Text = "下机时间" Then        DTPicker3.Visible = True                DTPicker3.Format = dtpTime        txtcontent3.Text = DTPicker3.Value    End If           If Combfield3.Text = "上机日期" Or Combfield3.Text = "下机日期" Then         DTPicker3.Visible = True                DTPicker3.Format = dtpShortDate        txtcontent3.Text = DTPicker3.Value    End If If Combfield3.Text <> "上机时间" And Combfield3.Text <> "下机时间" And Combfield3.Text <> "上机日期" And Combfield3 <> "下机日期" Then        DTPicker3.Visible = False         End IfEnd Sub

总结：设计系统时一定要本着为用户考虑，尽量让用户一目了然。

这样我们的组合查询也就差不多了。

0 0