Niyas

Niyas

  • NA
  • 1
  • 0

Error using stored procedure with Asp.Net

May 11 2009 6:26 AM
I am using asp.net 1.0
I have created a stored procedure to upload an excel file to sql server 2000 and it works fine in SQL query analyzer (i can import the file to sql)
the code is given below.

create procedure importexcel(@tablename varchar(10),@xlpath varchar(100),@sheetname varchar(10)) as
declare @SQL varchar(1000)
select @SQL ='insert into ' + @tablename +
'(c1,c2,c3) select * from ' + ' OpenRowSet(''MSDASQL'' '+
', ''Driver={Microsoft Excel Driver (*.xls)};'+ 'DBQ=' + @xlpath + '''' +
',''SELECT col1,col2,col3 FROM [' + @sheetname + ']'')'
exec(@SQL)
GO


but while i access it through asp.net, it returns error :-
System.Data.SqlClient.SqlException: OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Failure creating file.]

code given below
            string stno = "table1";
            string pth = "c:\test\test.xls";
            string sna = "Sheet1$";
            ArrayList inpar = new ArrayList();
            inpar.Add("@tablename");
            inpar.Add("@xlpath");
            inpar.Add("@sheetname");
            ArrayList inval = new ArrayList();
            inval.Add(stno);
            inval.Add(pth);
            inval.Add(sna);
            cmd1.Connection= con.getconn();
            cmd1.CommandText = "importexcel";
            cmd1.CommandType = CommandType.StoredProcedure;
            for(int i=0;i<inpar.Count;i++)
            {
                cmd1.Parameters.Add(inpar[i].ToString(),inval[i].ToString());
            }
            SqlParameter par1 = new SqlParameter();
            par1.Direction = ParameterDirection.Input;
            cmd1.ExecuteNonQuery();
}

Please help me........... its very urgent