机房收费系统—组合查询

时间：2022-09-16 10:23:14

在敲机房的时候，组合查询这一块是一个难点，为什么难呢，说白了就是没有理清思路，理清楚思路之后，写代码的时候再认真一些就没有问题了。

机房收费系统—组合查询

机房收费系统—组合查询

1首先在加载窗体中 

<span style="font-size:24px;">Private Sub Form_Load()

    Combo1.AddItem "卡号"
    Combo1.AddItem "学号"
    Combo1.AddItem "姓名"
    Combo1.AddItem "性别"
    Combo1.AddItem "系别"
    Combo1.AddItem "年级"
    Combo1.AddItem "班级"

    Combo2.AddItem "卡号"
    Combo2.AddItem "学号"
    Combo2.AddItem "姓名"
    Combo2.AddItem "性别"
    Combo2.AddItem "系别"
    Combo2.AddItem "年级"
    Combo2.AddItem "班级"

    Combo3.AddItem "卡号"
    Combo3.AddItem "学号"
    Combo3.AddItem "姓名"
    Combo3.AddItem "性别"
    Combo3.AddItem "系别"
    Combo3.AddItem "年级"
    Combo3.AddItem "班级"

    Combo4.AddItem "="
    Combo4.AddItem "<"
    Combo4.AddItem ">"
    Combo4.AddItem "<>"

    Combo5.AddItem "="
    Combo5.AddItem "<"
    Combo5.AddItem ">"
    Combo5.AddItem "<>"

    Combo6.AddItem "="
    Combo6.AddItem "<"
    Combo6.AddItem ">"
    Combo6.AddItem "<>"

    Combo7.AddItem "与"
    Combo7.AddItem "或"

    Combo8.AddItem "与"
    Combo8.AddItem "或"

    Combo2.Enabled = False
    Combo3.Enabled = False
    Combo5.Enabled = False
    Combo6.Enabled = False
    Text2.Enabled = False
    Text3.Enabled = False
    Combo8.Enabled = False

End Sub</span>
2控件的显示 

<span style="font-size:24px;">Private Sub Combo7_click()
Combo2.Enabled = True
Combo5.Enabled = True
Text2.Enabled = True
Combo8.Enabled = True
End Sub

Private Sub Combo8_click()
Combo3.Enabled = True
Combo6.Enabled = True
Text3.Enabled = True

End Sub</span>
3把汉字转化为数据库中的字段 

<span style="font-size:24px;">Private Function FieldName(strFieldName As String) As String
'用来把汉字转变成SQL可识别的英文
    Select Case strFieldName
        Case "卡号"
            FieldName = "cardno"
        Case "学号"
            FieldName = "studentNo"
        Case "姓名"
            FieldName = "studentName"
        Case "性别"
            FieldName = "sex"
        Case "系别"
            FieldName = "department"
        Case "年级"
            FieldName = "grade"
        Case "班级"
            FieldName = "class"
        Case "或"
            FieldName = "or"
        Case "与"
            FieldName = "and"

    End Select          
End Function</span>
4定义一个函数 

<span style="font-size:24px;">Private Function ViewData(txtSQL, mrc As ADODB.Recordset)

    '定义函数，调用
    '显示列名
    With myflexgrid
        .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

    With myflexgrid
    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) = mrc.Fields(14)
        .TextMatrix(.Rows - 1, 11) = mrc.Fields(12)
        .TextMatrix(.Rows - 1, 12) = mrc.Fields(13)

        mrc.MoveNext
    Loop

    End With
    mrc.Close
End Function</span>
5查询 

<span style="font-size:24px;">Private Sub cmdinquire_Click()
    Dim msgText As String
    Dim txtSQL1 As String
    Dim txtSQL2 As String
    Dim txtSQL3 As String
    Dim mrc As ADODB.Recordset

    '判断输入是否从新查询
    If myflexgrid.Text <> "" Then
        myflexgrid.Clear
        myflexgrid.Rows = 1
    End If

   ' 判断查询条件是否为空

   If Not Testtxt(Combo1.Text) Or Not Testtxt(Combo4.Text) Or Not Testtxt(Text1.Text) Then
        MsgBox "请输入查询条件一", vbOKOnly + vbExclamation, "警告"
        Exit Sub
   End If

   '却定查询条件一不为空的条件下
    txtSQL1 = "select * from student_Info where " & FieldName(Combo1.Text) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'"

    '组合条件一是否为空

    If Combo7.Text = "" Then
        Set mrc = ExecuteSQL(txtSQL1, msgText)
        If mrc.EOF = True Then
            MsgBox "此条件没有查询结果", vbOKOnly + vbExclamation, "警告"
            Exit Sub
        Else
            Call ViewData(txtSQL1, mrc)
        End If
    Else
        If Not Testtxt(Combo2.Text) Or Not Testtxt(Combo5.Text) Or Not Testtxt(Text2.Text) Then
            MsgBox "请输入查询条件二", vbOKOnly + vbExclamation, "警告"
            Exit Sub
        End If

         txtSQL2 = txtSQL1 & FieldName(Combo7.Text) & " " & FieldName(Combo2.Text) & Trim(Combo5.Text) & "'" & Trim(Text2.Text) & "'"

'组合条件一不为空的条件下组合条件二的判断
    If Combo8.Text = "" Then
        Set mrc = ExecuteSQL(txtSQL2, msgText)
            If mrc.EOF = True Then
                MsgBox "此条件没有查询结果", vbOKOnly + vbExclamation, "警告"
                Exit Sub
            Else
                Call ViewData(txtSQL2, mrc)
            End If
    Else
        If Not Testtxt(Combo3.Text) Or Not Testtxt(Combo6.Text) Or Not Testtxt(Text3.Text) Then
            MsgBox "请输入查询条件三", vbOKOnly + vbExclamation, "警告"
            Exit Sub
        End If

        txtSQL3 = txtSQL2 & FieldName(Combo8.Text) & " " & FieldName(Combo3.Text) & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"

         Set mrc = ExecuteSQL(txtSQL3, msgText)
         If mrc.EOF = True Then
            MsgBox "此条件没有查询结果", vbOKOnly + vbExclamation, "警告"
            Exit Sub
        Else
            Call ViewData(txtSQL3, mrc)
        End If
    End If
End If

End Sub</span>
6修改 

<span style="font-size:24px;">Private Sub cmdmodify_Click()
    If myflexgrid.Row = 0 Then
        MsgBox "请选中学生", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If

    SetParent frmmodifyStudentinfo.hWnd, frmstudentbase.hWnd
    frmmodifyStudentinfo.Show
End Sub</span>
理清楚思路之后是不是感觉很简单呀。

标签：

相关文章