Solved

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

Posted on 2014-10-13
10
433 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
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 18

Assisted Solution

by:lludden
lludden earned 200 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 300 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 300 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now