Passing DATA SOURCE value as VARIABLE in OPENDATASOURCE function

See more:

ASP.NET

SQL

server

data

Hi
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.

------------------------------------------------------------------------------------

NOTE:

fileimport is a FILEUPLOAD Control

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

EXCEL FILE IMPORT CODE:

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

    Try
        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")
        con.Open()

        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

        con.Close()
        dtr.Close()

    Catch ex As Exception

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

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

Else

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


End If

End Sub

------------------------------------------------------------------------------------

Kind Regards

Muffazal Tikiwala