SQL server 2008 Save,Edit,Delete and Search from Datagridview Vb.net 2008

Imports System.Data
Imports System.Data.SqlClient
Public Class frmRID
    Dim sCommand As SqlCommand
    Dim sAdapter As SqlDataAdapter
    Dim sBuilder As SqlCommandBuilder
    Dim sDs As DataSet
    Dim sTable As DataTable
 
    Private Sub cnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cnew.Click
        Call main()
        Con.Open()
        If cnew.Text = "New" Then
 
            cnew.Text = "Save"
            cedit.Text = "Cancel"
            clear()
 
            unlock()
            cdel.Enabled = False
            csearch.Enabled = False
            'txt1.Enabled = False
 
            Dim sql As String = "SELECT * FROM DOrecieve_inventory"
            'Dim connection As New SqlConnection '(connectionString)
            'connection.Open()
            sCommand = New SqlCommand(sql, Con)
            sAdapter = New SqlDataAdapter(sCommand)
            sBuilder = New SqlCommandBuilder(sAdapter)
            sDs = New DataSet()
            sAdapter.Fill(sDs, "DOrecieve_inventory")
            sTable = sDs.Tables("DOrecieve_inventory")
            Con.Close()
            grid1.DataSource = sDs.Tables("DOrecieve_inventory")
            'grid1.ReadOnly = True
            grid1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
 
        Else
            cnew.Text = "Save"
            cnew.Text = "New"
            cedit.Text = "Update"
            save()
            clear()
            lock()
            cdel.Enabled = True
            csearch.Enabled = True
        End If
    End Sub
    Private Sub clear()
        'grid1.Rows.Clear()
    End Sub
    Private Sub unlock()
        'grid1.Enabled = True
    End Sub
    Private Sub lock()
        'grid1.Enabled = False
    End Sub
    Private Sub save()
        Call main()
        Con.Open()
 
        Dim nonqueryCommand As SqlCommand = Con.CreateCommand()
 
        Try
 
            nonqueryCommand.CommandText = _
               "INSERT  INTO DOrecieve_inventory (type,lot,reference,location,meas,units,pounds) VALUES (@type,@lot,@reference,@location,@meas,@units,@pounds)"
 
            ' Add Parameters to Command Parameters collection
            nonqueryCommand.Parameters.Add("@type", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@lot", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@reference", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@location", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@meas", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@units", SqlDbType.VarChar, 50)
            nonqueryCommand.Parameters.Add("@pounds", SqlDbType.VarChar, 50)
 
 
            ' Prepare command for repeated execution
            nonqueryCommand.Prepare()
 
            ' Data to be inserted
            For Each row As DataGridViewRow In grid1.Rows
                If Not row.IsNewRow Then
                    nonqueryCommand.Parameters("@type").Value = row.Cells(0).Value.ToString
                    nonqueryCommand.Parameters("@lot").Value = row.Cells(1).Value.ToString
                    nonqueryCommand.Parameters("@reference").Value = row.Cells(2).Value.ToString
                    nonqueryCommand.Parameters("@location").Value = row.Cells(3).Value.ToString
                    nonqueryCommand.Parameters("@meas").Value = row.Cells(4).Value.ToString
                    nonqueryCommand.Parameters("@units").Value = row.Cells(5).Value.ToString
                    nonqueryCommand.Parameters("@pounds").Value = row.Cells(6).Value.ToString
                    'MsgBox("record save")
 
                End If
            Next
 
            nonqueryCommand.ExecuteNonQuery()
 
        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        Finally
            ' Close Connection
            Con.Close()
            'Console.WriteLine("Connection Closed")
 
        End Try
 
 
    End Sub
 
 
    Private Sub frmRID_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lock()
    End Sub
 
    Private Sub cedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cedit.Click
        If cedit.Text = "Update" Then
 
            updatesave()
            csearch.Text = "Search"
            clear()
            lock()
        ElseIf cedit.Text = "Cancel" Then
            cnew.Text = "New"
            cedit.Text = "Update"
            lock()
            clear()
            cdel.Enabled = True
            csearch.Enabled = True
        End If
    End Sub
    Private Sub updatesave()
        Call main()
        Con.Open()
        Dim cmd As SqlCommand = Con.CreateCommand()
        'Dim nonqueryCommand As SqlCommand = Con.CreateCommand()
 
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "update DOrecieve_inventory set lot=@lot,reference=@reference,location=@location,meas=@mas,units=@units,pounds=@pounds where type=@type"
 
        cmd.Parameters.Add("@type", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@lot", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@reference", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@location", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@mas", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@units", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@pounds", SqlDbType.VarChar, 50)
        ' Prepare command for repeated execution
        cmd.Prepare()
 
        ' Data to be inserted
        For Each row As DataGridViewRow In grid1.Rows
            If Not row.IsNewRow Then
                cmd.Parameters("@type").Value = row.Cells(0).Value.ToString
                cmd.Parameters("@lot").Value = row.Cells(1).Value.ToString
                cmd.Parameters("@reference").Value = row.Cells(2).Value.ToString
                cmd.Parameters("@location").Value = row.Cells(3).Value.ToString
                cmd.Parameters("@mas").Value = row.Cells(4).Value.ToString
                cmd.Parameters("@units").Value = row.Cells(5).Value.ToString
                cmd.Parameters("@pounds").Value = row.Cells(6).Value.ToString
                'MsgBox("update save")
            End If
        Next
 
        If Con.State = ConnectionState.Closed Then
            Con.Open()
        End If
        cmd.Connection = Con
        Try
            cmd.ExecuteNonQuery() 'Then
            MsgBox("Record Has Updated...!!", MsgBoxStyle.Information, "UPDATE EXPENDITURE")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
 
        Con.Close()
    End Sub
 
    Private Sub cdel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdel.Click
        On Error GoTo err
 
        If cdel.Text = "Delete" Then
            Select Case MsgBox("Are you sure You want to Delete this Record", MsgBoxStyle.YesNo)
                Case MsgBoxResult.Yes
                    deleterecord()
                    csearch.Text = "Search"
                    clear()
                    txt1.Text = ""
                    lock()
                    grid1.Refresh()
                Case MsgBoxResult.No
                    GoTo err
            End Select
        End If
err:
        Exit Sub
    End Sub
    Private Sub deleterecord()
        Call main()
        Con.Open()
        Dim adapter As New SqlDataAdapter
 
        Dim cmd As SqlCommand = Con.CreateCommand()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "delete DOrecieve_inventory where type=@type"
 
        cmd.Parameters.Add("@type", SqlDbType.VarChar, 50)
 
        'xxx Prepare command for repeated execution
        cmd.Prepare()
 
        ' Data to be inserted
        For Each row As DataGridViewRow In grid1.Rows
            If Not row.IsNewRow Then
                cmd.Parameters("@type").Value = row.Cells(0).Value.ToString
            End If
        Next
 
        If Con.State = ConnectionState.Closed Then
            Con.Open()
        End If
        cmd.Connection = Con
        Try
            cmd.ExecuteNonQuery() 'Then
            MsgBox("Record Has Delete..!!", MsgBoxStyle.Information, "DElete EXPENDITURE")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
 
        Con.Close()
 
 
    End Sub
 
    Private Sub csearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles csearch.Click
 
 
        If csearch.Text = "Search" Then
            csearch.Text = "Cancel"
            cnew.Enabled = False
            unlock()
            txt1.Focus()
 
        ElseIf csearch.Text = "Cancel" Then
            csearch.Text = "Search"
            cnew.Enabled = True
            'clear()
            txt1.Text = ""
            lock()
 
        End If
 
 
    End Sub
 
    Private Sub txt1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt1.TextChanged
 
        showauto()
        showsearch()
 
    End Sub
    Private Sub showauto()
        Call main()
        Con.Open()
 
        Dim cmd As New SqlCommand("SELECT type FROM DOrecieve_inventory", Con)
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds, "My List") 'list can be any name u want
 
        Dim col As New AutoCompleteStringCollection
        Dim i As Integer
        For i = 0 To ds.Tables(0).Rows.Count - 1
            col.Add(ds.Tables(0).Rows(i)("type").ToString())
 
        Next
        txt1.AutoCompleteSource = AutoCompleteSource.CustomSource
        txt1.AutoCompleteCustomSource = col
        txt1.AutoCompleteMode = AutoCompleteMode.Suggest
 
        Con.Close()
    End Sub
    Private Sub showsearch()
        Call main()
        Con.Open()
 
        Dim cmd As New SqlCommand("select * from DOrecieve_inventory where type like '%" + txt1.Text + "%'", Con)
        Dim Adpt As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        If (Adpt.Fill(ds, "DOrecieve_inventory")) Then
 
            grid1.DataSource = ds.Tables(0)
            MessageBox.Show("match found")
 
        Else
        MessageBox.Show("match not found")
        End If
        Con.Close()
    End Sub
 
 
End Class