Imports System.Data.OleDb
Imports System.Threading
Imports System.Runtime
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private ds As DataSet
    Private rw As Integer = 0

    Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFill.Click
        dgvTable.DataSource = Nothing
        Me.Label1.Visible = False
        Dim strcon As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0ata Source=C:\MyVBA\daotest.mdb;"
        Using con As OleDbConnection = New OleDbConnection(strcon)
            ds = New DataSet
            Dim cmd As OleDbCommand = con.CreateCommand()
            cmd.CommandText = "select * from BlocksTable"
            Dim da As New OleDbDataAdapter(cmd)
            da.Fill(ds, "BlocksTable"

        End Using
        Me.Label1.Visible = True
    End Sub

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

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Label1.Visible = False
    End Sub

    Public Sub AddRecord(ByVal numid As String, ByVal blockname As String, ByVal blockid As String, _
    ByVal x As String, ByVal y As String, ByVal z As String)
        Dim cnn As New OleDbConnection
        Dim cmd As New OleDbCommand
        Dim trans As OleDbTransaction = Nothing
        cnn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0ata Source=C:\MyVBA\daotest.mdb;"
        cnn.Open()
        Try

            cmd = cnn.CreateCommand
            trans = cnn.BeginTransaction(IsolationLevel.ReadCommitted)
            cmd.Connection = cnn
            cmd.Transaction = trans
            cmd.CommandText = "INSERT INTO BlocksTable " & _
"NumID,BlockName,BlockID,X_Coordinate,Y_Coordinate,Z_Coordinate) VALUES " & _
"?,?,?,?,?,?)"
            cmd.Parameters.Add("NumID", OleDbType.VarChar).Value = numid
            cmd.Parameters.Add("BlockName", OleDbType.Char).Value = blockname
            cmd.Parameters.Add("BlockID", OleDbType.Char).Value = blockid
            cmd.Parameters.Add("X_Coordinate", OleDbType.Char).Value = x
            cmd.Parameters.Add("Y_Coordinate", OleDbType.Char).Value = y
            cmd.Parameters.Add("Z_Coordinate", OleDbType.Char).Value = z

            cmd.ExecuteNonQuery()

        Catch ex As Exception
            trans.Rollback()

            If ex.GetType Is GetType(OleDbException) Then
                Dim dex As OleDbException = CType(ex, OleDbException)
                If dex.ErrorCode = -2147467259 Then
                    MessageBox.Show("uplicate value", "Adding Record", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                End If
            Else
                MessageBox.Show(ex.Message, "Adding Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If

        Finally
            cnn.Close()
        End Try
    End Sub


    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        rw = dgvTable.Rows.Count
        Dim crow As DataGridViewRow = Me.dgvTable.Rows(rw - 1)

        Dim numid As String = crow.Cells(0).ToString
        Dim blockname As String = crow.Cells(1).ToString
        Dim blockid As String = crow.Cells(2).ToString
        Dim x As String = crow.Cells(3).ToString
        Dim y As String = crow.Cells(4).ToString
        Dim z As String = crow.Cells(5).ToString

        AddRecord(numid, blockname, blockid, x, y, z)

    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Dim tbl As DataTable = Me.dgvTable.DataSource
        Dim fn As String = "C:\GridExport.xls"
        WriteToExcel(fn, tbl)
    End Sub
    Private Sub WriteToExcel(ByVal Filename As String, ByVal tbl As DataTable)

        Dim xlApp As Excel.Application = Nothing
        Dim xlbook As Excel.Workbook = Nothing
        Dim xlsheet As Excel.Worksheet = Nothing
        xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass()

        Try
            xlApp.Visible = True
            xlbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet)
            xlbook.Activate()

            For Each wrk As Excel.Worksheet In xlbook.Worksheets
                If wrk.Index > 1 Then wrk.Delete()
            Next

            xlsheet = xlbook.Sheets.Item(1)
            xlsheet.Select()
            xlsheet.Name = "ataGridViewSource"

            With xlbook.ActiveSheet
                For i As Integer = 0 To tbl.Rows.Count - 1
                    For j As Integer = 0 To tbl.Columns.Count - 1
                        .Cells(i + 1, j + 1) = tbl.Rows(i).Item(j)
                    Next
                Next
            End With
            xlsheet.UsedRange.Columns.AutoFit()
            xlbook.SaveAs(Filename)
            xlbook.Close()
            xlApp.Quit()

        Catch ex As Exception
            'Throw ex
            MessageBox.Show(ex.Message)
        Finally
            xlsheet = Nothing
            xlbook = Nothing
            xlApp = Nothing
            KillProc("excel"
        End Try

    End Sub
    Private Sub KillProc(ByVal procname As String)
        Try
            Dim procs() As Process = Process.GetProcesses()
            Dim proc As Process
            For Each proc In procs
                If proc.ProcessName.ToLower().Equals(procname) Then
                    proc.Kill()
                End If
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

End Class