frmresult.vb

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

        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 Function BindClass(ByVal classid As String)
        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 AND A.CLASSID=" + classid
            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")

                Return str4 + "|" + str1 + "级" + str2 + str3
            Next
        Catch ex As Exception
            WriteErr(ex)
        End Try
    End Function


    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 iditem() As String
        iditem = CmbClass.Text.Split("|"c)
        Dim ClassID As String = iditem(0)
        Dim str1 As String
        Dim str2 As String
        CmbStudent.Items.Clear()
        Try
            Dim sql As String = "SELECT STUDENTID,STUDENTNAME FROM STUDENTINFO WHERE CLASSID=" + ClassID
            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 BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        If Me.CmbClass.Text.Trim() = String.Empty Then
            MessageBox.Show("班级不能为空")
            Me.CmbClass.Focus()
        ElseIf Me.CmbCourse.Text.Trim() = String.Empty Then
            MessageBox.Show("课程不能为空")
            Me.CmbCourse.Focus()
        ElseIf Me.CmbStudent.Text.Trim() = String.Empty Then
            MessageBox.Show("学生姓名不能为空")
            Me.CmbStudent.Focus()

        ElseIf Me.TxtResult.Text.Trim() = String.Empty Then
            MessageBox.Show("成绩不能为空")
            Me.TxtResult.Focus()
        ElseIf Not JudgeInt(TxtResult.Text.Trim) Then
            MessageBox.Show("成绩必须为1-100的整数")
            Me.TxtResult.Focus()
        ElseIf Me.TxtTeacher.Text.Trim() = String.Empty Then
            MessageBox.Show("教师不能为空")
            Me.TxtTeacher.Focus()
        ElseIf Me.InDate.Text.Trim() = String.Empty Then
            MessageBox.Show("录入时间不能为空")
            Me.InDate.Focus()
        ElseIf CmbStudent.Text.Trim <> "" Then
            Dim iditem() As String
            iditem = CmbStudent.Text.Split("|"c)
            Dim StudentID As String = iditem(1)
            If JudgeKeyExist("resultinfo", "studentid", StudentID, "courseid", CourseNametoID(CmbCourse.Text.Trim)) Then
                MessageBox.Show("该学生该课程相关纪录已经存在")
            Else
                InsertData()
            End If
        End If
    End Sub

    Private Sub BtnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit.Click
        If Me.CmbClass.Text.Trim() = String.Empty Then
            MessageBox.Show("班级不能为空")
            Me.CmbClass.Focus()
        ElseIf Me.CmbCourse.Text.Trim() = String.Empty Then
            MessageBox.Show("课程不能为空")
            Me.CmbCourse.Focus()
        ElseIf Me.CmbStudent.Text.Trim() = String.Empty Then
            MessageBox.Show("学生姓名不能为空")
            Me.CmbStudent.Focus()
        ElseIf Me.TxtResult.Text.Trim() = String.Empty Then
            MessageBox.Show("成绩不能为空")
            Me.TxtResult.Focus()
        ElseIf Not JudgeInt(TxtResult.Text.Trim) Then
            MessageBox.Show("成绩必须为1-100的整数")
            Me.TxtResult.Focus()
        ElseIf Me.TxtResult.Text.Trim() = String.Empty Then
            MessageBox.Show("成绩不能为空")
            Me.TxtResult.Focus()
        ElseIf Me.InDate.Text.Trim() = String.Empty Then
            MessageBox.Show("录入时间不能为空")
            Me.InDate.Focus()
        ElseIf CmbStudent.Text.Trim <> "" Then
            Dim iditem() As String
            iditem = CmbStudent.Text.Split("|"c)
            Dim StudentID As String = iditem(1)
            If Not JudgeKeyExist("resultinfo", "studentid", StudentID, "courseid", CourseNametoID(CmbCourse.Text.Trim)) Then
                MessageBox.Show("该学生该课程相关纪录不存在")
            Else
                UpdateData()
            End If
        End If
    End Sub

    Private Sub BtnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDel.Click
        If Me.CmbCourse.Text.Trim() = String.Empty Then
            MessageBox.Show("课程不能为空")
            Me.CmbCourse.Focus()
        ElseIf Me.CmbStudent.Text.Trim() = String.Empty Then
            MessageBox.Show("学生姓名不能为空")
            Me.CmbStudent.Focus()
        ElseIf CmbStudent.Text.Trim <> "" Then
            Dim iditem() As String
            iditem = CmbStudent.Text.Split("|"c)
            Dim StudentID As String = iditem(1)
            If Not JudgeKeyExist("resultinfo", "studentid", StudentID, "courseid", CourseNametoID(CmbCourse.Text.Trim)) Then
                MessageBox.Show("该学生该课程相关纪录不存在")
            Else
                If MessageBox.Show("确定要删除？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) = DialogResult.OK Then
                    Me.DeleteData()
                End If
            End If
        End If
    End Sub

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

    Private Sub DataGrid1_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.CurrentCellChanged
        bindTextBox(Me.DataGrid1.CurrentCell.RowNumber)
    End Sub

    Private Sub InsertData()

        Dim iditem() As String
        iditem = CmbClass.Text.Split("|"c)
        Dim ClassID As String = iditem(0)
        iditem = CmbStudent.Text.Split("|"c)
        Dim StudentID As String = iditem(1)
        Dim CourseID As String = CourseNametoID(CmbCourse.Text.Trim)
        Dim result As String = TxtResult.Text.Trim
        Dim teacher As String = TxtTeacher.Text.Trim
        Dim mydate As Date = InDate.Value

        Dim value As String = CourseID + "," + StudentID + "," + result + ",'" + teacher + "','" + mydate + "'"

        Dim sql As String = "INSERT INTO RESULTINFO (COURSEID,STUDENTID,RESULT,TEACHER,INDATE) VALUES(" + value + ")"

        Dim sqlCmd As New SqlCommand
        sqlCmd.Connection = SqlCon
        sqlCmd.CommandText = sql

        Try
            SqlCon.Open()
            sqlCmd.ExecuteNonQuery()
            MessageBox.Show("添加记录成功")
        Catch ex As Exception
            MessageBox.Show("添加记录失败")
            WriteErr(ex)
        Finally
            SqlCon.Close()
        End Try
        BindData()
    End Sub

    Private Sub UpdateData()
        Dim iditem() As String
        iditem = CmbClass.Text.Split("|"c)
        Dim ClassID As String = iditem(0)
        iditem = CmbStudent.Text.Split("|"c)
        Dim StudentID As String = iditem(1)
        Dim CourseID As String = CourseNametoID(CmbCourse.Text.Trim)
        Dim result As String = TxtResult.Text.Trim
        Dim teacher As String = TxtTeacher.Text.Trim
        Dim mydate As Date = InDate.Value

        Dim sql As String = "UPDATE RESULTINFO SET RESULT =" + result + ",teacher='" + teacher + "',indate='" + mydate + "' where studentid=" + StudentID + "    and courseid=" + CourseID

        Dim sqlCmd As New SqlCommand
        sqlCmd.Connection = SqlCon
        sqlCmd.CommandText = sql

        Try
            SqlCon.Open()
            sqlCmd.ExecuteNonQuery()
            MessageBox.Show("修改记录成功")
        Catch ex As Exception
            MessageBox.Show("修改记录失败")
            WriteErr(ex)
        Finally
            SqlCon.Close()
        End Try
        BindData()
    End Sub

    Private Sub DeleteData()
        Dim iditem() As String
        iditem = CmbStudent.Text.Split("|"c)
        Dim StudentID As String = iditem(1)
        Dim CourseID As String = CourseNametoID(CmbCourse.Text.Trim)


        Dim sql As String = "DELETE FROM RESULTINFO WHERE STUDENTID= " + StudentID + " AND COURSEID=" + CourseID

        Dim sqlCmd As New SqlCommand
        sqlCmd.Connection = SqlCon
        sqlCmd.CommandText = sql

        Try
            SqlCon.Open()
            sqlCmd.ExecuteNonQuery()
            MessageBox.Show("删除记录成功")
        Catch ex As Exception
            MessageBox.Show("删除记录失败")
            WriteErr(ex)
        Finally
            SqlCon.Close()
        End Try
        BindData()
        bindTextBox(0)
    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 CmbStudent_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmbStudent.DropDown
        If CmbClass.Text = "" Then
            MessageBox.Show("请先选择班级")
        Else
            addCmbStudent()
        End If

    End Sub
End Class