在OPENDATASOURCE函数中将DATA SOURCE值作为VARIABLE传递 [英] Passing DATA SOURCE value as VARIABLE in OPENDATASOURCE function

问题描述

嗨
我正在使用VB在ASP.NET中设计一个Web应用程序，并希望使用OpenDataSource函数将数据从Excel(.xls)导入到SQL Server 2008.我设计了一个表单，用户可以从中单击浏览"按钮并找到要导入的Excel文件.

我粘贴的代码粘贴在下面，以供您参考.如果DATA SOURCE是经过硬编码的，并且可以将数据从Excel导入SQL Server，则该代码可以正常工作.但是，当我在数据源中指定变量名称时，它给了我以下错误:

错误: 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.

由于用户将浏览Excel文件，该文件可以位于计算机上的任何位置，因此我希望DATA SOURCE值是一个变量.任何对此的帮助将不胜感激.

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

注意:

fileimport 是FILEUPLOAD控件

filepath 是一个变量，用于存储要导入的excel文件的绝对路径.

EXCEL文件导入代码:

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

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

亲切的问候

Muffazal Tikiwala

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

Kind Regards

Muffazal Tikiwala

推荐答案

".该表不存在，或者当前用户对该表没有权限.

由于用户将浏览Excel文件，该文件可以位于计算机上的任何位置，因此我希望DATA SOURCE值是一个变量.任何对此的帮助将不胜感激.

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

注意:

fileimport 是FILEUPLOAD控件

filepath 是一个变量，将在其中存储要导入的excel文件的绝对路径

EXCEL文件导入代码:

". 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 = 新建 SqlCommand(importquery，骗局) dtr = cmd.ExecuteReader con.Close() dtr.Close() 捕获，例如 As 异常 lblmsg.Text = " & ex.Message.ToString() 结束 尝试 其他 lblmsg.Text = " 结束 如果 其他 lblmsg.Text = " 结束 如果 结束 子

" 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

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

亲切的问候

穆法扎尔·蒂基瓦拉(Muffazal Tikiwala)

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

Kind Regards

Muffazal Tikiwala

这篇关于在OPENDATASOURCE函数中将DATA SOURCE值作为VARIABLE传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！