Import Excel data into SQL Server 2005 though vb.net

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 

Select allOpen in new window

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