How can I update a data grid from table with row insertions in arbitrary locations

Question

0.00/5 (No votes)

See more:

, +

I have a question about updating a Data Grid control from a linked Data Table, usually an easy task.

I have a data table where rows can be inserted at a location (index) other than at the end. The data grid always shows the insertion at the end of the grid. The order of contents in the grid is critical for this application and must match the order of rows in the table including the table insertions.

The table starts out with entries of the form
a
b
c
d

Then along comes a new entry and the table can look like
a
b
g (new entry)
c
d

The grid will then look like
a
b
c
d
g

Does anyone know how to make the grid update so that it reflects the order of the rows in the table after insertion at an arbitrary location (index)? In the example, the grid should show g following b, not at the end.

Coding is in VB.

All replies welcome.

Posted 29-Jun-15 19:41pm

Charles Wolfe

Updated 7-Jul-15 17:51pm

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Answer 1 · 2015-07-07T20:25:00

Solution 1

Try to sort the records using DataView and then bind the datagrid.

VB

Dim dataView As New DataView(yourDataTable)

dataView.Sort = "YourColumnName"

Dim newdataTable AS DataTable = dataView.ToTable()

UPDATE :
My apologies, I have misunderstood the question completely.
Check if following approach can help resolving your problem.

As you want the rows to be displayed in order of their insertion, we can add new column in the datatable to keep track of this.

1. Add a new column named "SlNo". You can determine the "SlNo" using ROW_NUMBER() while retriving data from DB
2. While inserting a new row just update value of SlNo in all the rows with the value of GridView RowIndex.
3. Sort the datatable on "SlNo"

Please let me know if it doesn't work.
Thanks !

Posted 7-Jul-15 20:25pm

Suvendu Shekhar Giri

Updated 8-Jul-15 19:26pm

v4

Comments

Charles Wolfe 8-Jul-15 23:49pm

I think I'm missing something in reading your suggestion. The data grid is bound to the table when the program starts and gets populated correctly as data is added to the table until there is an insertion into the middle of the table. The table is always sorted because of the inserts at locations other than the end of the table are always at the sort location; they just always show up at end of the grid. I don't see how sorting the already sorted table will make a difference, even if it is a view. I did try setting the data grid data source to "nothing", then reset it to the table, which one would think would cause it to be repopulated, but the grid stayed the same! I think maybe I have to use .DataBind a row at a time to force the grid to match the table?

Suvendu Shekhar Giri 9-Jul-15 1:28am

My apologies, it was fault at my end. I misunderstood your question. Please see the updated solution.

Charles Wolfe 10-Jul-15 6:20am

Thank you for the updated suggestion; it is most welcome. I think it is already implemented, at least in part. I think I'd best explain the task a bit and the table structure so as to clarify what I meant earlier by the "table is always in sort order". This is bit lengthy but I think necessary.

The program is used to digitize biological structures from images shown on the screen e.g. JPEG or a digital x-ray. As each point is digitized it is added to the table. Each table row contains one point's information: Point Number, Cased ID, x-coordinate, y-coordinate, z-coordinate, current date. The table key is the combination of point number and case ID. The table will eventually be saved in an ACCESS database using an UPDATE SQL command.

As points are sequentially added, the data grid reflects the additions correctly.

If a point is deleted by the operator, it is removed from the table, the remaining rows have the point numbers modified to reflect the deletion so the points remain numbered 1,2,3, etc. (no missing numbers) and the data grid updates correctly. The deleted table row is also not in the data grid.

If a point is inserted by the operator between 2 already digitized points, the point numbers are modified in the table to allow the insertion e.g. point numbers are temporarily changed so that e.g. new point 2 can be inserted as "2,caseID..." without violating the uniqueness of the key as there would otherwise already be a "2,caseid..." row. The points are then renumbered 1 to n with original e.g. 2 becoming 3, etc.

So, the point number column is always in proper sort order, usually the same as the row number. However, when all is done (the operator says "time to save the data"), the grid shows the inserted value as if it was inserted at the end of table. A dump of the table to a text file shows that all the rows are as expected. A dump of the grid to a text file shows the "misplaced" row at the end. I've done a number of things but can never get the inserted row to show up correctly in the grid. Using grid.Refresh or grid.Update do not get the grid to reflect the table changes. I'm beginning to think this is an error in MS software, where the interplay between grid and linked table does not properly handle inserted rows? Most frustrating. I don't see how sorting the sorted table on point number would cause the grid to update as desired. I'll try it in the next few days when I get a chance and see if it does the trick.

Charles Wolfe 19-Jul-15 23:24pm

Well, I've "solved" the problem and it took a lot of work (well more experimentation). Using a DataView did not do it. I had to copy rows of the original table to a temporary table up thru the row preceding the insertion location. Add the new row to the temporary table and then copy the remaining rows of the original table. Then I had to empty the original table and copy (.Add) the temporary table rows to the original table. I could not find a way to sort the original table (Visual Studio 2003, .Net 1.1). "AcceptChanges" usage is also critical to get the table in a condition where this process works. Thanks to all who tried to help.

Charles Wolfe · Answer 2 · 2015-07-19T17:45:00

'It took a lot of experimenting to come up with this procedure!

Dim TempTable as New DataTable
'Call Subroutine that defines the columns of the temp table so that it matches the 'Real table, but has NO keys defined.
Dim DR As DataRow
For i as Integer = 0 to InsertLocation - 1
DR = TempTable.NewRow
DR.Item(COLUMN1) = RealTable.Row(i)(Column)
...
TempTable.Rows.Add(DR)
Next i

'Create the new row to be inserted
Dim NewRow as DataRow
NewRow = TempTable.NewRow
NewRow.Item(Column1) = newvalue1
......
Insert the new row
TempTable.Rows.Add(NewRow)

'Now copy rest of original rows to the TempTable
For j as Integer = InsertLocation to RealTable.Rows.Count-1
'As before, create a matching data row and add to TempTable.
Next j

'Now Empty the original table (RealTable)
For each r as DataRow in RealTable
r.Delete
Next

RealTable.AccptChanges 'CRITICAL Operation, won't work without this!

'Now create a set of new rows from the TempTable and .Add them to the RealTable.
Dim RDR as DataRow
For i as integer = 0 to TempTable.Rows.Count
RDR = RealTable.NewRow
RDR.Item(Column1) = TempTable.Row(i)(Column1)
...
RealTable.Rows.Add(RDR)
Next i

'Done at long last.