Importing Excel Data in Datagridview then export into access db using VB.Net RRS feed

  • Question

  • Hello to all.
    I have code that import invoice data from excel file into Datagridview in What i tried to export this dgv data into access db. The problem is that i don't know how to write a code for exporting datagridview data into access db. My Access db Name "mydb.accdb" and table Name is 'myTable'.

    Please Help me to write code for me that fulfill my requirement. I'm very thankful to all of you for help.

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

    Public Class MainForm

    Private Sub BtnImpExcelFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnImpExcelFile.Click

    Dim conn As OleDbConnection Dim dta As OleDbDataAdapter Dim dts As DataSet Dim excel As String Dim OpenFileDialog As New OpenFileDialog OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls" If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then Dim fi As New FileInfo(OpenFileDialog.FileName) Dim FileName As String = OpenFileDialog.FileName excel = fi.FullName conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;") dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn) dts = New DataSet dta.Fill(dts, "[Sheet1$]") DGVImpData.DataSource = dts DGVImpData.DataMember = "[Sheet1$]" conn.Close()

    End If End Sub

    End Class

    TYSM for Future Help

    Saturday, September 14, 2019 8:29 PM

All replies

  • Hello,

    I will begin with, we are not here to write code for you although on occasion it will happen yet in this case I will provide a concept with code, but the code is not plug and play.

    Option 1, you can use a OleDbDataAdapter but rows will not be inserted unless the RowState of the row in not deleted or unchanged. Let's say all of the row's row state is unchanged, you can iterate each row and call SetAdded but can throw an exception (see the docs in the link for SetAdded).

    For a newcomer the following is what I would use for MS-Access (Note that SQL-Server is a good deal easier) is to iterate each DataRow in the DataTable and add them one by one. This is done by first creating parameters for a command object (cmd in this case) then iterate the rows and perfor and insert, get the primary key is optional but the code below does return the new primary key.

    The code presented comes from one of my GitHub repositories dedicated to working with MS-Access. Database connections were done with the NuGet package in my signature.

    The following method has the insert statement and the parameters

    ''' <summary>
    ''' Used to setup parameters for the method AddNewCustomerRecords
    ''' </summary>
    ''' <param name="cn">A OleDbConnection that has been created as in AddNewCustomerRecords</param>
    ''' <returns>Fully prepared command object</returns>
    Private Function AddRecordCommand(cn As OleDbConnection) As OleDbCommand
        Dim cmd As New OleDbCommand With {.Connection = cn}
        cmd.CommandText =
                INSERT INTO Customer 
        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@CompanyName", .DbType = DbType.String})
        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@ContactName", .DbType = DbType.String})
        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@EstablishedYear", .OleDbType = OleDbType.Integer})
        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@Incorporated", .OleDbType = OleDbType.Date})
        Return cmd
    End Function 

    Then the code responsible for iterating data rows and inserting the data.

    ''' <summary>
    ''' Add many customer records
    ''' </summary>
    ''' <param name="dataTable">DataTable</param>
    ''' <returns>True if successful, false on error</returns>
    Public Function AddMultipleRecords(dataTable As DataTable) As Boolean
        ' part of a NuGet package
        mHasException = False
        Dim affected As Integer = 0
            Using cn As New OleDbConnection With {.ConnectionString = "Your connection string goes here for ms-access database"}
                For Each row As DataRow In dataTable.Rows
                    ' Create a command for adding a new record. This must
                    ' be done each iteration as in the code to get the new primary
                    ' key the command is altered.
                    Dim cmdAdd = AddRecordCommand(cn)
                    cmdAdd.Parameters("@CompanyName").Value = row.Field(Of String)("CompanyName")
                    cmdAdd.Parameters("@ContactName").Value = row.Field(Of String)("ContactName")
                    cmdAdd.Parameters("@EstablishedYear").Value = row.Field(Of Integer)("EstablishedYear")
                    cmdAdd.Parameters("@Incorporated").Value = row.Field(Of DateTime)("Incorporated")
                    ' Add record
                    affected = cmdAdd.ExecuteNonQuery()
                    ' If affected equals 1, this means the record was added,
                    ' in turn get the new primary key by changing the command text.
                    ' No need to clear parameters.
                    If affected = 1 Then
                        cmdAdd.CommandText = "Select @@Identity"
                        row.SetField(Of Integer)("Id", CInt(cmdAdd.ExecuteScalar))
                    End If
            End Using
        Catch ex As Exception
            ' part of a NuGet package
            mHasException = True
            mLastException = ex
            Return False
        End Try
        ' part of a NuGet package
        Return IsSuccessFul
    End Function

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    Sunday, September 15, 2019 12:49 AM