Troubles adding the first row in an empty datatable of a dataset in VB.Net

Hi

I am developing a VB.Net WinForm app which will replace an Access application which I wrote a while ago. This app is basically combining/modifying the contents of a number of (related) tables into a new table. So I created a dataset and loaded all my "input" tables first into the dataset as datatables, and also loaded the "output" table, which at this point was an empty table with no rows, also as a datatable.

When the app comes to the point where it wants to add the first row into the "output" datatable, I have obviously not found the right syntax to create a new row in a completely emty datatable, because it gives me an exception with a index out of range, and tells me the output datatable has no rows yet. Strange enough, when I re-execute the same few lines of code which add a new row, it works, and also for all subsequent rows added. So at the end my output datatable is correctly filled, except that the first row is empty. Here is the code I use to add a new row:
 
            nCountM = 0
            .....
           ' processing takes place here which selects the data to be inserted in the output datatable
            .....           
            nCountM = nCountM +1
            MultiTableDataSet.Tables("Out").NewRow()
            MultiTableDataSet.Tables("Out").Rows.Add(New Object())
            MultiTableDataSet.Tables("Out").AcceptChanges()

            ' Set first field of new row
            ' ---------------------------------
            MultiTableDataSet.Tables("Out").Rows(nCountM).Item(0) = MultiTableDataSet.Tables("In1").Rows(nCount).Item("Field0")

Open in new window


The exception happens on the last statement in the code snippet.

I thought that, by loading the output table into the datatable, VB had the right definition of all columns (which it does have), and that therefore even a MultiTableDataSet.Tables("Out").Rows.Add() would suffice, but both that syntax and the one above with New Object() do not work, but only for the very first Rows.Add call.

It's probably something very easy for you guys, but I'm still learning VB.Net and I prefer to ask than to loose time fiddling with trials and errors...

Thanks for help
Bernard
LVL 1
bthouinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
The first row is row 0, not 1 (nCountM is probably 1).  I generally create the new row, then set the fields on that row, then add the completed row to the table, rather than add the row to the table, then modify it within the table.
0
louisfrCommented:
First, the reason you get an IndexOutOfRange is because you try setting row using nCountM as an index. The first row is 0, so you should use nCountM-1.

As for the ways to add a row, you have two possibilities:
- NewRow creates and returns a new DataRow but it isn't added to the DataTable. You have to put it into a variable to be able to use it, and pass it to the Add method to add the row to the DataTable.
- Another Add method creates and adds the new row to the DataTable. That's the Add method which doesn't take a DataRow as parameter. Instead, it takes as many values as there is columns in the table.

Here is your code snippet modified to use the first way:
nCountM = 0
            .....
