您的位置: 首页  >  技术问答  >  将Excel数据上载到SQL Server表时出错

将Excel数据上载到SQL Server表时出错

分类: 技术问答 • 2022-07-22 17:53:07
问题描述：

我正在尝试将Excel数据上传到SQL服务器。



但是，我在该代码中收到错误。



错误如下。

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

已拒绝对OLE DB提供程序Microsoft.Jet.OLEDB.4.0的临时访问。您必须通过链接服务器访问此提供程序。



如何解决这个问题。因为我在SQL Server中的访问权限非常有限。



我可以使用ASP.NET将任何其他代码上传到SQL Server ..？



ASPX代码

---------

I am trying to upload excel data into SQL server.

But, i am getting error in that code.

Error is below.
---------------
Ad hoc access to OLE DB provider ''Microsoft.Jet.OLEDB.4.0'' has been denied. You must access this provider through a linked server.

How to solve this problem. because i have very limited access in SQL Server.

Can i have any other code to upload excel to SQL Server using ASP.NET..?

ASPX Code
---------

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Import Excel Data into Database</title>
</head>
<body>
    <form id="form1" runat="server">
<asp:Panel ID="Panel1" runat="server">
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="btnUpload" runat="server" Text="Upload"

             OnClick="btnUpload_Click" />
    <br />
    <asp:Label ID="lblMessage" runat="server" Text="" />
</asp:Panel>
<asp:Panel ID="Panel2" runat="server" Visible = "false" >
    <asp:Label ID="Label5" runat="server" Text="File Name"/>
    <asp:Label ID="lblFileName" runat="server" Text=""/>
    <br />
    <asp:Label ID="Label2" runat="server" Text="Select Sheet" />
    <asp:DropDownList ID="ddlSheets" runat="server"

                    AppendDataBoundItems = "true">
    </asp:DropDownList>
    <br />
    <asp:Label ID="Label3" runat="server" Text="Enter Source Table Name"/>
    <asp:TextBox ID="txtTable" runat="server"></asp:TextBox>
    <br />
    <asp:Label ID="Label1" runat="server" Text="Has Header Row?" />
    <br />
    <asp:RadioButtonList ID="rbHDR" runat="server">
        <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >
        </asp:ListItem>
        <asp:ListItem Text = "No" Value = "No"></asp:ListItem>
    </asp:RadioButtonList>
    <br />
    <asp:Button ID="btnSave" runat="server" Text="Save"

          OnClick="btnSave_Click" />
    <asp:Button ID="btnCancel" runat="server" Text="Cancel"

          OnClick="btnCancel_Click" />
 </asp:Panel>
    </form>
</body>
</html>





ASPX.Vb代码

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



ASPX.Vb Code
------------

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class VB
    Inherits System.Web.UI.Page

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If FileUpload1.HasFile Then
            Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
            Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")

            Dim FilePath As String = Server.MapPath(FolderPath + FileName)
            FileUpload1.SaveAs(FilePath)
            GetExcelSheets(FilePath, Extension, "Yes")
        End If
    End Sub
    Private Sub GetExcelSheets(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)
        Dim conStr As String = ""
        Select Case Extension
            Case ".xls"
                'Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
                Exit Select
            Case ".xlsx"
                'Excel 07
                conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
                Exit Select
        End Select

        'Get the Sheets in Excel WorkBoo
        conStr = String.Format(conStr, FilePath, isHDR)
        Dim connExcel As New OleDbConnection(conStr)
        Dim cmdExcel As New OleDbCommand()
        Dim oda As New OleDbDataAdapter()
        cmdExcel.Connection = connExcel
        connExcel.Open()

        'Bind the Sheets to DropDownList
        ddlSheets.Items.Clear()
        ddlSheets.Items.Add(New ListItem("--Select Sheet--", ""))
        ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        ddlSheets.DataTextField = "TABLE_NAME"
        ddlSheets.DataValueField = "TABLE_NAME"
        ddlSheets.DataBind()
        connExcel.Close()
        txtTable.Text = ""
        lblFileName.Text = Path.GetFileName(FilePath)
        Panel2.Visible = True

        Panel1.Visible = False
    End Sub

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim FileName As String = lblFileName.Text
        Dim Extension As String = Path.GetExtension(FileName)
        Dim FolderPath As String = Server.MapPath( _
           ConfigurationManager.AppSettings("FolderPath"))
        Dim CommandText As String = ""
        Select Case Extension
            Case ".xls"
                'Excel 97-03
                CommandText = "spx_ImportFromExcel03"
                Exit Select
            Case ".xlsx"
                'Excel 07
                CommandText = "spx_ImportFromExcel07"
                Exit Select
        End Select
        'Read Excel Sheet using Stored Procedure
        'And import the data into Database Table
        Dim strConnString As String = ConfigurationManager _
          .ConnectionStrings("conString").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = CommandText
        cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value = ddlSheets.SelectedItem.Text
        cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = FolderPath + FileName
        cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value = rbHDR.SelectedItem.Text
        cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = txtTable.Text
        cmd.Connection = con
        Try
            con.Open()
            Dim count As Object = cmd.ExecuteNonQuery()
            lblMessage.ForeColor = System.Drawing.Color.Green
            lblMessage.Text = count.ToString() & " records inserted."
        Catch ex As Exception
            lblMessage.ForeColor = System.Drawing.Color.Red
            lblMessage.Text = ex.Message
        Finally
            con.Close()
            con.Dispose()
            Panel1.Visible = True
            Panel2.Visible = False
        End Try
    End Sub
    Protected Sub btnCancel_Click(ByVal sender As Object, ByVal e As EventArgs)
        Panel1.Visible = True
        Panel2.Visible = False
    End Sub

End Class







1.存储过程

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




1.Stored Procedure
------------------

CREATE PROCEDURE spx_ImportFromExcel03
	@SheetName varchar(20),
    @FilePath varchar(100),
	@HDR varchar(3),
    @TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
		
	IF OBJECT_ID (@TableName,'U') IS NOT NULL
		SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
	ELSE
		SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

    SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 8.0;HDR=' 
	SET @SQL = @SQL + @HDR + ''''''')...[' 
    SET @SQL = @SQL + @SheetName + ']'
	EXEC sp_executesql @SQL
END
GO





2.Stored Procedure

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



2.Stored Procedure
-------------------

CREATE PROCEDURE spx_ImportFromExcel07
	@SheetName varchar(20),
    @FilePath varchar(100),
	@HDR varchar(3),
	@TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
	
	IF OBJECT_ID (@TableName,'U') IS NOT NULL
		SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
	ELSE
		SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL = @SQL + @HDR + ''''''')...[' 
    SET @SQL = @SQL + @SheetName + ']'
	EXEC sp_executesql @SQL
END
GO

答

Go with this buddy:

http://code.msdn.microsoft.com/office/Imoprt-Data-from-Excel-to-705ecfcd[^]
Go with this buddy:
http://code.msdn.microsoft.com/office/Imoprt-Data-from-Excel-to-705ecfcd[^]


I have solved myself and this will be done
I have solved myself and this will be done


相关推荐