DataGrid view Add, Delete and Update

gistfile1.vb

'Visual Basic Online Courses
'DataGridView Add, Edit, Delete, Search
'Using the KeyBoard, MS-Access, Sql Server
'By : evry1.net/VBNet

Imports System.Data.SqlClient
Public Class Form1
    Public CN As New SqlClient.SqlConnection
    Public RD As SqlDataReader
    Public DBS As New DataSet
    Public Ckind As New SqlClient.SqlCommand
    Public Akind As New SqlClient.SqlDataAdapter
    Public CmDB As New SqlClient.SqlCommandBuilder
    Public DkindS As New DataTable
    Public Rec As DataRow
    Public Frec() As DataRow
    Public N, I, CntRs, CntC As Integer
    Public SqlStr, xC As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String = "Data Source=Evry1falls;Initial Catalog=mydb;Integrated Security=True"
        Dim sql As String = "SELECT * FROM Kinds"
        Dim connection As New SqlConnection(connectionString)
        Dim dataadapter As New SqlDataAdapter(sql, connection)
        Dim ds As New DataSet()
        connection.Open()
        dataadapter.Fill(ds, "Kinds")
        connection.Close()
        DG1.DataSource = ds
        DG1.DataMember = "Kinds"
        DG1.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single
        DG1.CellBorderStyle = DataGridViewCellBorderStyle.Single
        DG1.Columns("Kind").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        DG1.Columns("kind").HeaderCell.Value = "Sort Of Items"
        Rec = Nothing
        Frec = Nothing
        PictureBox1.Hide()
        Label1.Text = ""
    End Sub

    Private Sub DG1_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DG1.CellBeginEdit
        If CN.State = ConnectionState.Open Then CN.Close()
        CN.ConnectionString = "Data Source=Evry1falls;Initial Catalog=mydb;Integrated Security=True"
        CN.Open()
        PictureBox1.Show()
        Label1.Text = ""
        PictureBox1.Image = My.Resources.loading

        SqlStr = "Select * from Kinds"
        Ckind.Connection = CN
        Ckind.CommandText = SqlStr
        With Akind
            .SelectCommand = Ckind
            .Fill(DBS, "Kinds")
        End With

        DkindS = DBS.Tables("Kinds")
        CntC = DG1.Rows.Count
        If DG1.Rows(DG1.CurrentCell.RowIndex).IsNewRow = False Then
            xC = DG1.CurrentCell.Value.ToString
        Else
            xC = Nothing
        End If
    End Sub
    Private Sub DG1_CellEndEdit(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
    Handles DG1.CellEndEdit
        If DG1.Rows.Count = CntC Then
            Frec = DkindS.Select("Kind = '" & xC & "'")
            If Frec.Length = 0 Then
                Exit Sub
            End If
            Rec = Frec(0)
            Rec.BeginEdit()
            Rec!Kind = DG1.Columns(0).DataGridView(0, DG1.CurrentCell.RowIndex).Value
            Rec.EndEdit()
            If DBS.HasChanges = True Then
                CmDB.DataAdapter = Akind
                Akind.Update(DBS, "Kinds")
                Label1.Text = "Editted Successfully"
                PictureBox1.Hide()
            End If
            Exit Sub
        End If
        SqlStr = "Select * from Kinds"
        Ckind.Connection = CN
        Ckind.CommandType = CommandType.Text
        Ckind.CommandText = SqlStr
        With Akind
            .SelectCommand = Ckind
            .Fill(DBS, "Kinds")
        End With
        DkindS = DBS.Tables("Kinds")
        If DG1.Columns(0).DataGridView(0, DG1.CurrentCell.RowIndex).Value Is DBNull.Value Then
            PictureBox1.Hide()
            Label1.Text = "Empty Sorts Are Not Allowed"
            Exit Sub
        End If
        PictureBox1.Show()
        Rec = DkindS.NewRow
        Rec!KIND = DG1.Columns(0).DataGridView(0, DG1.CurrentCell.RowIndex).Value
        DkindS.Rows.Add(Rec)
        If DBS.HasChanges = True Then
            CmDB.DataAdapter = Akind
            Akind.Update(DBS, "Kinds")
            Label1.Text = "New Record Added Successfully"
            PictureBox1.Hide()
        End If
    End Sub
    
    Private Sub DG1_KeyDown(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.KeyEventArgs) _
    Handles DG1.KeyDown

        If CN.State = ConnectionState.Open Then CN.Close()
        CN.ConnectionString = "Data Source=Evry1falls;Initial Catalog=MyDB;Integrated Security=True"
        CN.Open()
        If e.KeyCode = Keys.Delete Then
            If DG1.Rows.GetRowCount(DataGridViewElementStates.Visible) = 1 Then
                Label1.Text = "No Data Available"
                Exit Sub
            End If
            If DG1.Rows(DG1.CurrentRow.Index).IsNewRow = True Then
                Label1.Text = "Violation - Selected Couldn't be Deleted"
                Exit Sub
            End If
            Dim xc As String
            xc = DG1.Columns(0).DataGridView(0, DG1.CurrentCell.RowIndex).Value.ToString
            SqlStr = "Delete From Kinds where (Kind) = '" & xc & "'"
            Ckind.Connection = CN
            Ckind.CommandText = SqlStr
            CntRs = Ckind.ExecuteNonQuery
            Label1.Text = (CntRs & " Record(S) Deleted Successfully")
            DG1.Rows.Remove(DG1.CurrentCell.OwningRow)
            DG1.Refresh()
        End If
    End Sub
End Class