frmstudent.vb

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

    End Sub

#End Region
    Private Sub FrmStudent_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        BindData()
        addCmbStuClass()
        addCmbStuSex()
        If DataGrid1.VisibleRowCount > 0 Then
            bindTextBox(0)
        End If
    End Sub


    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        If Me.TxtStuID.Text.Trim() = String.Empty Then
            MessageBox.Show("学号不能为空")
            Me.TxtStuID.Focus()
        ElseIf Not JudgeInt(TxtStuID.Text.Trim) Then
            MessageBox.Show("学号必须为整数")
            Me.TxtStuID.Focus()
        ElseIf JudgeKeyExist("studentinfo", "studentid", TxtStuID.Text.Trim) Then
            MessageBox.Show("学号已经存在")
            Me.TxtStuID.Focus()
        ElseIf Me.TxtStuName.Text.Trim() = String.Empty Then
            MessageBox.Show("姓名不能为空")
            Me.TxtStuName.Focus()
        ElseIf Me.CmbStuSex.Text.Trim() = String.Empty Then
            MessageBox.Show("性别不能为空")
            Me.CmbStuSex.Focus()
        ElseIf Me.CmbStuClass.Text.Trim() = String.Empty Then
            MessageBox.Show("班级不能为空")
            Me.CmbStuClass.Focus()
        ElseIf Me.DateStuBir.Text.Trim() = String.Empty Then
            MessageBox.Show("生日不能为空")
            Me.DateStuBir.Focus()
        ElseIf Me.TxtPerCard.Text.Trim() = String.Empty Then
            MessageBox.Show("身份证号不能为空")
            Me.TxtPerCard.Focus()
        ElseIf Me.LblPhotoPath.Text.Trim() = String.Empty Then
            MessageBox.Show("照片不能为空")
        Else
            Me.InsertData()
        End If
    End Sub

    Private Sub BtnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit.Click
        If Me.TxtStuID.Text.Trim() = String.Empty Then
            MessageBox.Show("学号不能为空")
            Me.TxtStuID.Focus()
        ElseIf Not JudgeInt(TxtStuID.Text.Trim) Then
            MessageBox.Show("学号必须为整数")
            Me.TxtStuID.Focus()
        ElseIf Not JudgeKeyExist("studentinfo", "studentid", TxtStuID.Text.Trim) Then
            MessageBox.Show("要更新的学号不存在")
            Me.TxtStuID.Focus()
        ElseIf Me.TxtStuName.Text.Trim() = String.Empty Then
            MessageBox.Show("姓名不能为空")
            Me.TxtStuName.Focus()
        ElseIf Me.CmbStuSex.Text.Trim() = String.Empty Then
            MessageBox.Show("性别不能为空")
            Me.CmbStuSex.Focus()
        ElseIf Me.CmbStuClass.Text.Trim() = String.Empty Then
            MessageBox.Show("班级不能为空")
            Me.CmbStuClass.Focus()
        ElseIf Me.DateStuBir.Text.Trim() = String.Empty Then
            MessageBox.Show("生日不能为空")
            Me.DateStuBir.Focus()
        ElseIf Me.TxtPerCard.Text.Trim() = String.Empty Then
            MessageBox.Show("身份证号不能为空")
            Me.TxtPerCard.Focus()
        Else
            Me.UpdateData()
        End If
    End Sub

    Private Sub BtnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDel.Click
        If Me.TxtStuID.Text.Trim() = String.Empty Then
            MessageBox.Show("学号不能为空")
            Me.TxtStuID.Focus()
        ElseIf Not JudgeInt(TxtStuID.Text.Trim) Then
            MessageBox.Show("学号必须为整数")
            Me.TxtStuID.Focus()
        ElseIf Not JudgeKeyExist("studentinfo", "studentid", TxtStuID.Text.Trim) Then
            MessageBox.Show("要删除的记录集不存在")
            Me.TxtStuID.Focus()
        Else
            If MessageBox.Show("确定要删除？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) = DialogResult.OK Then
                Me.DeleteData()
            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 BindData()
        Try
            Dim sql As String = "select Studentid as '学号',StudentName as '学生姓名',Sex as '性别',Classid  as '班级',Birthday as '生日',PersonCardID as '身份证号',PersoncardID,StudentPhoto as '照片' from Studentinfo"
            Dim dt As DataTable = GetDataTable(sql)
            If dt.Rows.Count <> 0 Then
                DataGrid1.DataSource = dt
            End If
        Catch ex As Exception
            WriteErr(ex)
        End Try

    End Sub


    Private Sub bindTextBox(ByVal row As Int16)
        Me.TxtStuID.Text = Me.DataGrid1.Item(row, 0)
        Me.TxtStuName.Text = Me.DataGrid1.Item(row, 1)
        Me.CmbStuSex.Text = Me.DataGrid1.Item(row, 2)
        Me.CmbStuClass.Text = BindClass(Me.DataGrid1.Item(row, 3))
        Me.DateStuBir.Value = Me.DataGrid1.Item(row, 4)
        Me.TxtPerCard.Text = Me.DataGrid1.Item(row, 5)
        Dim MyData() As Byte = Me.DataGrid1.Item(row, 6)
        Me.LblPhotoPath.Text = ""
        Dim StuPhoto As New MemoryStream(MyData)
        PictureBox1.Image = Image.FromStream(StuPhoto)

    End Sub

    Private Sub InsertData()
        Dim IDItem(2) As String
        IDItem = CmbStuClass.Text.Split(":"c)

        Dim StuID As Int16 = Int16.Parse(TxtStuID.Text.Trim)
        Dim StuName As String = TxtStuName.Text.Trim
        Dim StuSex As String = CmbStuSex.Text.Trim
        Dim StuClass As String = IDItem(0)
        Dim StuBirthday As Date = DateStuBir.Value
        Dim StuCard As String = TxtPerCard.Text
        Dim StuPhoto As String = LblPhotoPath.Text.Trim


        Dim MyStream As New FileStream(StuPhoto, FileMode.Open, FileAccess.Read)
        Dim buffer(MyStream.Length) As Byte
        MyStream.Read(buffer, 0, MyStream.Length)
        MyStream.Close()


        Dim value As String = StuID.ToString + ",'" + StuName + "','" + StuSex + "'," + StuClass + ",'" + StuBirthday + "','" + StuCard + "'"

        Dim sql As String = "INSERT INTO STUDENTINFO (STUDENTID, STUDENTNAME,SEX,CLASSID,BIRTHDAY,PERSONCARDID,STUDENTPHOTO) VALUES(" + value + ", @IMAGE)"

        Dim sqlCmd As New SqlCommand
        sqlCmd.Parameters.Add("@image", buffer)
        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(2) As String
        IDItem = CmbStuClass.Text.Split(":"c)

        Dim StuID As Int16 = Int16.Parse(TxtStuID.Text.Trim)
        Dim StuName As String = TxtStuName.Text.Trim
        Dim StuSex As String = CmbStuSex.Text.Trim
        Dim StuClass As String = IDItem(0)
        Dim StuBirthday As Date = DateStuBir.Value
        Dim StuCard As String = TxtPerCard.Text
        Dim StuPhoto As String = LblPhotoPath.Text.Trim

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

        If StuPhoto <> "" Then
            Dim MyStream As New FileStream(StuPhoto, FileMode.Open, FileAccess.Read)
            Dim buffer(MyStream.Length) As Byte
            MyStream.Read(buffer, 0, MyStream.Length)
            MyStream.Close()

            sqlCmd.Parameters.Add("@image", buffer)
            sql = "UPDATE STUDENTINFO SET STUDENTNAME='" + StuName + "',SEX='" + StuSex + "',CLASSID=" + StuClass + ",BIRTHDAY='" + StuBirthday + "',PERSONCARDID='" + StuCard + "', STUDENTPHOTO=@IMAGE  WHERE STUDENTID= " + StuID.ToString
        Else
            sql = "UPDATE STUDENTINFO SET STUDENTNAME='" + StuName + "',SEX='" + StuSex + "',CLASSID=" + StuClass + ",BIRTHDAY='" + StuBirthday + "',PERSONCARDID='" + StuCard + "'WHERE STUDENTID= " + StuID.ToString
        End If

        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 StuID As Int16 = Int16.Parse(TxtStuID.Text.Trim)

        Dim sql As String = "DELETE FROM STUDENTINFO WHERE STUDENTID= " + StuID.ToString

        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 Sub BtnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOpen.Click

        OpenDlg.Title = "Select a picture file"
        OpenDlg.Filter = "(*.bmp)|*bmp|(*.jpg)|*.jpg"
        OpenDlg.ShowDialog()

        Me.LblPhotoPath.Text = OpenDlg.FileName.ToString()
        If Me.LblPhotoPath.Text <> "" Then
            PictureBox1.Image = Image.FromFile(OpenDlg.FileName.ToString())
        End If

    End Sub
    Private Sub addCmbStuClass()
        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.CmbStuClass.Items.Add(str4 + ":" + str1 + "级" + str2 + str3)
            Next
        Catch ex As Exception
            WriteErr(ex)
        End Try

    End Sub

    Private Sub AddCmbStuSex()
        Me.CmbStuSex.Items.Add("男")
        Me.CmbStuSex.Items.Add("女")

    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 DataGrid1_CurrentCellChanged1(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.CurrentCellChanged
        bindTextBox(Me.DataGrid1.CurrentCell.RowNumber)
    End Sub
End Class