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 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"
conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Exit Select
Case ".xlsx"
conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
Exit Select
End Select
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()
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"
CommandText = "spx_ImportFromExcel03"
Exit Select
Case ".xlsx"
CommandText = "spx_ImportFromExcel07"
Exit Select
End Select
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
------------------
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
-------------------
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