System.Data.OleDb.OleDbException: Unspecified error

I was trying to access the excel sheet using  Oledb, but as soon as try to open the connection to that excel file i got error like "UnSpecified error"... tried with different drivers and nothing worked. Tried giving full access to 'Everyone' for the particular folder but nothing solved the issue.



the sample code is....

  if (_fullFilePath.EndsWith(".csv", StringComparison.CurrentCultureIgnoreCase))

                {

                    var dirPath = Path.GetDirectoryName(FullFilePath);

                    var fileName = Path.GetFileName(FullFilePath);

                    CreateCsvSchemaIniFile(dirPath, fileName);

                    //_dbConnection = new OleDbConnection(

                    //    String.Format(

                    //        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\;Extended Properties='text;HDR=Yes;FMT=Delimited'",

                    //        dirPath));

                     string strConnString= "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dirPath+";Extensions=asc,csv,tab,txt; Persist Security Info=False";

                   //  string strConnString= "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dirPath+";Extensions=asc,csv,tab,txt;Persist Security Info=False";

                     System.Data.Odbc.OdbcConnection _dbConnection;

                     _dbConnection = new System.Data.Odbc.OdbcConnection(

                                    strConnString.Trim());

                    _dbConnection.Open();

                    strReaderQuery = CSV_QUERY + "[" + fileName + "]"; // kaf causes error when file name has spaces

After lot of research i found that, it was a problem with impersonation issue. I have set impersonation to true to fix some other problem and that was the issue here. When we read data from  excel file , these data will be stored in temp folder and these folders will be accessible only by ASPNET  account. So, if you use impersonation it will be overridden and that cause the issue. Just changed the impersonation to false and that solved the issue. Now the temp folder is having the proper authorization and i got rid of the issue. This is not only specific to reading excel file, whenever you read something in systems memory, make sure that you don't override the impersonation.

cascade delete - Deleting the foreign keys tables without giving the child table names.

Have you ever faced a situation where you need to delete all your master table data along with its corresponding transaction table records. I faced few days ago and got a solution to delete the records with foreign keys references. The below code worked for me for cascade delete, if its not working for you pls let me know with the error u get.


declare  @cTab varchar(255),  
 @cCol varchar(255),  
 @cRefTab varchar(255),  
 @cRefCol varchar(255),  
 @cFKName varchar(255),  
 @cSQL nvarchar(1000),  
 @cChildCriteria nvarchar(1000),  
 @iChildRows int  

 
DECLARE cFKey CURSOR LOCAL FOR  
SELECT SO1.name AS Tab,  
       SC1.name AS Col,
       SO2.name AS RefTab,  
       SC2.name AS RefCol,  
       FO.name AS FKName  
FROM dbo.sysforeignkeys FK    
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id  
                              AND FK.fkey = SC1.colid  
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id  
                              AND FK.rkey = SC2.colid  
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id  
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id  
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id  
WHERE SO2.Name = @cTableName  

OPEN cFKey  
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName  
WHILE @@FETCH_STATUS = 0  
     BEGIN  
   SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +  
           @cRefTab +'] WHERE ' + @cCriteria + ')'  

     
   EXEC [sp_CascadeDelete] @cTab, @cChildCriteria  
   FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName  
     END  
Close cFKey
DeAllocate cFKey  

SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria  
EXEC sp_ExecuteSQL @cSQL