Click here to Skip to main content
13,352,408 members (37,861 online)
Rate this:
Please Sign up or sign in to vote.
I am designing a web application in ASP.NET using VB and would like to import data from Excel (.xls) to SQL Server 2008 using OpenDataSource function. I have designed a form, from where the user clicks the Browse button and locates the Excel file to be imported.

The code I have written is pasted below for your reference. The code works fine if the DATA SOURCE is hard coded and imports the data from Excel to SQL Server. But when I specify a variable name in DATA SOURCE it gave me the following error:

Error: The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Data1$". The table either does not exist or the current user does not have permissions on that table.

Since the user will be browsing for the Excel file which can be located anywhere on the machine, I want the DATA SOURCE value to be a variable. Any help regarding this will be greatly appreciated.



fileimport is a FILEUPLOAD Control

filepath is a variable where the absolute path of the excel file to be imported will be stored

Protected Sub txtimport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles txtimport.Click

If fileimport.HasFile Then
Dim fileextension As String
Dim filepath As String
fileextension = System.IO.Path.GetExtension(fileimport.FileName)
filepath = Server.MapPath(fileimport.FileName)

If (fileextension = ".xls") Or (fileextension = ".XLS") Then

        Dim con As SqlConnection
        Dim cmd As SqlCommand
        Dim dtr As SqlDataReader
        Dim importquery As String

        con = New SqlConnection("Server=localhost;Integrated Security = true;Database=RTS")

        importquery = "SELECT * INTO ABC FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=" & filepath & ";" & _
        "Extended Properties=Excel 8.0')...[Data1$]"

        cmd = New SqlCommand(importquery, con)
        dtr = cmd.ExecuteReader


    Catch ex As Exception

        lblmsg.Text = "Error: " & ex.Message.ToString()

    End Try
    lblmsg.Text = "Wrong File Extension"
End If


lblmsg.Text = "You have not selected a file"

End If

End Sub


Kind Regards

Muffazal Tikiwala
Posted 14-Aug-12 19:25pm
Updated 27-Sep-12 10:39am
Maciej Los261.3K

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.180111.1 | Last Updated 27 Sep 2012
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100