GetOleDbSchemaTable bug (!) not all columns

Discussion in 'ASP .Net' started by Radu, May 13, 2008.

  1. Radu

    Radu Guest

    Hi. I'm trying to read the structure of some Excel 2000 file that the
    user is expected to upload. The file must be an excel file (solved)
    and it must contain a column named "PIN". That's all. Now about the
    column name.

    I have the code:

    cnExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=" & strTableName & ";Extended Properties=""Excel
    8.0;HDR=Yes;IMEX=1"";"
    cnExcel.Open()

    'Retrieve schema information about the excel file (i.e the name of the
    first sheet in the worksheet).
    schemaTable = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New
    Object() {Nothing, Nothing, Nothing, "TABLE"})
    strSheetName = schemaTable.Rows(0).Item("Table_Name").ToString

    'Retrieve the collection of columns in this excel file...
    schemaTable = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New
    Object() {Nothing, Nothing, strSheetName, Nothing})

    'Check if we have at least the "PIN" column:
    If schemaTable.Select("COLUMN_NAME='PIN'").GetUpperBound(0) < 0 Then
    .....

    I have tried with multiple files. Sometimes it
    - returns schemaTable.Rows.Count=0 (no columns !!!)
    - returns all columns, and I can see them nicely in the immediate
    window
    - returns only some columns - say the file has 5 columns, but I can
    only see 4 - schemaTable.Rows.Count = 4 and ?schemaTable.Rows(4)!
    COLUMN_NAME.ToString tells me that this index does not exist

    I have tried moving the PIN column back and forth in the excel file,
    and to my astonishment.... sometimes it works.

    Example:

    Col1 PIN Col2 Status
    1 1 63 AAA
    2 1 27 AAA
    3 1 82 AAA
    3 1 40 AAA
    4 1 52 AAA
    1 1 5 AAA
    2 1 11 AAA
    3 1 11 AAA
    3 1 17 AAA

    gives
    ?schemaTable.Rows(0)!COLUMN_NAME.ToString
    "Col1"
    ?schemaTable.Rows(1)!COLUMN_NAME.ToString
    "Col2"
    ?schemaTable.Rows(2)!COLUMN_NAME.ToString
    "PIN"
    ?schemaTable.Rows(3)!COLUMN_NAME.ToString
    Run-time exception thrown : System.IndexOutOfRangeException - There is
    no row at position 3.
    (No Status ???)



    Col1 Col2 Status PIN
    1 653 AAA 1
    2 27 AAA 1
    3 892 AAA 1
    3 40 AAA 1
    4 52 AAA 1
    1 5 AAA 1
    2 151 AAA 1
    3 11 AAA 1
    3 1472 AAA 1




    ?schemaTable.Rows(0)!COLUMN_NAME.ToString
    "Col1"
    ?schemaTable.Rows(1)!COLUMN_NAME.ToString
    "Col2"
    ?schemaTable.Rows(2)!COLUMN_NAME.ToString
    Run-time exception thrown : System.IndexOutOfRangeException - There is
    no row at position 2.
    (No PIN ??? No Status ??? Now two columns dropped ???)

    This one


    Col1 SS_Col2 Col3 PIN SS_Col4
    1 5 AAA 1 2
    2 6 AAA 1 3
    3 7 AAA 1 4
    3 8 AAA 1 5
    4 9 AAA 1 6
    1 10 AAA 1 7
    2 11 AAA 1 8
    3 12 AAA 1 9
    3 13 AAA 1 10

    gives

    ?schemaTable.Rows(0)!COLUMN_NAME.ToString
    "Col1"
    ?schemaTable.Rows(1)!COLUMN_NAME.ToString
    "SS_Col2"
    ?schemaTable.Rows(2)!COLUMN_NAME.ToString
    Run-time exception thrown : System.IndexOutOfRangeException - There is
    no row at position 2.

    .... and so on. I have tried many combinations. This situation doesn't
    have neither rhyme nor reason. Can somebody hep me, please ?

    Thanks a lot.
    Alex
    Radu, May 13, 2008
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. pete
    Replies:
    1
    Views:
    2,915
    Jon Yates
    Aug 29, 2003
  2. cosmos
    Replies:
    0
    Views:
    1,829
    cosmos
    Oct 23, 2003
  3. helpful sql
    Replies:
    0
    Views:
    785
    helpful sql
    May 19, 2005
  4. Radu
    Replies:
    0
    Views:
    1,656
  5. rob thomson
    Replies:
    0
    Views:
    241
    rob thomson
    Sep 4, 2003
Loading...

Share This Page