Import Excel using VB 2005 into SQL by button click

I am working on a front-end for a SQL database.  I'm using VB 2005.  The SQL server is 2000.  I need a solution for a user to click on a button to bring up a file open window.  Then the user will navigate to the Excel file, which needs to be imported into SQL.  The Excel file will have the same fields as the SQL database.  It's just the file name will be different each time so they have to navigate to the correct file through the file open window.  Once the user chooses the file and click on open.  I need the Excel file to be imported into the table on the SQL server.  Any help is appreciated very much.
Who is Participating?
Bob LearnedConnect With a Mentor Commented:
You are going to have to read the values into a DataTable, using and OleDbDataAdapter, and then use the SqlBulkCopy to quickly add the records to SQL Server.

Bob LearnedCommented:
1) Use OleDb to read the Excel file into a DataTable

2) Use the SqlBulkCopy to insert the data into the SQL Server table.

hatforceAuthor Commented:
I guess I need to work on the code to actually import the data first before moving onto creating a file open window for the users.  

Ok.  I have the following code to open a specific file on my C:\ drive:

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Product Add Spreadsheettest.xls;Extended Properties=""Excel 8.0;HDR=Yes""")
        Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=(local);Data Source=A\DATASERVER;Initial Catalog=DATA1]; SELECT * FROM [DATA Add];", ExcelConnection)

    End Sub

 When I try to click the button I get an error "OleDbException was unhandled"  Syntex error in INSERT INTO statement.  The highlighted error references to "ExcelCommand.ExecuteNonQuery()".  

My SQL server is local (for now) it will be remote when the app is launched for production.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.