Cannot create an instance of OLE DB provider "MSDASC" error.

Hi

I'm Developing an application which will get the excel file from the user and read the data and finally it will store it in sql server 2008.

But it keep on throwing the above error when i try to open a excel file connection.

Here is my code:

Here is my connection String:

My Previous error was gone I run the below code:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

But now i'm getting following error:

Insert Error: Column name or number of supplied values does not match table definition.

Total column in excel is = 17
Total field in table = 17 +3=20
1(primarykeyid-autogenerate)+1(will get from another textbox)+1(defaulvalue 1)

i dont know how to pass textbox value and default value to procedure.

Please suggest me a way

Here is my stored procedure code

USE [Demo]
GO
/****** Object: StoredProcedure [dbo].[spx_ImportFromExcel07]    Script Date: 03/21/2014 18:25:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spx_ImportFromExcel07]

   @SheetName varchar(20),

   @FilePath varchar(300),

   @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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

AppDev icodetech12y ago
Here is my connection String:

My Previous error was gone I run the below code:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

But now i'm getting following error:

Insert Error: Column name or number of supplied values does not match table definition.

Total column in excel is = 17
Total field in table = 17 +3=20
1(primarykeyid-autogenerate)+1(will get from another textbox)+1(defaulvalue 1)

i dont know how to pass textbox value and default value to procedure.

Please suggest me a way

Here is my stored procedure code

USE [Demo]
GO
/****** Object: StoredProcedure [dbo].[spx_ImportFromExcel07]    Script Date: 03/21/2014 18:25:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spx_ImportFromExcel07]

   @SheetName varchar(20),

   @FilePath varchar(300),

   @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
12y ago
Hi,
did your connection string matches with following

excel 2003
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+fileName+";Extended Properties='Excel 8.0;HDR=Yes;'"
excel 2007
@"Provider=Microsoft.Jet.OLEDB.12.0;Data Source="+fileName+";Extended Properties='Excel 8.0;HDR=Yes;'"

if not try above
if it helpful to you, please accept as answer