在SQL Server 2005中将MS excel文件上载到我的数据库时出错 [英] Error while uploading MS excel file into my database in SQL server 2005

问题描述

当我直接在代码中指定文件目录时，它会正确地上传到我的数据库中的指定表中，下面的代码保存，文件地址直接放在代码中：

when i specify the file directory in the code direct, it uploads into the specified table in my database correctly,the code below is saving with the file address placed directly in the code:

Dim strConnection As String = "Data Source=Jamie-PC\SQLSERVER2005; initial catalog=StudentDB; persist security info=true;user id=sa;password=nash"
            
            Dim FullPath As String = "C:\Users\Narsh\Desktop\Excel Files\english pasco\english.xls"
            Dim MyPath As String = System.IO.Path.GetDirectoryName(FullPath)
            Dim myFilename As String = System.IO.Path.GetFileName(FullPath)
            
            Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FullPath + ";Extended Properties='Excel 8.0;';"
            Dim excelConnection As New OleDbConnection(excelConnectionString)
            Dim cmd As New OleDbCommand("Select * from [Sheet1$]", excelConnection)
            excelConnection.Open()
            Dim dReader As OleDbDataReader
            dReader = cmd.ExecuteReader()
            Dim sqlBulk As New SqlBulkCopy(strConnection)
            sqlBulk.DestinationTableName = "English2000"
            sqlBulk.WriteToServer(dReader)
            excelConnection.Close()

但是当我使用< asp：FileUpload ID =fileuploadExcelrunat =服务器/>在将文件上传到数据库之前将文件上传到fileupload控件中，它给出了以下错误：



Microsoft Jet数据库引擎无法打开文件'' ''。它已由其他用户专门打开，或者您需要获得查看其数据的权限。



以下代码给出了错误：

But when i use the <asp:FileUpload ID="fileuploadExcel" runat="server"/> to upload the file into the fileupload control before uploading it into the database, it gives me this error below:

"The Microsoft Jet database engine cannot open the file ''''. It is already opened exclusively by another user, or you need permission to view its data."

The code below is giving the error:

Dim strConnection As String = "Data Source=Jamie-PC\SQLSERVER2005; initial catalog=StudentDB; persist security info=true;user id=sa;password=nash"
            
            Dim path As String = fileuploadExcel.PostedFile.FileName
            
            Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;';"
            Dim excelConnection As New OleDbConnection(excelConnectionString)
            Dim cmd As New OleDbCommand("Select * from [Sheet1$]", excelConnection)
            excelConnection.Open()
            Dim dReader As OleDbDataReader
            dReader = cmd.ExecuteReader()
            Dim sqlBulk As New SqlBulkCopy(strConnection)
            sqlBulk.DestinationTableName = "English2000"
            sqlBulk.WriteToServer(dReader)
            excelConnection.Close()

请为什么需要紧急帮助？我正在使用excel 2003-2007格式

Please i need urgent help on why? i am using excel 2003-2007 format

推荐答案

，excelConnection）
excelConnection.Open（）
Dim dReader As OleDbDataReader
dReader = cmd.ExecuteReader（）
Dim sqlBulk 作为 新 SqlBulkCopy（strConnection）
sqlBulk.DestinationTableName = English2000
sqlBulk.WriteToServer（dReader）
excelConnection.Close（ ）

", excelConnection) excelConnection.Open() Dim dReader As OleDbDataReader dReader = cmd.ExecuteReader() Dim sqlBulk As New SqlBulkCopy(strConnection) sqlBulk.DestinationTableName = "English2000" sqlBulk.WriteToServer(dReader) excelConnection.Close()

但是当我使用< asp：FileUpload ID =fileuploadExcelrunat =服务器/>在将文件上传到数据库之前将文件上传到fileupload控件中，它给出了以下错误：



Microsoft Jet数据库引擎不能Ø写下文件''''。它已经由其他用户专门打开，或者您需要获得查看其数据的权限。



以下代码给出错误：

But when i use the <asp:FileUpload ID="fileuploadExcel" runat="server"/> to upload the file into the fileupload control before uploading it into the database, it gives me this error below:

"The Microsoft Jet database engine cannot open the file ''''. It is already opened exclusively by another user, or you need permission to view its data."

The code below is giving the error:

Dim strConnection As String = "Data Source=Jamie-PC\SQLSERVER2005; initial catalog=StudentDB; persist security info=true;user id=sa;password=nash"
            
            Dim path As String = fileuploadExcel.PostedFile.FileName
            
            Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;';"
            Dim excelConnection As New OleDbConnection(excelConnectionString)
            Dim cmd As New OleDbCommand("Select * from [Sheet1

，excelConnection）中选择*
excelConnection。 Open（）
Dim dReader As OleDbDataReader
dReader = cmd.ExecuteReader（）
Dim sqlBulk As 新 SqlBulkCopy（strConnection）
sqlBulk.DestinationTableName = English2000
sqlBulk.WriteToServer （dReader）
excelConnection.Close（）

", excelConnection) excelConnection.Open() Dim dReader As OleDbDataReader dReader = cmd.ExecuteReader() Dim sqlBulk As New SqlBulkCopy(strConnection) sqlBulk.DestinationTableName = "English2000" sqlBulk.WriteToServer(dReader) excelConnection.Close()

我为什么需要紧急帮助？我正在使用excel 2003-2007格式

Please i need urgent help on why? i am using excel 2003-2007 format

这是你想要用于.xlsx文件的另一个连接字符串：

Here is the other connection string you will want to use for .xlsx files:

"Provider=Microsoft.Ace.Oledb.12.0;Data Source=" + filepath + ";Extended Properties=" + "\"" + "Excel 12.0;" + "\"";

您已经拥有.xls文件的连接字符串。只需检查我上面说的扩展名，然后应用相应的连接字符串。



此外，请确保您使用的是完整文件路径，而不仅仅是您的名称连接字符串。

You already have the connection string for .xls files. Just check the extension as I said above and then apply the appropriate connection string.

Also, be sure you are using the full file path and not just the name in your connection string.

这篇关于在SQL Server 2005中将MS excel文件上载到我的数据库时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！