?
Solved

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

Posted on 2014-10-13
10
Medium Priority
?
564 Views
Last Modified: 2014-10-27
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
0
Comment
Question by:bthouin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 18

Assisted Solution

by:lludden
lludden earned 800 total points
ID: 40377477
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
 
LVL 11

Accepted Solution

by:
louisfr earned 1200 total points
ID: 40377478
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
 
LVL 40
ID: 40378706
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 11

Expert Comment

by:louisfr
ID: 40379270
@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
 
LVL 1

Author Comment

by:bthouin
ID: 40394511
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
 
LVL 40
ID: 40394884
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
 
LVL 1

Author Comment

by:bthouin
ID: 40396559
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
 
LVL 11

Assisted Solution

by:louisfr
louisfr earned 1200 total points
ID: 40396770
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
 
LVL 40
ID: 40397136
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
 
LVL 1

Author Comment

by:bthouin
ID: 40406246
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

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question