DATA SOURCE value as VARIABLE in OPENDATASOURCE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

DATA SOURCE value as VARIABLE in OPENDATASOURCE

Author

Topic

muffazal
Starting Member

1 Post

Posted - 2012-08-15 : 01:33:02

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-15 : 07:30:26

Although in OPENDATASOURCE you cannot use variables in the arguments, what you are doing should work, because you are simply providing a string constant.

You may already have done this, but if not: Just before the statement shown below, print out or examine via debugger whether the variable importquery contains exactly the same string in the case where it works and in the case where it does not work:

cmd = New SqlCommand(importquery, con)

If it does, I don't see any reason why it would work in one case and wouldn't in another.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources