Import Excel data into SQL Server 2005 though vb.net

Hi,
I  need to import excel spread sheets in to  sql server 2005 tables through  front end application (VB.NET 2008)  and show the  data in a datagridview.  I want to have a best method to do it. If anybody can help me on this, it would be great.

Thanks
JOLEJEJE
JOLEEJJAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
Please check the following KB article:
http://www.knowdotnet.com/articles/exceldatasource.html

Display data from sql in datagridview can be something like this:
Dim myConnectionString As String = "...."
Dim connection As New SqlConnection(myConnectionString) 
Dim SQL As String = "SELECT * FROM Table" 
da = New SqlDataAdapter(SQL, connection) 
ds = New DataSet 
da.Fill(ds, "myTable") 
Me.DataGridView1.DataSource = ds.Tables("myTable") 
connection = Nothing 

Open in new window

JOLEEJJAuthor Commented:
Hi om prakash,

I spent hours to fix the problem of isam error in excel.  However, I fixed it but now I get the following error message "Syntax Error in FROM Clause" while filling the dataset. I am not used C# and therefore, I had spent time also on converting the code to VB. It is Ok.  Following is my vbcode.  Please check where I have gone wrong.

Public Sub JoeImportExcel(ByVal strFile)

        If Not File.Exists(strFile) = True Then
            MessageBox.Show("File does not exist...!!!")
            Exit Sub
        End If

        Dim JoeExcelConString As String
        'JoeExcelConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEST\TESTING.XLS;Extended Properties=Excel 11.0;"
        JoeExcelConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=YES;"""
        Dim SheetName As String = "Sheet1"
        Dim JoeExcelCon As New OleDbConnection

        JoeExcelCon.ConnectionString = JoeExcelConString

        Try
            JoeExcelCon.Open()
            Dim JoeExcelAda As New OleDbDataAdapter("SELECT * FROM Sheet1$", JoeExcelCon)
            Dim ExcelData As New DataSet()
            JoeExcelAda.Fill(ExcelData)

        Catch Olex As OleDb.OleDbException
            MessageBox.Show("Err No: " & Olex.ErrorCode & "  Err Msg: " & Olex.Message, "Excel Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally
            If JoeExcelCon.State = ConnectionState.Open Then
                JoeExcelCon.Close()
            End If

        End Try

    End Sub

Once I get success in this code, then I may be able to populate them in a datagridview.

Please help me on this issue.

JOLEEJEJE



Om PrakashCommented:
change
Dim JoeExcelAda As New OleDbDataAdapter("SELECT * FROM Sheet1$", JoeExcelCon)
to
Dim JoeExcelAda As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", JoeExcelCon)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.