frmquery.vb

教务管理系统：学生成绩管理

Public Class FrmQuery
    Inherits System.Windows.Forms.Form

#Region " Windows 窗体设计器生成的代码 "

    Public Sub New()
        MyBase.New()

        '该调用是 Windows 窗体设计器所必需的。
        InitializeComponent()

        '在 InitializeComponent() 调用之后添加任何初始化

    End Sub

    '窗体重写 dispose 以清理组件列表。
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Windows 窗体设计器所必需的
    Private components As System.ComponentModel.IContainer

    '注意: 以下过程是 Windows 窗体设计器所必需的
    '可以使用 Windows 窗体设计器修改此过程。
    '不要使用代码编辑器修改它。
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents CheckClass As System.Windows.Forms.CheckBox
    Friend WithEvents CheckCourse As System.Windows.Forms.CheckBox
    Friend WithEvents BtnQuery As System.Windows.Forms.Button
    Friend WithEvents BtnExit As System.Windows.Forms.Button
    Friend WithEvents CmbClass As System.Windows.Forms.ComboBox
    Friend WithEvents CmbCourse As System.Windows.Forms.ComboBox
    Friend WithEvents CmbStudent As System.Windows.Forms.ComboBox
    Friend WithEvents CheckStudent As System.Windows.Forms.CheckBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.CheckClass = New System.Windows.Forms.CheckBox
        Me.CheckCourse = New System.Windows.Forms.CheckBox
        Me.BtnQuery = New System.Windows.Forms.Button
        Me.BtnExit = New System.Windows.Forms.Button
        Me.CmbClass = New System.Windows.Forms.ComboBox
        Me.CmbCourse = New System.Windows.Forms.ComboBox
        Me.CmbStudent = New System.Windows.Forms.ComboBox
        Me.CheckStudent = New System.Windows.Forms.CheckBox
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(8, 0)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(440, 248)
        Me.DataGrid1.TabIndex = 0
        '
        'CheckClass
        '
        Me.CheckClass.Location = New System.Drawing.Point(40, 264)
        Me.CheckClass.Name = "CheckClass"
        Me.CheckClass.TabIndex = 1
        Me.CheckClass.Text = "按班级查询："
        '
        'CheckCourse
        '
        Me.CheckCourse.Location = New System.Drawing.Point(40, 288)
        Me.CheckCourse.Name = "CheckCourse"
        Me.CheckCourse.TabIndex = 2
        Me.CheckCourse.Text = "按课程查询："
        '
        'BtnQuery
        '
        Me.BtnQuery.Location = New System.Drawing.Point(344, 264)
        Me.BtnQuery.Name = "BtnQuery"
        Me.BtnQuery.Size = New System.Drawing.Size(72, 24)
        Me.BtnQuery.TabIndex = 3
        Me.BtnQuery.Text = "查询"
        '
        'BtnExit
        '
        Me.BtnExit.Location = New System.Drawing.Point(344, 312)
        Me.BtnExit.Name = "BtnExit"
        Me.BtnExit.Size = New System.Drawing.Size(72, 24)
        Me.BtnExit.TabIndex = 4
        Me.BtnExit.Text = "退出"
        '
        'CmbClass
        '
        Me.CmbClass.Location = New System.Drawing.Point(144, 264)
        Me.CmbClass.Name = "CmbClass"
        Me.CmbClass.Size = New System.Drawing.Size(112, 20)
        Me.CmbClass.TabIndex = 5
        '
        'CmbCourse
        '
        Me.CmbCourse.Location = New System.Drawing.Point(144, 288)
        Me.CmbCourse.Name = "CmbCourse"
        Me.CmbCourse.Size = New System.Drawing.Size(112, 20)
        Me.CmbCourse.TabIndex = 6
        '
        'CmbStudent
        '
        Me.CmbStudent.Location = New System.Drawing.Point(144, 312)
        Me.CmbStudent.Name = "CmbStudent"
        Me.CmbStudent.Size = New System.Drawing.Size(112, 20)
        Me.CmbStudent.TabIndex = 7
        '
        'CheckStudent
        '
        Me.CheckStudent.Location = New System.Drawing.Point(40, 312)
        Me.CheckStudent.Name = "CheckStudent"
        Me.CheckStudent.TabIndex = 8
        Me.CheckStudent.Text = "按学号查询："
        '
        'FrmQuery
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(456, 349)
        Me.Controls.Add(Me.CheckStudent)
        Me.Controls.Add(Me.CmbStudent)
        Me.Controls.Add(Me.CmbCourse)
        Me.Controls.Add(Me.CmbClass)
        Me.Controls.Add(Me.BtnExit)
        Me.Controls.Add(Me.BtnQuery)
        Me.Controls.Add(Me.CheckCourse)
        Me.Controls.Add(Me.CheckClass)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "FrmQuery"
        Me.Text = "成绩查询"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub FrmQuery_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        addCmbClass()
        addCmbCourse()
    End Sub

    Private Sub addCmbClass()
        Dim str1 As String
        Dim str2 As String
        Dim str3 As String
        Dim str4 As String
        Try
            Dim sql As String = "SELECT A.CLASSID, A.CLASSYEAR,B.DEPARTMENT,A.CLASSNUMBER FROM CLASSINFO A,DEPARTMENTINFO B WHERE A.DEPARTMENTID=B.DEPARTMENTID"
            Dim dt As DataTable = GetDataTable(sql)
            For i As Integer = 0 To dt.Rows.Count - 1
                str1 = dt.Rows(i)("classyear")
                str2 = dt.Rows(i)("department")
                str3 = dt.Rows(i)("classnumber")
                str4 = dt.Rows(i)("classid")

                Me.CmbClass.Items.Add(str4 + "|" + str1 + "级" + str2 + str3)
            Next
        Catch ex As Exception
            WriteErr(ex)
        End Try

    End Sub
    Private Sub addCmbCourse()
        Try
            Dim sql As String = "SELECT COURSENAME  FROM COURSEINFO"
            Dim dt As DataTable = GetDataTable(sql)
            For i As Integer = 0 To dt.Rows.Count - 1
                Me.CmbCourse.Items.Add(dt.Rows(i)("coursename"))
            Next
        Catch ex As Exception
            WriteErr(ex)
        End Try
    End Sub

    Private Sub addCmbStudent()


        Dim str1 As String
        Dim str2 As String
        CmbStudent.Items.Clear()
        Dim ClassID As String
        Dim sql As String
        Try
            If CheckClass.Checked And CmbClass.Text <> "" Then
                Dim iditem() As String
                iditem = CmbClass.Text.Split("|")
                ClassID = iditem(0)
                sql = "SELECT STUDENTID,STUDENTNAME FROM STUDENTINFO WHERE CLASSID=" + ClassID
            ElseIf CheckClass.Checked And CmbClass.Text = "" Then
                MessageBox.Show("请先选择班级")
                Return
            Else
                sql = "SELECT STUDENTID,STUDENTNAME FROM STUDENTINFO"
            End If

            Dim dt As DataTable = GetDataTable(sql)
            For i As Integer = 0 To dt.Rows.Count - 1
                str1 = dt.Rows(i)("STUDENTNAME")
                str2 = dt.Rows(i)("STUDENTID")
                Me.CmbStudent.Items.Add(str1 + "|" + str2)
            Next
        Catch ex As Exception
            WriteErr(ex)
        End Try
    End Sub

    Private Sub CmbStudent_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmbStudent.DropDown
        addCmbStudent()
    End Sub

    Private Function CourseNametoID(ByVal name As String)
        Try
            Dim sql As String = "SELECT  COURSEID FROM COURSEINFO WHERE COURSENAME='" + name + "'"
            Dim dt As DataTable = GetDataTable(sql)
            If dt.Rows.Count <> 0 Then
                Return dt.Rows(0)("COURSEID")
            End If

        Catch ex As Exception
            WriteErr(ex)
        End Try
    End Function

    Private Sub BtnQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnQuery.Click

        If CheckClass.Checked And CmbClass.Text = "" Then
            MessageBox.Show("请选择班级")
            Return
        ElseIf CheckCourse.Checked And CmbCourse.Text = "" Then
            MessageBox.Show("请选择课程")
            Return
        ElseIf CheckStudent.Checked And CmbStudent.Text = "" Then
            MessageBox.Show("请选择学生")
            Return
        End If

        Dim sql As String
        sql = "SELECT A.STUDENTID AS '学生编号',B.STUDENTNAME AS '学生姓名',C.COURSENAME AS '课程',A.RESULT AS '分数' FROM RESULTINFO A,STUDENTINFO B,COURSEINFO C WHERE A.STUDENTID=B.STUDENTID AND A.COURSEID=C.COURSEID"
        Dim Query As String = ""
        If CheckClass.Checked Then
            Dim iditem() As String
            iditem = CmbClass.Text.Split("|")
            Dim ClassID As String = iditem(0)
            Query = Query + " and B.classid=" + ClassID
        End If
        If CheckCourse.Checked Then
            Dim CourseID As String = CourseNametoID(CmbCourse.Text.Trim)
            Query = Query + " and A.courseid=" + CourseID
        End If
        If CheckStudent.Checked Then
            Dim iditem() As String
            iditem = CmbStudent.Text.Split("|")
            Dim StudentID As String = iditem(1)
            Query = Query + " and A.studentid=" + StudentID
        End If

        sql = sql + Query
        Try
            Dim dt As DataTable = GetDataTable(sql)
            DataGrid1.DataSource = dt
        Catch ex As Exception
            WriteErr(ex)
        End Try

    End Sub

    Private Sub CheckClass_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckClass.CheckStateChanged
        CmbClass.Text = ""
    End Sub

    Private Sub CheckCourse_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckCourse.CheckStateChanged
        CmbCourse.Text = ""
    End Sub

    Private Sub CheckStudent_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckStudent.CheckStateChanged
        CmbStudent.Text = ""
    End Sub

    Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
        Me.Close()
    End Sub

  
End Class