How to delete a row from DataGridview and also Excel sheet permanently in VB.NET

See more:

VB

What i want to do is when i select a Particular row in DataGridView and when i click the DELETE button. Then the row has to delete from my Excelsheet(database) also. I don't know how to write the the code for this delete button but i have tried something which is not working .

Here is my Code:

Imports System.Data.OleDb
Imports System
Imports System.Text
Imports System.IO
Imports System.Net
Imports System.Net.Sockets
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Threading
Imports Microsoft.VisualBasic
Imports System.Text.RegularExpressions
Imports System.Windows.Forms

Public Class Form1
    Private Function vld(ByVal ParamArray ctl() As Object) As Boolean
        For i As Integer = 0 To UBound(ctl)
            If ctl(i).text = "" Then
                ErrorProvider1.SetError(ctl(i), ctl(i).tag)
                Return False
                Exit For
            End If
        Next
        Return True
    End Function
    Dim cn As New OleDbConnection
    Dim cm As New OleDbCommand
    Dim da As OleDbDataAdapter
    Dim dt As New DataTable

    Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        cn.Close()
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        TxtExamtime.Format = DateTimePickerFormat.Custom
        TxtExamtime.CustomFormat = "hh:MM tt"
        cn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source=C:\psave\New folder\save.xls;Extended Properties=Excel 8.0;"
        cn.Open()
        FillDataGridView("select ID, Family Name, Given Name, Gender, DOB, Exam Date, Exam Time, Street Name, House Nr, PLZ, City from [edit$]")

    End Sub

    Private Sub FillDataGridView(ByVal Query As String)
        da = New OleDbDataAdapter(Query, cn)
        dt.Clear()
        da.Fill(dt)
        With DataGridView1
            .DataSource = dt
            .Columns(0).HeaderText = "ID"
            .Columns(1).HeaderText = "Family Name"
            .Columns(2).HeaderText = "Given Name"
            .Columns(3).HeaderText = "Gender"
            .Columns(4).HeaderText = "DOB"
            .Columns(5).HeaderText = "Exam Date"
            .Columns(6).HeaderText = "Exam Time"
            .Columns(7).HeaderText = "Street Name"
            .Columns(8).HeaderText = "House Nr"
            .Columns(9).HeaderText = "PLZ"
            .Columns(10).HeaderText = "City"
            .Columns(10).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        End With
    End Sub

    Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
        Try
            FillDataGridView("select * from [edit$] where ID='" & TxtId.Text & "'")
            TxtFamilyname.Text = dt.Rows(0).Item(1)
            TxtGivenname.Text = dt.Rows(0).Item(2)
            TxtGender.Text = dt.Rows(0).Item(3)
            TxtDob.Text = dt.Rows(0).Item(4)
            TxtExamdate.Text = dt.Rows(0).Item(5)
            TxtExamtime.Text = dt.Rows(0).Item(6)
            TxtStreet.Text = dt.Rows(0).Item(7)
            TxtHouse.Text = dt.Rows(0).Item(8)
            TxtPlz.Text = dt.Rows(0).Item(9)
            TxtCity.Text = dt.Rows(0).Item(10)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
        End Try
    End Sub

    Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
        If vld(TxtId, TxtFamilyname, TxtGivenname, TxtGender, TxtDob, TxtExamdate, TxtExamtime, TxtStreet, TxtHouse, TxtPlz, TxtCity) = False Then
            Exit Sub
        Else

        End If

        Try
            With cm
                .Connection = cn
                .CommandText = "insert into [edit$]values('" & TxtId.Text & "','" & TxtFamilyname.Text & "','" & TxtGivenname.Text & "','" & TxtGender.Text & "','" & TxtDob.Text & "','" & TxtExamdate.Text & "','" & TxtExamtime.Text & "','" & TxtStreet.Text & "','" & TxtHouse.Text & "','" & TxtPlz.Text & "','" & TxtCity.Text & "' )"
                .ExecuteNonQuery()
            End With
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
            Return
        End Try
        MsgBox("Succefully Saved!", MsgBoxStyle.Information, Text)
    End Sub

    Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles Btnupdate.Click
        Try
            With cm
                .Connection = cn
                .CommandText = "Update [edit$] set [Family Name] = '" & TxtFamilyname.Text & "', [Given Name] = '" & TxtGivenname.Text & "',[Gender] = '" & TxtGender.Text & "',[DOB] = '" & TxtDob.Text & "',[Exam Date] = '" & TxtExamdate.Text & "',[Exam Time] = '" & TxtExamtime.Text & "',[Street Name] = '" & TxtStreet.Text & "',[House Nr] = '" & TxtHouse.Text & "',[PLZ] = '" & TxtPlz.Text & "',[CITY] = '" & TxtCity.Text & "' where ID = '" & TxtId.Text & "'"
                .ExecuteNonQuery()
            End With
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, Text)
            Return
        End Try
        MsgBox("Succesfully updated!", MsgBoxStyle.Information, Text)
    End Sub

    Private Sub BtnClose_Click(sender As Object, e As EventArgs) Handles BtnClose.Click
        Close()
    End Sub

    Private Sub BtnClear_Click(sender As Object, e As EventArgs) Handles BtnClear.Click
        TxtId.Clear()
        TxtFamilyname.Clear()
        TxtGivenname.Clear()
        TxtStreet.Clear()
        TxtHouse.Clear()
        TxtPlz.Clear()
        TxtCity.Clear()
        'To see all the data in DataGridView
        FillDataGridView("select * from[edit$]")

    End Sub

    Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click
        Try
            If Not DataGridView1.CurrentRow.IsNewRow Then
                With cm
                    .CommandText = "delete from [edit$] where ID='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
                    cn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source=C:\psave\New folder\save.xls;Extended Properties=Excel 8.0;"
                    cn.Open()
                    .ExecuteNonQuery()
                    DataGridView1.Rows.Remove(DataGridView1.CurrentRow)
                End With

                MsgBox("Succesfully Deleted!", MsgBoxStyle.Information, Text)
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, Text)

        End Try

    End Sub

End Class