[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Import Excel using VB 2005 into SQL by button click

Posted on 2007-08-02
5
Medium Priority
?
541 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:hatforce
  • 2
3 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19616905
1) Use OleDb to read the Excel file into a DataTable

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

Bob
0
 

Author Comment

by:hatforce
ID: 19617153
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""")
        ExcelConnection.Open()
        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)

        ExcelCommand.ExecuteNonQuery()
        ExcelConnection.Close()
    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.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 19617788
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
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question