I want to upload excel file to my MS access using VB.NET

Question

0.00/5 (No votes)

See more:

Good day everyone!

I try this code for uploading excel file and i want to save it to my MS ACCESS Database, i really dont know whats the next step. Can you help me. Please refer to my codes. Thank you so much

What I have tried:

Imports System.Data.OleDb
Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim sFileName As String = "C:\Users\Administrator\Desktop\Book1.xls"
        Dim sConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES';", sFileName)
        Dim dt As DataTable = New DataTable()

        '1.
        Using connection As OleDbConnection = New OleDbConnection(sConStr)
            Dim sql As String = "SELECT * FROM [Sheet1$] WHERE UserName Like @UserName;"
            connection.Open()
            '2.
            Using command As OleDbCommand = New OleDbCommand(sql, connection)
                command.Parameters.AddWithValue("@UserName", "%j_L%")
                '3.
                Dim reader As OleDbDataReader = command.ExecuteReader()
                '4. 
                dt.Load(reader)
                reader.Close()
                reader.Dispose()
            End Using
        End Using
        '5.
        Me.DataGridView1.DataSource = dt
    End Sub

End Class

Posted 24-Jan-19 16:27pm

Programmerprogrammeranlang25

Updated 24-Jan-19 17:33pm

Add a Solution

Comments

san2debug 24-Jan-19 22:34pm

Do you want to save the data from excel sheet to MS access database? What kind of issue your facing from your code and let me know how i can help you

Programmerprogrammeranlang25 24-Jan-19 22:40pm

after i upload my excel file to datagridview, I just want to save it to my ms access database using vb.net.

Programmerprogrammeranlang25 24-Jan-19 22:41pm

I don't have any issue in my code, I just want to know my next step or code that will save my datagridview to my MS Access.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2019-01-24T17:31:00

There is an older article here on CodeProject that will show you the basics. It will need some updating; like replacing the JET 4.0 connection provider and replacing itwith ACE 12- the same as your source excel file.
Bulk Record Insert for Access[^]

Google has lots of resources for save datatable to access database c#.

san2debug · Answer 2 · 2019-01-24T17:33:00

Hi,

I hope you want to know how to save your Datagridview data to your MS Access database using VB.Net. you can place one button in your windows forms then you can write insert operation inside the button click event the following code

Private Sub btnInsert_Click(sender As System.Object, e As System.EventArgs) Handles btnInsert.Click
	For Each row As DataGridViewRow In dataGridView1.Rows
		
		Dim sFileName As String = "C:\Users\Administrator\Desktop\Book1.xls"
		Dim sConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES';", sFileName)
		
		Using connection As OleDbConnection = New OleDbConnection(sConStr)
			Dim sql As String = "INSERT INTO [Sheet1$] VALUES(@ColParameter1, @ColParameter2, @ColParameter3;"
			Using command As OleDbCommand = New OleDbCommand(sql, connection)
				command.Parameters.AddWithValue("@ColParameter1", row.Cells("ColumnName1").Value)
				command.Parameters.AddWithValue("@ColParameter2", row.Cells("ColumnName2").Value)
				command.Parameters.AddWithValue("@ColParameter3", row.Cells("ColumnName3").Value)
				connection.Open()
				command.ExecuteNonQuery()
				connection.Close()
			End Using
		End Using			
	Next
		 MessageBox.Show("Records added successfully.")
End Sub

Recommended:

1, you don't write connection string and file path for each method inside instead of that you can write the common declaration in the top of the class.

2, you can open the connection object before executing a query or execute reader then close it after executing a query or execute reader.

I hope it will help you

I want to upload excel file to my MS access using VB.NET

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0