Unspecified error while connecting to Oledb

Question

User391606836 posted

 Hi

iam trying to read the excel file worksheet names.

For this am using oledbConnection with following code 

public ArrayList GetWorksheetNames(string filename)

{

OleDbConnection excelConn = null;

DataTable dataTable = null;

ArrayList excelSheetList = new ArrayList(); 

try

{

 string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";Extended Properties=Excel 8.0;";

excelConn = new OleDbConnection(connString);

 

excelConn.Open(); 

 

dataTable = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dataTable == null)

{

return null;

}foreach (DataRow row in dataTable.Rows)

{

excelSheetList.Add(row[TABLE_NAME"].ToString());

}

 But getting error as "Unspecified error" while trying to connect ie at excelConn.Open().

not able to get wat is the problem

Pls let me know the resolution asap

 

 

Answer 1

User2019981500 posted

 Hi,

you are not getting any error because you are not using OleDb Exception class any where

just use below source code and make sure you will display data from datatable

using System.Data.OleDb;
2using System.Data;
3
4public DataTable GetOLETable(string FileLocation, string strSelect, CommandType cmdType, string sIncludeHeader)
5        {
6            DataTable dt = new DataTable();
7            OleDbDataAdapter da = new OleDbDataAdapter();
8            saveFileLocation = saveFileLocation.Replace("\\\\", "\\");
9            string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileLocation + ";" + "Extended Properties='Excel 8.0; HDR=" + sIncludeHeader + "; IMEX=1';";
10            OleDbConnection objConn = new OleDbConnection(sConnectionString);
11            try
12            {
13                objConn.Open();
14
15                OleDbCommand objCmdSelect = new OleDbCommand(strSelect, objConn);
16                da.SelectCommand = objCmdSelect;
17                da.Fill(dt);
18                objConn.Close();
19                objConn.Dispose();
20                return dt;
21
22            }
23            catch (OleDbException ex)
24            {
25                objConn.Close();
26                objConn.Dispose();
27                throw (ex);
28            }
29            catch (Exception ex)
30            {
31                objConn.Close();
32                objConn.Dispose();
33                throw (ex);
34            }
35            finally
36            {
37                objConn.Close();
38                objConn.Dispose();
39            }
40
41        }


Then call or use function like below

string sTemp = "select * from [SheetName$B1:M16]";
DataTable dtOLE = GetOLETable("C:\\test.xls", sTemp, CommandType.Text, "No");

 

yes here is alternative http://www.sharpprogrammer.com/dotnet/how-to-read-excel-file-in-c-net/

Answer 2

User-68639941 posted

 try using the below code

  StringBuilder sbConn = new StringBuilder();
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filePath);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));

Answer 3

User391606836 posted

Thanks ..but i was able to it by my self by doing following steps

1 .Open inetmgr.

2.Right click on the project and select proeprties.

3.Select Directory security tab and click Edit button

4.Uncheck anonymus access

5.click Ok

An it worked.

 

Answer 4

User-137212317 posted

try

<identity impersonate="true" />

 

in your web.config file