【机房收费系统】组合查询

曾经终败给现在 2022-08-04 04:09 170阅读 0赞

机房组合查询我感觉是在这上面时间最多的一个，不是太难但就是有很多的不容易让人想到的地方。下面我就来总结一下。

**     1、查询条件strA单独查询，组合关系comboZh1符合的时候strA和StrB一起查询，组合关系comboZh2符合的时候strA、StrB、StrC三行条件一起查询。**

**     2、当字段名是性别的时候操作符中的符号只能是“=”和“< >”并且提示只能输入“男” 或“女”。当字段名为时间或者是日期的时候，txtYc1要有限制只能输入日期或者是时间**

**3、组合查询ComboZh1为空的时候StrB不能用或者是隐藏。只有单击ComboZh1的时候ComboZh2才能用。否则无效。（这个想着很是费劲，一定要好好的思考。。。）**

**![Center][]**

接下来就是展示一下我的代码。

先应该在新建的模块中写到（这会为你省掉很多的麻烦。。。）

<span style="font-size:14px;">Option Explicit
    
    Public Function FiledName(strFiledName As String) As String
    '将字段名转化成数据库表中的字段名
        Select Case strFiledName
            Case "卡号"
                FiledName = "cardno"
            Case "学号"
                FiledName = "studentNo"
            Case "姓名"
                FiledName = "studentName"
            Case "性别"
                FiledName = "sex"
            Case "系别"
                FiledName = "department"
            Case "年级"
                FiledName = "grade"
            Case "班级"
                FiledName = "class"
            
        End Select
    End Function
    
    '将操作符转化成数据库表中的字段名
    Public Function Operate(strOperate As String)
       Select Case strOperate
          Case "="
             Operate = "="
          Case "<"
             Operate = "<"
          Case ">"
             Operate = ">"
          Case "<>"
             Operate = "<>"
       End Select
    End Function
    
    '将连接符转化成数据库表中的字段名
    Public Function Connect(strConnect As String) As String
        Select Case strConnect
           Case "或"
              Connect = "or"
           Case "与"
              Connect = "and"
           Case ""
              Connect = ""
        End Select
    End Function
    
    Function chkkey(t As String, k As Integer) As Integer   '只能输入数字
        chkkey = k
        If k = 46 And InStr(t, ".") = 0 Then
            Exit Function
        End If
        If k = 8 Then
            Exit Function
        End If
        If k < 48 Or k > 57 Then
            chkkey = 0
        End If
        
        
    End Function</span>

<span style="font-size:14px; font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"><span style="white-space:pre">	<span style="font-size:14px; white-space: pre;">之后</span></span>就是要在窗体中写你的代码了。</span>

