Getting Error while uploading Excel data in to SQL Server table

Question

4.00/5 (2 votes)

See more:

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
---------

XML

<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 Code
------------

VB

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.Stored Procedure
------------------

SQL

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
-------------------

SQL

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

Posted 13-Sep-12 1:43am

gani7787

Updated 13-Sep-12 1:50am

__TR__

v2

Add a Solution

2 solutions

Solution 1

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

Posted 13-Sep-12 2:08am

Kuthuparakkal

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gani7787 · Accepted Answer · 2012-12-03T22:06:00

Solution 2

I have solved myself and this will be done

Posted 3-Dec-12 22:06pm

gani7787