frmstusleep.vb

师生情况管理系统

        Me.ResumeLayout(False)

    End Sub

#End Region
    Private sqlConn As SqlConnection
    Private sqlComm As SqlCommand
    Private daDate As SqlDataAdapter
    Private dsData As DataSet
    Private flag As Int32                   '标明添加还是修改还是能够查询
    Private strSelect As String             '查询框是否有值
    Private tableNum As Int32               '查询框没值则为0，有值则为1


    Private Sub FrmStuSleep_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sqlConn = New SqlConnection(FrmMain.DBCONN.DataConStr)
        sqlComm = New SqlCommand("select * from bedInfo", sqlConn)
        daDate = New SqlDataAdapter("select BID 宿舍号, Bremark  备注 from bedInfo", sqlConn)
        dsData = New DataSet
        sqlConn.Open()
        daDate.Fill(dsData, "Table")
        dgDate.SetDataBinding(dsData, "Table")

        Me.txtRemark.Enabled = False
        Me.txtSleep.Enabled = True
        Me.btnSave.Enabled = False

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

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

    Private Sub dgDate_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgDate.CurrentCellChanged
        txtSleep.Text = dsData.Tables(tableNum).Rows(dgDate.CurrentCell.RowNumber).Item(0)
        txtRemark.Text = dsData.Tables(tableNum).Rows(dgDate.CurrentCell.RowNumber).Item(1)
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        flag = 1        'flag为1，则表示添加数据
        txtSleep.Text = ""
        txtRemark.Text = ""

        Me.btnSave.Enabled = True
        Me.btnCancel.Enabled = True
        Me.btnAdd.Enabled = False
        Me.btnDelete.Enabled = False
        Me.btnSelect.Enabled = False
        Me.btnUpdate.Enabled = False
        Me.txtSleep.Enabled = True
        Me.txtRemark.Enabled = True
    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.btnSave.Enabled = True
        Me.btnCancel.Enabled = True
        Me.btnAdd.Enabled = False
        Me.btnDelete.Enabled = False
        Me.btnSelect.Enabled = False
        Me.btnUpdate.Enabled = False
        Me.txtSleep.Enabled = False
        Me.txtRemark.Enabled = True
    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
            sqlComm.CommandText = "delete bedInfo where BID = '" & txtSleep.Text & "'"
            daDate.DeleteCommand = sqlComm
            Try
                sqlComm.ExecuteNonQuery()
                MsgBox("删除成功")
                dsData.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Delete()

                Me.btnSave.Enabled = False
                Me.btnCancel.Enabled = True
                Me.btnAdd.Enabled = False
                Me.btnDelete.Enabled = False
                Me.btnSelect.Enabled = False
                Me.btnUpdate.Enabled = False
                Me.txtSleep.Enabled = False
                Me.txtRemark.Enabled = False
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    Private Function check() As Boolean
        If txtSleep.Text = "" Then
            MsgBox("寝室号不能为空")
            Return False
        End If
        If txtRemark.Text = "" Then
            MsgBox("备注不能为空")
            Return False
        End If
        Return True
    End Function
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If Me.check() = False Then
            Exit Sub
        End If

        If flag = 1 Then        '添加操作
            Dim objRow As DataRow
            objRow = dsData.Tables(0).NewRow
            objRow(0) = txtSleep.Text
            objRow(1) = txtRemark.Text
            dsData.Tables(0).Rows.Add(objRow)

            sqlComm.CommandText = "insert into bedInfo values ('" & txtSleep.Text & "', '" & txtRemark.Text & "')"
            daDate.DeleteCommand = sqlComm

            Try
                sqlComm.ExecuteNonQuery()
                MsgBox("添加成功")

                Me.btnSave.Enabled = False
                Me.btnCancel.Enabled = True
                Me.btnAdd.Enabled = False
                Me.btnDelete.Enabled = False
                Me.btnSelect.Enabled = False
                Me.btnUpdate.Enabled = False
                Me.txtSleep.Enabled = False
                Me.txtRemark.Enabled = False
            Catch ex As Exception
                MsgBox(ex.Message)
                dsData.Tables(0).Rows(dsData.Tables(0).Rows.Count - 1).Delete()
            End Try
        End If

        If flag = 2 Then        '更新操作
            dsData.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(0) = txtSleep.Text
            dsData.Tables(0).Rows(dgDate.CurrentCell.RowNumber).Item(1) = txtRemark.Text

            sqlComm.CommandText = "update bedInfo set Bremark = '" & txtRemark.Text & "' where BID = '" & txtSleep.Text & "'"
            daDate.DeleteCommand = sqlComm

            Try
                sqlComm.ExecuteNonQuery()
                MsgBox("更新成功")

                Me.btnSave.Enabled = True
                Me.btnCancel.Enabled = True
                Me.btnAdd.Enabled = False
                Me.btnDelete.Enabled = False
                Me.btnSelect.Enabled = False
                Me.btnUpdate.Enabled = False
                Me.txtSleep.Enabled = False
                Me.txtRemark.Enabled = False
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.btnSave.Enabled = False
        Me.btnCancel.Enabled = False
        Me.btnAdd.Enabled = True
        Me.btnDelete.Enabled = True

        Me.btnUpdate.Enabled = True
        Me.txtSleep.Enabled = True
        Me.txtRemark.Enabled = False

        flag = 3
    End Sub

    Private Sub txtSleep_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtSleep.KeyUp
        If flag = 3 Then
            strSelect += txtSleep.Text
            If strSelect = "" Then
                tableNum = 0
                dsData.Tables(0).Clear()
                daDate.Fill(dsData, "Table")
                dgDate.SetDataBinding(dsData, "Table")
            Else
                tableNum = 1
                sqlComm.CommandText = "select BID 宿舍号, Bremark  备注 from bedInfo where BID like '" & txtSleep.Text & "%'"
                daDate.SelectCommand = sqlComm
                If dsData.Tables.Count > 1 Then
                    dsData.Tables(1).Clear()
                End If
                daDate.Fill(dsData, "TableSelect")
                dgDate.SetDataBinding(dsData, "TableSelect")
            End If
        End If

    End Sub

End Class