Error - string does not contain a definition for GetOleDbSchemaTable and no extension method GetOleDbSchemaTable accepting a first argument of type string could be found.

Question

User-1499457942 posted

Hi

  I have below code on which it gives above error

DataTable dtExcelSchema; dtExcelSchema = excelConnectionString.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"];

Thanks

Answer 1

User753101303 posted

Hi,

As pointed by the error message you are trying to call this method direcltly on a string (which happens to be a connection string).

You need to have something such as :

var excelConnection=new OleDbConnection(excelConnectionString);
DataTable dtExcelSchema; dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

Answer 2

User-1171043462 posted

This is code from my Article. You are trying to access function of a OledbCommand in String.

Here's full code

String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=Book1.xls;"
+ "Extended Properties='Excel 8.0;HDR=Yes'";
 
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel = new OleDbCommand();
cmdExcel.Connection = connExcel;


connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();

Also you can download sample from my article: Read and Import Excel Sheet using ADO.Net and C#

Answer 3

User-1499457942 posted

Hi

  I want first sheet . It is giving me the no of 4th sheet. Below is the code

OleDbConnection connExcel = new OleDbConnection(excelConnectionString);
                OleDbCommand cmdExcel = new OleDbCommand();
                cmdExcel.Connection = connExcel;

                connExcel.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                connExcel.Close();

                string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

Thanks

Answer 4

User753101303 posted

List all names to see if they are listed in the reverse order ? Is the sheet position the best criteria to find out the one you want? I('m not sure I would be really confident that sheets are listed in a particular order. Move then the Excel sheet and see what happens. Maybe it uses an alphabetical order ???

You could expect a known sheet name, test maybe which columns you have or ultimately ask the user which one should be processed ?

Answer 5

User-1499457942 posted

Hi

  Yes i also think that it uses sorted order but i want first sheet

Thanks

Answer 6

User-821857111 posted

i want first sheet

There is no guarantee that the worksheets returned by the GetOleDbSchemaTable method are in any particular order. You should iterate the worksheets and present a list on the web page for the user to select the one that needs to be processed.

Answer 7

User753101303 posted

So it seems you don't have access to the actual order in the Excel file.

You really don't have any other criteria than the position? If importing data, you expect known columns ?

Else you would have to use (or complement this) with the OpenXML SDK so that you can get this sheet ordering information.

Answer 8

User-1171043462 posted

So add a break point and check the Sheets in DataTable based on adjust index

Answer 9

User-1171043462 posted

But this is a different question, you can ask a new question and close this one by marking Answer