nCountM = nCountM +1
Dim newrow = MultiTableDataSet.Tables("Out").NewRow()
' Set first field of new row
newrow(0) = MultiTableDataSet.Tables("In1").Rows(nCount).Item("Field0")
' Add the row to the table
MultiTableDataSet.Tables("Out").Rows.Add(

Open in new window

And here using the second way:
nCountM = 0
            .....
MultiTableDataSet.Tables("Out").Rows.Add(MultiTableDataSet.Tables("In1").Rows(nCount).Item("Field0"))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jacques Bourgeois (James Burger)PresidentCommented:
I am in a hurry and do not have time to read everything that was written before.

But the DataReader provides you with a FillSchema method that can be used instead or prior to the Fill method to retrieve the structure of a table. Using FillSchema with a database table that has the proper structure might do it for you.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

louisfrCommented:
@JamesBurger
It looks like the author already knows how to read the database ("I created a dataset and loaded all my "input" tables first into the dataset as datatables").
0
bthouinAuthor Commented:
Hi guys

Tnaks for your input. I could not yet chek because I've been working on other stuff, but the problem is most certainly my "bad habit" of defining my arrays in Access from row 1 instead of 0, which means that I effectively have the initialize the datatable counter to -1, as I increment that counter as soon as I find data to fill, i.e. before I fill the datable first row. That way the counter will be correctly 0 for the first row, instead of 1 if I init with 0.

I will test soon again, so I'll let you know. And yes, I have already filled the "output" data table a couple of times, although the first row was empty and I had to fiddle in the debugger to avoid ending the processing before it had even properly started...

@JamesBurger: does the FillSchema means that the dataset "knows" what is the primary key of the datatable and will keep it in primary key sequence when I add my rows ? I'm asking because that's my next problem: I'm filling the datatable first with data where the keys are NOT in PK sequence , but I need to get the table at some point in PK sequence, because I have to compute a number of subtotals where the PK sequence is essential. And I don't know yet how I should do that or if it will be done automatically by this fantastic dataset concept.

Thanks for your answers.

Regards
Bernard
0
Jacques Bourgeois (James Burger)PresidentCommented:
Yes, FillSchema retrieves the primary key, but naturally, as long as your SELECT command retrieves data from only one table and retrieves the field(s) that make the primary key.

If what you need is the PK sequence however, the data will not come back sorted on the PK, FillSchema or not. You still have to have the proper ORDER BY clause in your SELECT. FillSchema will simply mark the proper field(s) as being the primary key, thus automatically preventing you from adding duplicates. Il will also enables you to create the proper DataRelation objects between the tables in the DataSet if needed.
0
bthouinAuthor Commented:
Hi James

OK, then let me explain what my problem is.

The app which I am converting from Access to VB.net is extracting data every morning from different tables in a host system and stores that extracted data in a "result" table which gets filled anew every day. I.e all rows in that result table are first deleted, then the host data is extracted and the table filled with the extracted data.

To speed up the process, I've developed in Acces a technique which is actually close to the dataset (and that completely without being aware of the dataset concept), using memory arrays in which I load the contents of the DB tables I need, then the processing is fantastically fast, as one works in memory.

So my result table is one large memory array with exactly the same number of columns as the physical DB table, and the Variant loosely-typed VBA data type allows me to store any kind of data in there, which is handy. There is only one drawback: the way the host data is extracted, my memory table in Access is not filled in PK sequence, so I have to rely on dumping the mem table to the DB table to get it in PK sequence, before I can do processing which requires the data to be in PK sequence.

That's what I'd like to avoid with the dataset: I'd like that datatable in the dataset to be in PK sequence, but, if I understand you correctly, that won't be the case. So, I guess my only choice in .Net is to:
- fill the datatable with that out-of-sequence data
- call the Update method to get it saved in the DB table
- reload it in the datatable in the dataset using ORDER BY clause so that it is in PK sequence
- do my processing which need PK sequence

Am I right ?

Thanks for your answer.
Bernard

PS: you have a very French name. Are you French by any chance ? I am French myself, living in Zurich, Switzerland since many, many years.
0
louisfrCommented:
Quelle coïncidence.
I'll continue in English anyway to the benefit of people with the same problem.

If you set the Sort property of the DefaultView of the DataTable, then you can get the Rows in that order.
        Dim ds = New DataSet1
        Dim dt = ds.Tables.Add()
        dt.Columns.Add("test1")
        dt.Columns.Add("test2")
        dt.Rows.Add("a", "b")
        dt.Rows.Add("d", "b")
        dt.Rows.Add("c", "b")
        dt.Rows.Add("c", "a")

        'This prints in DataTable order
        '   a, b
        '   d, b
        '   c, b
        '   c, a
        For Each row In dt.DefaultView
            Debug.Print(row("test1") & "," & row("test2"))
        Next

        'This sorts by test1 column
        '   a, b
        '   c, b
        '   c, a
        '   d, b
        dt.DefaultView.Sort = "test1"
        For Each row In dt.DefaultView
            Debug.Print(row("test1") & "," & row("test2"))
        Next

        'This sorts by test1 and test2 columns
        '   a, b
        '   c, a
        '   c, b
        '   d, b
        dt.DefaultView.Sort = "test1,test2"
        For Each row In dt.DefaultView
            Debug.Print(row("test1") & "," & row("test2"))
        Next

Open in new window

0
Jacques Bourgeois (James Burger)PresidentCommented:
Sorry for the delay, we are not on the same time frame, and there has just been a violent event at our parliament that took my attention for the last hour.

You do not need to sort after the DataTable is filled in. You can specify the sort order in the SQL that you use to build the table.

Je vis à Longueuil, en banlieue de Montréal. And have been there for probably more many years than you in Zurich.
0
bthouinAuthor Commented:
Bonjour Jacques

Sorry for the late response, I've been working on other stuff myself as well.

Thanks for your answers, more questions to come in separate posts.

J'ai vécu à Zurich au total 46 ans, et le reste en France.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.