VB.NET - Reordering columns in a DataTable loses some data

Question

I am trying to reorder the columns in a DataTable by passing the DataTable and a Dictionary of options into a function, similar to as described in the answer to this thread.

DataTable column reorder

Here is the code I am using:

Private Function ReorderColumns(dt As DataTable, options As
Dictionary(Of String, Object)) As DataTable

    Dim orderList = New System.Collections.Generic.Dictionary(Of String, String)

    For Each item In options
        orderList.Add(item.Key, GetOptionValue(options(item.Key), "Order", Int32.MaxValue).ToString())
    Next

    'May be some untested regex characters that need to be added.         
    For Each item In orderList.ToList()
        If String.IsNullOrEmpty(item.Value) Or Not IsNumeric(item.Value) Or Regex.IsMatch(item.Value, "[$.,]") Then
            orderList(item.Key) = Int32.MaxValue.ToString()
        End If
    Next

    Dim colOrd = orderList.OrderBy(Function(x) Convert.ToInt32(x.Value)).ToList()

    For Each lkey In colOrd
        For i As Integer = 0 To dt.Columns.Count - 1
            If dt.Columns(i).ColumnName.Equals(lkey.Key) Then
                dt.Columns(i).SetOrdinal(lkey.Value - 1)
                Exit For
            End If
        Next
    Next

    Return dt
End Function

The reordering takes place as desired. The issue is, there is no data in some of the columns when the DataTable is returned.

I think I'm pretty close since the columns are reordering properly. It feels like the issue might be in this line:

dt.Columns(i).SetOrdinal(lkey.Value - 1)

The columns need to be able to be reordered for a MS Word report. The DataTable is sent through a process that builds the report through OpenXML. In fact, most of the required data shows up when the document is built. It's only on the rows where cells have been merged that the ColumnTitle no longer appears(it used to traverse the entire document, now it is blank instead).

The existing code was generating the report from the DataTable just fine . So I'm trying to just reorder the columns before sending the DataTable through it's usual processes.

Can you confirm the datatable has rows when it enters the function but doesnt when it is returned? — Sam Makin, Apr 09 '14 at 14:01
@Sam Makin The datatable has rows and columns when it enters, and also when it returns. But when it returns, the data is missing from some of the columns(particularly ColumnTitle, which is column(0)). — Josh Nester, Apr 09 '14 at 14:16
I believe the issue is in the OpenXML part of the process. I've tested the above code and data is retained. Inspect the rows as the table is returned to see this is the case. If the OpenXMl formatting is merging cells the content of those cells, that arnt the primary cell, is lost. — Sam Makin, Apr 09 '14 at 14:19
@Sam Makin Yeah that makes sense. I was just looking at it, and the column reordering test I'm running moves the Column Title cell to the second position in the datatable before returning it. So you think would be what is causing the row to be blank then? — Josh Nester, Apr 09 '14 at 14:21
Thanks for the help Sam. I'm going to give this another go in the morning. Will notate the results here. — Josh Nester, Apr 09 '14 at 14:32
@Sam Makin I ended up locking the first column to the first position since the XML side of things does cell merges based on the column title being in the first column. This produced the desired results. Thanks again for your help. — Josh Nester, Apr 10 '14 at 00:54

score 3 · Answer 1 · edited May 23 '17 at 10:33

3

You're doing this the hard way. Rather than re-building the table, which is slow and error-prone, create a DataView for the table with the columns in the order you need. The sample code at the end of that MSDN link should be helpful.

Also take a look at DataColumn.SetOrdinal() as recommended in this answer:

How to change DataTable columns order

edited May 23 '17 at 10:33

Community

1
1

answered Apr 09 '14 at 14:24

Joel Coehoorn

399,467
113
570
794

Thanks Joel. I'll have a good look at that first thing tomorrow morning. – Josh Nester Apr 09 '14 at 14:32
+1. The VERY hard way. Even without a data view, most grids support column hiding & reordering. :) – Victor Zakharov Apr 09 '14 at 14:32
Agreed this is good, if you arnt using the compact framework :) – Sam Makin Apr 09 '14 at 14:58
@Joel Coehoorn Thanks for the input Joel. I didn't end up needing to build a DataView to fix the problem, so I can't credit you for the answer, but +1 for pointing out the downsides of my approach. – Josh Nester Apr 10 '14 at 00:59

score 0 · Accepted Answer · answered Apr 10 '14 at 01:18

This approach did reorder the columns as required.

The issue with the columns losing data was discovered in the OpenXML, as suggested by @Sam Makin.

To fix the problem, I locked the first column in the DataTable to the first position by not allowing it to be part of the reordering process that is presented to the user. The rest of the columns will fall into whatever order the user requires, but the first column will always be the first column since it contains the data that is required by any "title" cell merges in the OpenXML processes.

To accomplish this, the textbox that was taking the first column's order value was turned into a label and set to 1.

@Sam Makin Thanks Sam. It won't let me accept it until tomorrow, so I'll do it then. Thanks for the advice. +1 — Josh Nester, Apr 10 '14 at 10:41

VB.NET - Reordering columns in a DataTable loses some data

2 Answers2