5 Replies - 702 Views - Last Post: 17 September 2019 - 10:21 AM Rate Topic: -----

#1 rajafaizan   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 13-September 19

Import Excel sheet to access database using vb.net

Posted 13 September 2019 - 11:15 AM

Hello to all.
I am developing software in vb.net for sales invoices. Where i need to import invoice data from excel file to vb.net Grid Data View and then save into access db. I will use db data in the future for creating customer invoices. I have code that import excel data into GDV successfully, but i don't know how to save data in access db. Access db Name "mydb.accdb" and table Name is 'myTable'. Please help me to write the correct code.
Thank You.

Imports System.Data.OleDb
Imports System.IO
Imports System.Data.SqlClient

Public Class MainForm

    Dim ds1 As New DataSet

    Dim ds2 As New DataSet

    Private Sub BtnQuickBookData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnQuickBookData.Click

        Dim _filename As String = "C:\Users\Faiza\Documents\myexcelsheet.xlsx"

        Dim _conn As String

        _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 16.0;"

        Dim _connection As OleDbConnection = New OleDbConnection(_conn)

        Dim da As OleDbDataAdapter = New OleDbDataAdapter()

        Dim _command As OleDbCommand = New OleDbCommand()

        _command.Connection = _connection

        _command.CommandText = "SELECT * FROM [Sheet1$]"

        da.SelectCommand = _command

        Try

            da.Fill(ds1, "sheet1")

            MessageBox.Show("The import is complete!")

            Me.DGVImpData.DataSource = ds1

            Me.DGVImpData.DataMember = "sheet1"

        Catch e1 As Exception

            MessageBox.Show("Import Failed, correct Column name in the sheet!")

        End Try

    End Sub

    Dim da As SqlDataAdapter

        Dim conn As SqlConnection

        Dim cb As SqlCommandBuilder

    Private Sub BtnEditImpData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEditImpData.Click

        conn = New SqlConnection("Data Source=Servername;Initial Catalog=mydb;Integrated Security=True")

        Dim sel As String = "SELECT * FROM myTable"

        da = New SqlDataAdapter(sel, conn)

        cb = New SqlCommandBuilder(da)

        da.MissingSchemaAction = MissingSchemaAction.AddWithKey

        da.Fill(ds2, "myTable")

        Me.DGVImpData.DataSource = ds2

        Me.DGVImpData.DataMember = "myTable"

    End Sub



    Private Sub BtnSaveImpData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSaveImpData.Click

        For Each dr As DataRow In ds1.Tables(0).Rows

            Dim expression As String

            expression = "myId =" + CType(dr.Item(0), Integer).ToString

            Dim drs() As DataRow = ds2.Tables(0).Select(expression)

            If (drs.Length = 1) Then

                For i As Integer = 1 To ds2.Tables(0).Columns.Count - 1

                    drs(0).Item(i) = dr.Item(i)

                Next

            Else

                Dim drnew As DataRow = ds2.Tables(0).NewRow

                For i As Integer = 0 To ds2.Tables(0).Columns.Count - 1

                    drnew.Item(i) = dr.Item(i)

                Next

                ds2.Tables(0).Rows.Add(drnew)

            End If

        Next

        Me.DGVImpData.DataSource = ds2

        Me.DGVImpData.DataMember = "myTable"

        da.Update(ds2.Tables(0))

    End Sub

End Class


Is This A Good Question/Topic? 0
  • +

Replies To: Import Excel sheet to access database using vb.net

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15262
  • View blog
  • Posts: 61,187
  • Joined: 12-June 08

Re: Import Excel sheet to access database using vb.net

Posted 13 September 2019 - 11:49 AM

What is your question?

Also - you would load your data into a DATASET object. Bind that to the grid.. _NOT_ load data into the grid directly.
Was This Post Helpful? 0
  • +
  • -

#3 rajafaizan   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 13-September 19

Re: Import Excel sheet to access database using vb.net

Posted 14 September 2019 - 09:33 AM

Hi modi123_1,
Thank You for help. This code import data from excel and save in access DB once, but when I tried to save data again it does not work. What I want a code that will import excel sheet data into GridDataView and then save data into access DB from GDV. I'm very thankful to you.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15262
  • View blog
  • Posts: 61,187
  • Joined: 12-June 08

Re: Import Excel sheet to access database using vb.net

Posted 14 September 2019 - 10:07 AM

Quote

but when I tried to save data again it does not work.


Explain this.

Remember folk here are not sitting over your shoulder. Be overly verbose on what is happening, what you are seeing, error messages, etc.
Was This Post Helpful? 0
  • +
  • -

#5 rajafaizan   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 13-September 19

Re: Import Excel sheet to access database using vb.net

Posted 17 September 2019 - 10:15 AM

View Postmodi123_1, on 14 September 2019 - 05:07 PM, said:

Quote

but when I tried to save data again it does not work.


Explain this.

Remember folk here are not sitting over your shoulder. Be overly verbose on what is happening, what you are seeing, error messages, etc.


Hi, Sorry for replay late.
When I import more data file from excel to access database. It does not work correctly. It shows Message ("Import Failed, correct Column name in the sheet!"). This code allow me only one time import data excel to access db using vb.net.
I hope this will be clear what you asked.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15262
  • View blog
  • Posts: 61,187
  • Joined: 12-June 08

Re: Import Excel sheet to access database using vb.net

Posted 17 September 2019 - 10:21 AM

I would check you are clearing variables, objects, etc after using them. Maybe just appending junk to an object being reused.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1