<span style="font-size:14px;">Option Explicit
    Dim nowrow As Integer
    
    Private Sub ComboZd1_Click()
    '选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
    ''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
    Select Case ComboZd1.Text
           Case "性别"
                ComboOpt1.Clear
                ComboOpt1.AddItem "="
                ComboOpt1.AddItem "<>"
           Case "姓名"
                ComboOpt1.Clear
                ComboOpt1.AddItem "="
                ComboOpt1.AddItem "<>"
            Case "系别"
                ComboOpt1.Clear
                ComboOpt1.AddItem "="
                ComboOpt1.AddItem "<>"
            Case "卡号"
                ComboOpt1.Clear
                ComboOpt1.AddItem "="
                ComboOpt1.AddItem ">"
                ComboOpt1.AddItem "<"
                ComboOpt1.AddItem "<>"
            Case "学号"
                ComboOpt1.Clear
                ComboOpt1.AddItem "="
                ComboOpt1.AddItem ">"
                ComboOpt1.AddItem "<"
                ComboOpt1.AddItem "<>"
            Case "年级"
                ComboOpt1.Clear
                ComboOpt1.AddItem "="
                ComboOpt1.AddItem ">"
                ComboOpt1.AddItem "<"
                ComboOpt1.AddItem "<>"
            Case "班级"
                ComboOpt1.Clear
                ComboOpt1.AddItem "="
                ComboOpt1.AddItem ">"
                ComboOpt1.AddItem "<"
                ComboOpt1.AddItem "<>"
             Case ""
                ComboOpt1.Clear
                ComboOpt1.AddItem ""
    End Select
    '性别选择男女
    If ComboZd1.Text = "性别" Then
        MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
    End If
    End Sub
    Private Sub ComboZd2_Click()
    '选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
    ''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
    Select Case ComboZd2.Text
           Case "性别"
                ComboOpt2.Clear
                ComboOpt2.AddItem "="
                ComboOpt2.AddItem "<>"
           Case "姓名"
                ComboOpt2.Clear
                ComboOpt2.AddItem "="
                ComboOpt2.AddItem "<>"
            Case "系别"
                ComboOpt2.Clear
                ComboOpt2.AddItem "="
                ComboOpt2.AddItem "<>"
            Case "卡号"
                ComboOpt2.Clear
                ComboOpt2.AddItem "="
                ComboOpt2.AddItem ">"
                ComboOpt2.AddItem "<"
                ComboOpt2.AddItem "<>"
            Case "学号"
                ComboOpt2.Clear
                ComboOpt2.AddItem "="
                ComboOpt2.AddItem ">"
                ComboOpt2.AddItem "<"
                ComboOpt2.AddItem "<>"
            Case "年级"
                ComboOpt2.Clear
                ComboOpt2.AddItem "="
                ComboOpt2.AddItem ">"
                ComboOpt2.AddItem "<"
                ComboOpt2.AddItem "<>"
            Case "班级"
                ComboOpt2.Clear
                ComboOpt2.AddItem "="
                ComboOpt2.AddItem ">"
                ComboOpt2.AddItem "<"
                ComboOpt2.AddItem "<>"
            Case ""
                ComboOpt2.Clear
                ComboOpt2.AddItem ""
    End Select
    '性别选择男女
    If ComboZd2.Text = "性别" Then
        MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
    End If
    End Sub
    Private Sub ComboZd3_Click()
    '选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
    ''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
    Select Case ComboZd3.Text
           Case "性别"
                ComboOpt3.Clear
                ComboOpt3.AddItem "="
                ComboOpt3.AddItem "<>"
           Case "姓名"
                ComboOpt3.Clear
                ComboOpt3.AddItem "="
                ComboOpt3.AddItem "<>"
            Case "系别"
                ComboOpt3.Clear
                ComboOpt3.AddItem "="
                ComboOpt3.AddItem "<>"
            Case "卡号"
                ComboOpt3.Clear
                ComboOpt3.AddItem "="
                ComboOpt3.AddItem ">"
                ComboOpt3.AddItem "<"
                ComboOpt3.AddItem "<>"
            Case "学号"
                ComboOpt3.Clear
                ComboOpt3.AddItem "="
                ComboOpt3.AddItem ">"
                ComboOpt3.AddItem "<"
                ComboOpt3.AddItem "<>"
            Case "年级"
                ComboOpt3.Clear
                ComboOpt3.AddItem "="
                ComboOpt3.AddItem ">"
                ComboOpt3.AddItem "<"
                ComboOpt3.AddItem "<>"
            Case "班级"
                ComboOpt3.Clear
                ComboOpt3.AddItem "="
                ComboOpt3.AddItem ">"
                ComboOpt3.AddItem "<"
                ComboOpt3.AddItem "<>"
            Case ""
                ComboOpt3.Clear
                ComboOpt3.AddItem ""
    End Select
    '性别选择男女
    If ComboZd3.Text = "性别" Then
        MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
    End If
    End Sub
    Private Sub ComboZh1_Click()
    '组合关系是1个时候第2行的选择显示
    If ComboZh1.Text <> "" Then
       ComboZd2.Enabled = True
       ComboOpt2.Enabled = True
       txtYc2.Enabled = True
       ComboZh2.Enabled = True
    Else
        ComboZd2.Enabled = False
        ComboOpt2.Enabled = False
        txtYc2.Enabled = False
        ComboZh2.Enabled = False
        ComboZd3.Enabled = False
       ComboOpt3.Enabled = False
       txtYc3.Enabled = False
        txtYc2.Text = ""
        txtYc3.Text = ""
    End If
    
    
    End Sub
    
    
    Private Sub ComboZh2_Click()
    '组合关系是2个时候第3行的选择显示
    If ComboZh2.Text <> "" Then
       ComboZd3.Enabled = True
       ComboOpt3.Enabled = True
       txtYc3.Enabled = True
    End If
    End Sub
    te Sub Command1_Click()
    'Command1是清空
        ComboOpt1.Text = ""
        ComboOpt2.Text = ""
        ComboOpt3.Text = ""
        ComboZd1.Text = ""
        ComboZd2.Text = ""
        ComboZd3.Text = ""
        ComboZh1.Text = ""
        ComboZh2.Text = ""
        txtYc1.Text = ""
        txtYc2.Text = ""
        MSHFGd.Clear
       
    End Sub
    
    Private Sub Command2_Click()
    'Command2是查询
        Dim txtSQl As String
        Dim MsgText As String
        Dim mrc As ADODB.Recordset    'mrc是student数据库
        Dim strA As String   '定义三个保存SQL语句的字符串 代表第一种查询条件
        Dim strB As String  '代表第二种
        Dim strC As String  '代表第三种
        
        txtSQl = "select * from student_Info where "
     
        
        strA = txtSQl & FiledName(ComboZd1.Text) & Operate(ComboOpt1.Text) & "'" & Trim(txtYc1.Text) & "'"
        strB = strA & "" & Connect(ComboZh1.Text) & " " & FiledName(ComboZd2.Text) & Operate(ComboOpt2.Text) & "'" & Trim(txtYc2.Text) & "'"
        strC = strB & Connect(ComboZh2.Text) & " " & FiledName(ComboZd2.Text) & Operate(ComboOpt3.Text) & "'" & Trim(txtYc3.Text) & "'"
    
        If Trim(ComboZh1.Text) = "" Then   '没有组合关系的时候就是一行查询条件
            If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Then
                MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
                Exit Sub
            Else
                txtSQl = strA
            Set mrc = ExecuteSQL(txtSQl, MsgText)
            End If
        End If
        
        If Trim(ComboZh1.Text) <> "" Then  '有组合关系的时候是两行和起来的查询条件
            If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Or Trim(ComboZd2.Text) = "" Or Trim(ComboOpt2.Text) = "" Or Trim(txtYc2.Text) = "" Then
                MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
                Exit Sub
            Else
                txtSQl = strB
                Set mrc = ExecuteSQL(txtSQl, MsgText)
            End If
        End If
        
        If Trim(ComboZh2.Text) <> "" Then   '两个组合关系时候的查询条件 三行查询条件一起
            If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Or Trim(ComboZd2.Text) = "" Or Trim(ComboOpt2.Text) = "" Or Trim(txtYc2.Text) = "" Or Trim(ComboZd3.Text) = "" Or Trim(ComboOpt3.Text) = "" Or Trim(txtYc3.Text) = "" Then
                MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
                Exit Sub
            Else
                txtSQl = strC
                Set mrc = ExecuteSQL(txtSQl, MsgText)
            End If
        End If
        
    
        If mrc.EOF Then
            MSHFGd.Clear
            MsgBox "暂无查询结果", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        End If
        
        With MSHFGd     '表示的是设置MSHFlexGrid控件第i行，第j列的文本内容（注意标题算第一行）
            .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) = "状态"
            .TextMatrix(0, 9) = "备注"
            .TextMatrix(0, 10) = "类型"
            .TextMatrix(0, 11) = "日期"
            .TextMatrix(0, 12) = "时间"
        
        
        Do While Not mrc.EOF
            .Rows = .Rows + 1   '防止空白行的出现
            .CellAlignment = 4
            .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))   '与数据库进行对应
            .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(2))
            .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(0))
            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))
            .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5))
            .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6))
            .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(3))
            .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(10))
            .TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(8))
            .TextMatrix(.Rows - 1, 10) = Trim(mrc.Fields(14))
            .TextMatrix(.Rows - 1, 11) = mrc.Fields(12) & ""
            .TextMatrix(.Rows - 1, 12) = mrc.Fields(13) & ""
            mrc.MoveNext
            Loop
            End With
            AdjustColWidth frmcxsjbxxwh, MSHFGd  '调节列宽
            mrc.Close
        
    End Sub
    
    Private Sub Command3_Click()
    'Command3_是退出
      Unload Me
    End Sub
    
    Private Sub Command4_Click()
    '修改信息
    If MSHFGd.MouseRow = 0 Then
        MsgBox "请选择学生", vbOKOnly + vbExclamation, "警告"
    Else
        frmcxsjbxxwh.Hide
        SetParent frmcModifySI.hWnd, frmmain.Picture1.hWnd   '‘显示修改学生信息窗体
        frmcModifySI.Width = 12180
        frmcModifySI.Height = 9255
     End If
    End Sub
    
    
    Private Sub Form_Activate()
    '把当前不是活动的窗体最小化
    Dim a As Form
    For Each a In Forms
        If a.Name <> frmmain.Name And a.Name <> Me.Name Then
            a.WindowState = 1
        End If
    Next
        
    End Sub
    Private Sub Form_Load()
    
        With MSHFGd      '表示的是设置MSHFlexGrid控件第i行，第j列的文本内容（注意标题算第一行）
            .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) = "状态"
            .TextMatrix(0, 9) = "备注"
            .TextMatrix(0, 10) = "类型"
            .TextMatrix(0, 11) = "日期"
            .TextMatrix(0, 12) = "时间"
            End With
        
    
        ComboZd1.AddItem "学号"     '添加字段名
        ComboZd1.AddItem "姓名"
        ComboZd1.AddItem "卡号"
        ComboZd1.AddItem "系别"
        ComboZd1.AddItem "年级"
        ComboZd1.AddItem "班级"
        ComboZd1.AddItem "性别"
        ComboZd1.AddItem ""
    
        ComboZd2.AddItem "学号"
        ComboZd2.AddItem "姓名"
        ComboZd2.AddItem "卡号"
        ComboZd2.AddItem "系别"
        ComboZd2.AddItem "年级"
        ComboZd2.AddItem "班级"
        ComboZd2.AddItem "性别"
          ComboZd2.AddItem ""
          
        ComboZd3.AddItem "学号"
        ComboZd3.AddItem "姓名"
        ComboZd3.AddItem "卡号"
        ComboZd3.AddItem "系别"
        ComboZd3.AddItem "年级"
        ComboZd3.AddItem "班级"
        ComboZd3.AddItem "性别"
          ComboZd3.AddItem ""
    
         
        ComboZh1.AddItem "或"
        ComboZh1.AddItem "与"
        ComboZh1.AddItem ""
        
        ComboZh2.AddItem "或"       '添加组合关系
        ComboZh2.AddItem "与"
        ComboZh2.AddItem ""
        
        ComboZd2.Enabled = False
        ComboZd3.Enabled = False
        ComboOpt2.Enabled = False
        ComboOpt3.Enabled = False
        txtYc2.Enabled = False
        txtYc3.Enabled = False
        ComboZh2.Enabled = False
    End Sub
    
    Public Sub MSHFgd_Mousedown(button As Integer, Shift As Integer, X As Single, Y As Single)
        With MSHFGd
        .Row = .MouseRow  '当前活动单元就是鼠标按下的单元
        nowrow = .Row   '把单元传给nowrow
        End With
    End Sub
    Public Sub MSHFgd_mouseup(button As Integer, Shift As Integer, X As Single, Y As Single)
        With MSHFGd
            .RowSel = nowrow
        End With
        
    End Sub
    
    
    
    Private Sub txtYc1_Click()
       txtYc1.SetFocus
    End Sub
    
    
    Private Sub txtYc2_Click()
       txtYc2.SetFocus
    End Sub
    
    
    Private Sub txtYc3_Click()
       txtYc3.SetFocus
    End Sub
    
     
    Private Sub txtYc1_KeyPress(KeyAscii As Integer)
    '确定输入的是数字
    If (ComboZd1.Text = "卡号") Then
        KeyAscii = chkkey(txtYc1, KeyAscii)
    Else
        If (ComboZd1.Text = "学号") Then
        KeyAscii = chkkey(txtYc1, KeyAscii)
    End If
    End If
    
    End Sub
     
    Private Sub txtYc2_KeyPress(KeyAscii As Integer)
    '确定输入的是数字
    If (ComboZd2.Text = "卡号") Then
        KeyAscii = chkkey(txtYc2, KeyAscii)
    Else
        If (ComboZd2.Text = "学号") Then
        KeyAscii = chkkey(txtYc2, KeyAscii)
    End If
    End If
    End Sub
     
    Private Sub txtYc3_KeyPress(KeyAscii As Integer)
    '确定输入的是数字
    If (ComboZd3.Text = "卡号") Then
        KeyAscii = chkkey(txtYc3, KeyAscii)
    Else
        If (ComboZd3.Text = "学号") Then
        KeyAscii = chkkey(txtYc3, KeyAscii)
    End If
    End If
    
    End Sub
    
    </span>

好了这就是我的代码了。虽然过程很是艰辛但是结果还是很美好的。

[Center]: /images/20220731/8381c031e76d4028b2bc5ee2696fb33a.png