.NET (Core and Framework)

Imports System.Data.OleDb<br />
Imports System.Exception<br />
<br />
<br />
<br />
Public Class Form11<br />
<br />
    Public Sub Form11_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br />
<br />
        If Not String.IsNullOrEmpty("10.0.0.75\rprtaccess\garbage") Then<br />
<br />
            Dim OleDbConn As New OleDbHandler<br />
            Dim dgvOleDb1 As New Object<br />
            Dim ds As DataSet = OleDbConn.GetDataFromOleDb("10.0.0.75\rprtaccess\")<br />
<br />
            Dim OleCm As New OleDbCommand<br />
<br />
<br />
            If ds IsNot Nothing Then<br />
<br />
                Dim table As New DataTable<br />
                table.Locale = System.Globalization.CultureInfo.InvariantCulture<br />
<br />
                OleCm.CommandType = System.Data.CommandType.StoredProcedure<br />
                OleCm.CommandText = "Select Garbage"<br />
                Dim CM As Object = OleCm.CommandText<br />
<br />
<br />
                Dim OleDA As New OleDbDataAdapter()<br />
                ds = CM.SelectCommand.ExecuteReader<br />
<br />
<br />
<br />
                dgvOleDb1 = table.Select<br />
<br />
                dgvOleDb1.DataSource = DataGridViewEditMode.EditProgrammatically<br />
                dgvOleDb1.DataSource = ds.Tables(0)<br />
            End If<br />
        End If<br />
    End Sub<br />
<br />
    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvOleDb1.CellContentClick<br />
<br />
    End Sub<br />
    Public Sub ShowOleDbException()<br />
        Dim mySelectQuery As String = "SELECT column1 FROM table1"<br />
        Dim myConnection As New OleDbConnection _<br />
           ("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=")<br />
        Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)<br />
<br />
        Try<br />
            myCommand.Connection.Open()<br />
        Catch e As OleDbException<br />
            Dim errorMessages As String<br />
            Dim i As Integer<br />
<br />
            For i = 0 To e.Errors.Count - 1<br />
                errorMessages += "Index #" & i.ToString() & ControlChars.Cr _<br />
                               & "Message: " & e.Errors(i).Message & ControlChars.Cr _<br />
                               & "NativeError: " & e.Errors(i).NativeError & ControlChars.Cr _<br />
                               & "Source: " & e.Errors(i).Source & ControlChars.Cr _<br />
                               & "SQLState: " & e.Errors(i).SQLState & ControlChars.Cr<br />
            Next i<br />
<br />
            Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog()<br />
            log.Source = "My Application"<br />
            log.WriteEntry(errorMessages)<br />
            Console.WriteLine("An exception occurred. Please contact your system administrator.")<br />
        End Try<br />
    End Sub<br />
<br />
End Class

#Region " Import Section"<br />
Imports System<br />
Imports System.Collections.Generic<br />
Imports System.Text<br />
Imports System.Data<br />
Imports System.Data.OleDb<br />
Imports System.Reflection<br />
Imports System.Runtime.InteropServices<br />
Imports Microsoft.Office.Interop<br />
<br />
#End Region<br />
<br />
<br />
<br />
Public Class OleDbHandler<br />
<br />
    ' Return data in dataset from OleDb. '   <br />
    Public Function GetDataFromOleDb(ByVal a_sFilepath As String) As DataSet<br />
        Dim ds As New DataSet()<br />
        Dim oOleDbConnection As OleDbConnection<br />
        Dim sConnString As String = _<br />
            "Provider=IBMDA400.DataSource.1;" & _<br />
            "Data source=10.0.0.75;" ' & _<br />
        '"User Id=myUsername;" & _<br />
        ' "Password=myPassword"<br />
        oOleDbConnection = New OleDb.OleDbConnection(sConnString)<br />
        oOleDbConnection.Open()<br />
<br />
<br />
<br />
        Try<br />
            oOleDbConnection.Open()<br />
        Catch ex As OleDbException<br />
            Console.WriteLine(ex.Message)<br />
        Catch ex As Exception<br />
            Console.WriteLine(ex.Message)<br />
        End Try<br />
<br />
        ' It Represents  data table '<br />
        Dim dt As New System.Data.DataTable()<br />
        dt = oOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)<br />
        If dt IsNot Nothing OrElse dt.Rows.Count > 0 Then<br />
            For sheet_count As Integer = 0 To dt.Rows.Count - 1<br />
                Try<br />
                    ' Create Query to get Data from sheet. '<br />
                    Dim sheetname As String = dt.Rows(sheet_count)("table_name").ToString()<br />
                    Dim da As New OleDbDataAdapter("SELECT * FROM [" & sheetname & "]", sConnString)<br />
                    da.Fill(ds, sheetname)<br />
                Catch ex As DataException<br />
                    Console.WriteLine(ex.Message)<br />
                Catch ex As Exception<br />
                    Console.WriteLine(ex.Message)<br />
                End Try<br />
            Next<br />
        End If<br />
        oOleDbConnection.Close()<br />
        Return ds<br />
    End Function<br />
<br />
<br />
Private Function GetDataOleDb(ByVal a_dtData As System.Data.DataTable) As Object(,)<br />
        Dim obj As Object(,) = New Object((a_dtData.Rows.Count + 1) - 1, a_dtData.Columns.Count - 1) {}<br />
<br />
        Try<br />
            For j As Integer = 0 To a_dtData.Columns.Count - 1<br />
                obj(0, j) = a_dtData.Columns(j).Caption<br />
            Next<br />
<br />
            Dim dt As New DateTime()<br />
            Dim sTmpStr As String = String.Empty<br />
<br />
            For i As Integer = 1 To a_dtData.Rows.Count<br />
                For j As Integer = 0 To a_dtData.Columns.Count - 1<br />
                    If a_dtData.Columns(j).DataType Is dt.[GetType]() Then<br />
                        If a_dtData.Rows(i - 1)(j) IsNot DBNull.Value Then<br />
                            DateTime.TryParse(a_dtData.Rows(i - 1)(j).ToString(), dt)<br />
                            obj(i, j) = dt.ToString("MM/dd/yy hh:mm tt")<br />
                        Else<br />
                            obj(i, j) = a_dtData.Rows(i - 1)(j)<br />
                        End If<br />
                    ElseIf a_dtData.Columns(j).DataType Is sTmpStr.[GetType]() Then<br />
                        If a_dtData.Rows(i - 1)(j) IsNot DBNull.Value Then<br />
                            sTmpStr = a_dtData.Rows(i - 1)(j).ToString().Replace(vbCr, "")<br />
                            obj(i, j) = sTmpStr<br />
                        Else<br />
                            obj(i, j) = a_dtData.Rows(i - 1)(j)<br />
                        End If<br />
                    Else<br />
                        obj(i, j) = a_dtData.Rows(i - 1)(j)<br />
                    End If<br />
<br />
                Next<br />
            Next<br />
        Catch ex As Exception<br />
            Console.WriteLine(ex.Message)<br />
        End Try<br />
        Return obj<br />
    End Function<br />
<br />
End Class