frmstuscore.vb

师生情况管理系统

        '
        'FrmStuScore
        '
        Me.AcceptButton = Me.btnSelect
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.CancelButton = Me.btnExit
        Me.ClientSize = New System.Drawing.Size(462, 415)
        Me.Controls.Add(Me.cmbCourse)
        Me.Controls.Add(Me.txtRemark)
        Me.Controls.Add(Me.Label7)
        Me.Controls.Add(Me.btnExit)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.dgDate)
        Me.Controls.Add(Me.txtTime)
        Me.Controls.Add(Me.Label6)
        Me.Controls.Add(Me.txtScore)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.txtName)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.btnSelect)
        Me.Controls.Add(Me.txtID)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.btnSave)
        Me.Name = "FrmStuScore"
        Me.Text = "FrmStuScore"
        CType(Me.dgDate, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub FrmStuScore_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sqlConn = New SqlConnection(FrmMain.DBCONN.DataConStr)
        sqlConn.Open()
        sqlComm = New SqlCommand("select * from Score", sqlConn)
        daDate = New SqlDataAdapter("SELECT b.SID AS 学号, b.Sname AS 姓名, c.Cname AS 课程, a.Sscore AS 分数, a.Stime AS 学期, a.Sremark AS 备注 FROM dbo.Score a INNER JOIN dbo.StuInfo b ON a.SID = b.SID INNER JOIN dbo.CourseInfo c ON a.CID = c.CID", sqlConn)
        dsDate = New DataSet
        daDate.Fill(dsDate, "Table")
        dgDate.SetDataBinding(dsDate, "Table")

        Dim objDateReader As SqlDataReader
        sqlComm.CommandText = "select Cname from CourseInfo"
        objDateReader = sqlComm.ExecuteReader
        While objDateReader.Read()
            cmbCourse.Items.Add(objDateReader(0))
        End While
        cmbCourse.SelectedIndex = 0
        objDateReader.Close()

        Me.cmbCourse.Enabled = False
        Me.txtID.Enabled = True
        Me.txtName.Enabled = False
        Me.txtTime.Enabled = False
        Me.txtRemark.Enabled = False
        Me.txtScore.Enabled = False
        Me.btnSave.Enabled = False
        Me.btnCancel.Enabled = False

        flag = 3                            'flag为3，则是可以查询
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.sqlConn.Close()
        Me.Close()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If dgDate.CurrentCell.RowNumber < 0 Then
            MsgBox("你还没选中信息")
            Exit Sub
        End If
        flag = 1       'flag为1，则是添加数据
        Me.cmbCourse.Enabled = True
        Me.txtID.Enabled = False
        Me.txtName.Enabled = False
        Me.txtTime.Enabled = True
        Me.txtRemark.Enabled = True
        Me.txtScore.Enabled = False
        Me.btnSave.Enabled = True
        Me.btnCancel.Enabled = True
        Me.btnAdd.Enabled = False
        Me.btnUpdate.Enabled = False
        Me.btnDelete.Enabled = False

        Me.cmbCourse.SelectedIndex = 0
        Me.txtScore.Text = ""
        Me.txtTime.Text = ""
        Me.txtRemark.Text = ""

    End Sub

    Private Sub dgDate_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgDate.CurrentCellChanged
        txtID.Text = dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(0)
        txtName.Text = dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(1)
        cmbCourse.SelectedItem = dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(2)
        txtScore.Text = dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(3)
        txtTime.Text = dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(4)
        txtRemark.Text = dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(5)
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        If dgDate.CurrentCell.RowNumber < 0 Then
            MsgBox("你还没选中信息")
            Exit Sub
        End If
        flag = 2        'flag为2，则是更新数据
        Me.cmbCourse.Enabled = True
        Me.txtID.Enabled = False
        Me.txtName.Enabled = False
        Me.txtTime.Enabled = True
        Me.txtRemark.Enabled = True
        Me.txtScore.Enabled = False
        Me.btnSave.Enabled = True
        Me.btnCancel.Enabled = True
        Me.btnAdd.Enabled = False
        Me.btnUpdate.Enabled = False
        Me.btnDelete.Enabled = False
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If dgDate.CurrentCell.RowNumber < 0 Then
            MsgBox("你还没有选中信息")
            Exit Sub
        End If
        If MsgBox("你确定要删除吗？", MsgBoxStyle.OKCancel, "删除") = MsgBoxResult.OK Then
            Dim strDelete As String
            strDelete = dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(0)
            sqlComm.CommandText = "delete Score where SID = '" & strDelete & "'"
            daDate.DeleteCommand = sqlComm
            Try
                sqlComm.ExecuteNonQuery()
                MsgBox("删除成功")
                dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Delete()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            Me.btnAdd.Enabled = False
            Me.btnUpdate.Enabled = False
            Me.btnDelete.Enabled = False
            Me.btnCancel.Enabled = True
        End If
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.cmbCourse.Enabled = False
        Me.txtID.Enabled = True
        Me.txtName.Enabled = False
        Me.txtTime.Enabled = False
        Me.txtRemark.Enabled = False
        Me.txtScore.Enabled = False
        Me.btnSave.Enabled = True
        Me.btnCancel.Enabled = False
        Me.btnAdd.Enabled = True
        Me.btnUpdate.Enabled = True
        Me.btnDelete.Enabled = True

        flag = 3
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If Me.check() = False Then
            MsgBox(flag)
            Exit Sub
        End If

        Dim objDataReader As SqlDataReader
        Dim strCID As String
        sqlComm.CommandText = "select CID from CourseInfo where Cname = '" & cmbCourse.SelectedItem & "'"
        objDataReader = sqlComm.ExecuteReader()
        While objDataReader.Read()
            strCID = objDataReader(0)
        End While
        objDataReader.Close()

        If flag = 1 Then    '添加数据操作
            Dim objRow As DataRow
            objRow = dsDate.Tables(0).NewRow()
            objRow(0) = txtID.Text
            objRow(1) = txtName.Text
            objRow(2) = cmbCourse.SelectedItem
            objRow(3) = txtScore.Text
            objRow(4) = txtTime.Text
            objRow(5) = txtRemark.Text
            dsDate.Tables(0).Rows.Add(objRow)


            daDate.InsertCommand = sqlComm
            daDate.InsertCommand.CommandText = "insert into Score values ('" & txtID.Text & "', '" & strCID & "', " & txtScore.Text & ", '" & txtTime.Text & "', '" & txtRemark.Text & "')"
            Try
                sqlComm.ExecuteNonQuery()
                MsgBox("添加成功")
            Catch ex As Exception
                MsgBox(ex.Message)
                dsDate.Tables(0).Rows(dsDate.Tables(0).Rows.Count - 1).Delete()
            End Try

        Else : flag = 2
            dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(0) = txtID.Text
            dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(1) = txtName.Text
            dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(2) = cmbCourse.SelectedItem
            dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(3) = txtScore.Text
            dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(4) = txtTime.Text
            dsDate.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(5) = txtRemark.Text

            daDate.UpdateCommand = sqlComm
            daDate.UpdateCommand.CommandText = "update Score set  Sscore = " & txtScore.Text & ", Stime = '" & txtTime.Text & "', Sremark = '" & txtRemark.Text & "' where SID = '" & txtID.Text & " '"
            Try
                sqlComm.ExecuteNonQuery()
                MsgBox("更改数据成功")
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If

        
    End Sub

    Private Function check() As Boolean
        If txtScore.Text = "" Then
            MsgBox("分数不能为空")
            Return False
        End If
        If txtRemark.Text = "" Then
            MsgBox("备注不能为空")
            Return False
        End If
        If txtTime.Text = "" Then
            MsgBox("学期不能为空")
            Return False
        End If
    End Function

    Private Sub txtID_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtID.KeyUp
        If flag = 3 Then
            strSelect += txtID.Text
            If strSelect = "" Then
                tableNum = 0
                dsDate.Tables(0).Clear()
                daDate.Fill(dsDate, "Table")
                dgDate.SetDataBinding(dsDate, "Table")
            Else
                tableNum = 1
                sqlComm.CommandText = "select b.SID AS 学号, b.Sname AS 姓名, c.Cname AS 课程, a.Sscore AS 分数, a.Stime AS 学期, a.Sremark AS 备注 from Score a, StuInfo b, CourseInfo c where a.SID = b.SID and a.CID = c.CID and a.SID like '" & txtID.Text & "%'"
                daDate.SelectCommand = sqlComm
                If dsDate.Tables.Count > 1 Then
                    dsDate.Tables(1).Clear()
                End If
                daDate.Fill(dsDate, "TableSelect")
                dgDate.SetDataBinding(dsDate, "TableSelect")
            End If
        End If

    End Sub
End Class