I'm trying to load a dataset from a spreadsheet into a grid for
asp.net. I'm using Framework 3.5. I'm using the Access 2007 driver for
oledb. The spreadsheet has 7,000 rows. I traced the process and after
the .fill executes the dataobject (excelDS) has 563 rows. Where does
this number come from? I'm only loading data from ONE column in the
spreadsheet.

------------------------------------------------------------------------------------------------------------------------------------------------------
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" & savePath & ";Extended Properties=Excel 12.0"
Dim oleDbConn = New OleDbConnection(connString)
Dim oleDa = New OleDbDataAdapter()
Dim cmd = New OleDbCommand("SELECT distinct address FROM
[" & sName & "$] order by address", oleDbConn)
oleDa.SelectCommand = cmd
Dim excelDs = New DataSet
oleDa.Fill(excelDs, "Address") '--- here's
the fill
Dim myDataColumn As DataColumn
myDataColumn = New DataColumn() '--- here's where I
stopped and checked the row count
myDataColumn.DataType = Type.GetType("System.String")
myDataColumn.ColumnName = "Not Found"
excelDs.Tables(0).Columns.Add(myDataColumn)

On Aug 12, 9:40*am, oaksong <oaks... (AT) hotmail (DOT) com> wrote:
I forgot that I had used "DISTINCT" in the sql query. The code does
exactly what it's supposed to.

Also know - when importing text from cells the default length given to pull
in is 256 characters.
To get around this issue, find the 'longest' text within the row column and
add a dummy row on top with a cell taht contains 'that many characters'.
If you have a row on lets say row 800 that has text that is 600 characters
long...
by default this text will be chopped.
So go insert a row into row 1 and put
abcdefghij.....600 characters later...xyz into the column.

I can see that you select your cells- so this next part might not
appy...but...
By default I beleive it is the first 8 cells the import looks at to figure
out what "type" of cell it is, and then formats the cell accordingly.

Example: lets say you had a zipcode/postal code column, but the first 8
data rows were all zip codes. You might get the column become an integer.

So just be careful of that too.

Miro

"oaksong" <oaksong (AT) hotmail (DOT) com> wrote

On Aug 12, 9:40 am, oaksong <oaks... (AT) hotmail (DOT) com> wrote:
I forgot that I had used "DISTINCT" in the sql query. The code does
exactly what it's supposed to.

First thing i would do i run the query on the database and compare the
results.

as the distinct will remove and duplicates on the address column

"oaksong" <oaksong (AT) hotmail (DOT) com> wrote

On Aug 12, 9:40 am, oaksong <oaks... (AT) hotmail (DOT) com> wrote:
I forgot that I had used "DISTINCT" in the sql query. The code does
exactly what it's supposed to.