Link to home
Start Free TrialLog in
Avatar of Mehawitchi

asked on

Is it possible to use da.Fill(Dataset) multiple times without adding new rows

OK, I know the question sounds weired a little bit, but here is my problem:

I'm trying to retrieve a table in Excel 2007 worksheet, which has more than 255 columns, using 2

The problem lies in the "Select * from [Sheet1$]" statement, which will only pick a maximum of 255 columns from the Excel sheet, even though the sheet has 280 columns.

I've posted this problem in another forum and the answer I got is that this is a limitation (Max 255 columns) in the data access provider (OLEDB.12.0), and they suggested that I do two or more uploads (Select / da.Fill) on thec same table.

I did this already, but the problem now is in the rows of second upload, instead of adding them to the same rows of first upload, they are bieng added on top of them.

To explain further, the excel sheet has 280 columns x 100 rows. In the first upload, I fill the dataset with 255 clms x 100 rows. In the second upload, I fill the dataset with 25 clmns x 100 rows, but in the end result I get a dataset with 280 clmns (cool) and 200 rows (Not cool)

My question is:
Is there any way that I enforce the dataset to add the rows of the second upload into those of the first upload, so that I get actual represenatation of my Excel sheet in the dataset (280 columns x 100 rows)

I've attached my code here for your review

Appreciate your help

'Declare and instantiate the connection object.
                    Dim con As New OleDbConnection(xlCon)
                    'Declare and instantiate the command object.
                    Dim com As New OleDbCommand()
                    '' ''Declare and instantiate a local untyped dataset.
                    ' ''Dim ds As New DataSet
                    'Declare and instantiate a DataAdapter which fill the dataset with data.
                    Dim da As New OleDbDataAdapter
                    Dim dsXLData As New DataTable()
                    NoOfClms = 280 'There is another procedure that will get the NoOfClmns
                    StrtClm = 1
                    EndClm = Math.Min(255, NoOfClms)
                    Do While EndClm <= NoOfClms
                        'Construct the "SELECT" statement from SheetNames array
                        XlsSQL = "Select * from [" & SheetNames(ShtNo, 0).ToString & _
                                                "$" & ConvertToXlClm(StrtClm) & "1:" & ConvertToXlClm(EndClm) & "101]"
                        'Add values to the command object's properties.
                        With com
                            .Connection = con
                            .CommandType = CommandType.Text
                            .CommandText = XlsSQL
                        End With
                        With da
                            .AcceptChangesDuringFill = True
                            .SelectCommand = com
                            'Add data to the dataset
                        End With
                        StrtClm = EndClm + 1
                        EndClm = Math.Min(EndClm + 255, NoOfClms)

Open in new window

Avatar of ZachSmith
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sancler

For this to work, looking at it from the VB.NET/ADO.NET perspective, I think two conditions would need to be met.  First the dataadapter would have to recognise a Primary Key in the Excel data.  Second, that Primary Key would have to be included in BOTH .Fills.  

It's not something I've ever done - I've worked very little with the interface between VB.NET and Excel - but would it be possible for you to refine your code so that both those conditions are met?  For instance, I don't think that Excel actually provides a Primary Key.  But is there some column or combination of columns that you could add as primary key to the datatable as primary key between the two fills?

For instance, I've just tried this code

        Dim sql As String = "SELECT * FROM [Sheet1$]"
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter(Sql, con)
        DataGridView1.DataSource = dt
        'Dim pk As DataColumn = dt.Columns(0)
        'Dim pkcols(0) As DataColumn
        'pkcols(0) = pk
        'dt.PrimaryKey = pkcols

If I run it without the commented out lines, I get two lots of rows.  But if I include those lines, I only get one lot of rows.

Avatar of Mehawitchi


Wow. Three solutions in no time. I'm really impressed.
Thank you all for your great help.

Would appreciate if you have a simple code that shows the loop process between the two datasets.

Roger (Sancler)
Your solution appears to be the one that will finally work for me, but I'll leave it as my last resort as I feel it's a bit complex

Bob (TheLearnedOne)
Do you think you can elaborate a little bit on DataTable.Merge

Thank you all again. Greatly appreciated


Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Roger/Bob

Quick question before I start applying your solution:

Can I use range in the 2nd Select command:

da2.SelectCommand.CommandText = "SELECT Col1, Col4:Col6 FROM [Sheet1$]"
Can I use range in the 2nd Select command

I don't think so, but as I said before, I haven't done much with using OleDbDataAdapters on Excel.  

My demo code might have been a bit misleading.  The Col1, Col2 etc were actually header texts rather than column numbers.  In my connection string I used

    ;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

and in my test Excel Worksheet I just put a few Col* along the first row.

Although it is possible to use ranges with dataadapters, so far as I know that is equivalent to limiting the (equivalent to) the "table" from which the data is to be drawn.  So a typical sql might be

    "SELECT * FROM [Sheet1$A1:$B10]"

That looks to me to be different in principle from

    "SELECT Col1, Col4:Col6 FROM [Sheet1$]"

But the only way to find out is to try it.  I've fiddled a bit - I would not claim exhaustively - and couldn't get it to work.

Thanks again Roger for your great help

I'm also trying from my side, but the multiple columns is not working.

What is working though is when you select one range like "SELECT * FROM [Sheet1$A1:$B10]"

Anyways, I'll keep trying and will let you know the outcome.

Greatly appreciated