Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > GetOleDbSchemaTable bug (!) not all columns

Reply
Thread Tools

GetOleDbSchemaTable bug (!) not all columns

 
 
Radu
Guest
Posts: n/a
 
      06-13-2008
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
..
..
..
'Retreive the collection of columns in this excel file...
schemaTable = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Column s,
New
Object() {Nothing, Nothing, strSheetName, Nothing})
..
..
..
'Check if we have at least the "PIN" column:
If schemaTable.Select("COLUMN_NAME='PIN'").GetUpperBo und(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 113 AAA 1
3 147 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 Col2 Col3 PIN 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
GetOleDbSchemaTable bug (!) not all columns Radu ASP .Net 0 05-13-2008 10:54 PM
convert rows to columns and columns to rows helpful sql ASP .Net 0 05-19-2005 06:03 PM
How to GetOleDbSchemaTable with MSIDXS OLE DB provider ? cosmos ASP .Net 0 10-23-2003 07:08 AM
Columns and Inherited Datagrid...Active Schema does not support columns rob thomson ASP .Net Datagrid Control 0 09-04-2003 03:09 PM
Using GetOleDbSchemaTable to get SQL Server Field Description - using pete ASP .Net 1 08-29-2003 10:50 AM



Advertisments