OLEDB | The Blog Pros

July 1, 2020

VB.NET Custom import excel to Datagridview

Hello all!
I have an excel file that looks like this:

Time of Day Lap Lap Tm  Speed
14 - Stoicescu Dan - Regularitate Coupe         
9:40:50.835 1   2:47.462    88.763
9:43:37.584 2   2:46.749    89.142
9:46:33.319 3   2:55.735    84.584
9:49:58.901 4   3:25.582    72.304
9:53:13.988 5   3:15.087    76.194
9:55:50.639 6   2:36.651    94.889
9:58:49.884 7   2:59.245    82.928
10:02:33.328    8   3:43.444    66.524
20 - Dima Viorel - Regularitate Coupe           
9:40:56.287 1   2:46.284    89.392
9:43:39.673 2   2:43.386    90.977
9:46:18.075 3   2:38.402    93.84
9:48:57.580 4   2:39.505    93.191
9:51:38.104 5   2:40.524    92.599
9:54:18.371 6   2:40.267    92.748
9:57:01.825 7   2:43.454    90.939
9:59:51.986 8   2:50.161    87.355
10:02:33.040    9   2:41.054    92.295
10:05:35.524    10  3:02.484    81.456
10:08:55.828    11  3:20.304    74.209
12 - Cobori Stefan - Regularitate Sedan         
9:41:18.824 1   3:07.493    79.28
9:44:10.809 2   2:51.985    86.428
9:46:55.658 3   2:44.849    90.17

what i need is to import this in datagridview like this:

 NO NAME LAP TIME CLASS
    20  Dima Viorel 1   00:02:46.328    Coupe
    20  Dima Viorel 2   00:02:38.169    Coupe
    20  Dima Viorel 3   00:02:40.080    Coupe
    20  Dima Viorel 4   00:02:44.595    Coupe
    43  Caraban Bogdan  1   00:03:25.701    Cabrio
    43  Caraban Bogdan  2   00:02:45.295    Cabrio
    43  Caraban Bogdan  3   00:02:45.642    Cabrio
    14  Stoicescu Dan   1   00:03:06.488    Coupe
    14  Stoicescu Dan   2   00:02:49.431    Coupe

where no name and class must be extracted from single cell in excell see in first codeblock

 14 - Stoicescu Dan - Regularitate Coupe

the times and laps are in next rows after the row with the cell that contains the no name and class until next competitor

i know how to import in datagridview but i do not know how to parse
please help
Code for populate datagridview with excell sheet as it is:

Private Sub Button4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Retro Racing Et. 1 - Regularitate - laptimes.xls';Extended Properties='Excel 12.0;HDR=YES'")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select `Time of Day`,Lap,`Lap Tm` from [Sheet1$] ", MyConnection)

        MyCommand.TableMappings.Add("Table", "RAW")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView4.DataSource = DtSet.Tables(0)
        MyConnection.Close()

    End Sub

March 30, 2020

How to populate table from two different BindingSources to third?

Hi, I have one form with multiple reading data (using BindingSource) and I want to store those data + some text entry to third table (also using BindingSource DataGrid)

Is it possoble to do that using sql statement ? I have connection to database. Picture is to show what I have on my mind.

If someone can give me example, just as guidelines it would be great.

foe example, how to save into database
combobox1.text (PID)
textbox1.text (PVAL)
combobox3.text (Customer)

This is code on my form from attachment

Public Class Form2
    Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\invoicedb.accdb")

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Me.Close()
        Form1.Show()

    End Sub

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'InvoicedbDataSet.rac' table. You can move, or remove it, as needed.
        Me.RacTableAdapter.Fill(Me.InvoicedbDataSet.rac)

        Try
            con.Open()

            If con.State = ConnectionState.Open Then
                Label5.Text = "Connected"
            Else
                Label5.Text = "Not Connected"

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            Label5.Text = "Not Connected"
        Finally
            con.Close()

        End Try
        'TODO: This line of code loads data into the 'InvoicedbDataSet.invoice' table. You can move, or remove it, as needed.
        Me.InvoiceTableAdapter.Fill(Me.InvoicedbDataSet.invoice)
        'TODO: This line of code loads data into the 'InvoicedbDataSet.customer' table. You can move, or remove it, as needed.
        Me.CustomerTableAdapter.Fill(Me.InvoicedbDataSet.customer)
        'TODO: This line of code loads data into the 'InvoicedbDataSet.company' table. You can move, or remove it, as needed.
        Me.CompanyTableAdapter.Fill(Me.InvoicedbDataSet.company)

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs)

    End Sub
End Class

Thanks

April 17, 2019

Cannot open database (Unknown)

Hi all
I have a x32 bit application on a windows10 x64 machine.
The application was working fine untill I change the connection string from OLEDB to ODBC with a DSN.
Since my application x32 bit I created a x32 bit DSN to an ACCESS db wich also is a x32 (Office 365).
When I run the application I receive the error, cannot open database (Unknown) it may not be a database that your application recognizes.
I can oppen the Database from Access fine no pronblem, I also recreated the 32bit DSN dultiple times and the problem still precistes.