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