A surprising fix for column ordering of a dynamic DataGridView

  • Thread starter Thread starter michael sorens
  • Start date Start date
M

michael sorens

I have seen a few posts in the forums regarding column order of a
DataGridView. I also ran into an issue involving this and thought I would
pass along my scenario and its solution in case it might be of use to the
community.

I have an application that uses a RichTextBox and a DataGridView as a simple
database query tool. As such, the DataGridView has no columns or associated
data set when the program starts up, and the AutoGenerateColumns property is
true. The user may enter an arbitrary query and the result set dynamically
defines and populates the DataGridView.

The problem was this:
(1) Execute a query which begins "select a, b from... "
(2) Observe DataGridView columns in this order: a, b
(3) Change the query to begin "select c, a, b from..." and execute it.
(4) Observe DataGridView columns in this order: a, b, c rather than c, a, b
(5) Make no changes and execute again.
(6) Observe DataGridView columns now in this order: c, a, b
So it takes an extra refresh of the same query to get the DataGridView to
put columns in the correct order! This is 100% reproducible behavior.

I then attempted to create a smaller project to isolate this issue. With
essentially the same code the behavior in this test program was different:
At step 6 the columns remain in the wrong order and stay that way no matter
how many times the query is executed. For this project, this is 100%
reproducible behavior.

Both of these use essentially the same code. First the BAD code:

qDataGridView.BindingSource = qBindingSource;
DataTable dataTable = new DataTable();
SqlDataAdapter tableAdapter =
new SqlDataAdapter(queryString, myConnectionString);
try { tableAdapter.Fill(dataTable); }
catch (SqlException ex) { /* handle exception... */ }
qBindingSource.DataSource = dataTable;
qBindingSource.DataMember = null;

And now the GOOD code:

qDataGridView.BindingSource = qBindingSource;
DataTable dataTable = new DataTable();
SqlDataAdapter tableAdapter =
new SqlDataAdapter(queryString, myConnectionString);
qBindingSource.DataSource = dataTable;
qBindingSource.DataMember = null;
try { tableAdapter.Fill(dataTable); }
catch (SqlException ex) { /* handle exception... */ }

The fix, as you will observe, is that the Fill() method must be called
*after* the DataTable is assigned to the binding source's DataSource
property. I am not sure why the two projects yielded different results with
the same bad code, but they both are corrected with the good code.

I am not enough of a subject matter expert to state categorically, but this
seems like a bug in the framework code to me...
 
If you map the DataPropertyName setting of each column of the grid (from the
smart tag of the grid, or otherwise), with the column (or field name, if
you prefer) returned by the SQL statement, the order is preserved (at least,
when using LINQ to MS SQL Server).


Vanderghast, Access MVP
 
Hi Michael,

Thank you for sharing with us your workaround!

I performed a test based on your sample code and did see the same thing on
my side.

If a DataGridView's AutoGenerateColumns property is set to true (which is
the default value), the DataGridView will populate columns in it when its
DataSource/DataMember property is set or changed.

If the schema of the DataGridView's data source is changed later,
DataGridView is informed of this change and then it re-populates its
columns, i.e. create new columns for new data columns in the data source
and remove those columns that don't have corresponding data columns in the
data source. Note that for those columns that still have corresponding data
columns in the data source, they remain unchanged. This is why you see the
column order in the DataGridView is "a, b, c" rather "c, a, b" in step 4.

In your workaround, you set a new created DataTable to the DataSource
property of the qBindingSource. Because the data source of the DataGridView
is changed and there's no data columns in the new DataTable, DataGridView
removes all columns in it.

Then you execute a new query and fill the result into the new DataTable.
DataGridView re-populate columns with the new schema. So the column
ordering in the DataGridView now is correct.

As we can see, this behavior of DataGridView is by design. Another
workaround is to set the DataGridView.DataSource property to null (so that
DataGridView removes all columns in it first) and then set this property to
the data source (so that columns are populated according to the new schema)
after this data source is changed. For example:

qDataGridView.DataSource = qBindingSource;
DataTable dataTable = new DataTable();
SqlDataAdapter tableAdapter =
new SqlDataAdapter(queryString, myConnectionString);
try { tableAdapter.Fill(dataTable); }
catch (SqlException ex) { /* handle exception... */ }
qBindingSource.DataSource = dataTable;
qBindingSource.DataMember = null;

qDataGridView.DataSource = null;
qDataGridView.DataSource = qBindingSource;

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Linda:
Thank you so much for explaining why my code was really working as it was
supposed to; I understand now why the sequence of statements I originally
used was flawed. And thanks also for specifying a better sequence of
statements. Once I understood why you ordered them as you did and I changed
my code to match, this also fixed another elusive bug I was encountering but
had not yet had enough details to post a question about. (The bug was that on
certain data I was getting DataError events being generated from the
DataGridView complaining about contents incompatible with column data types.
I judge that this was just another manifestation of the same issue I started
this thread about.) So you saved me quite a bit of effort on this second bug
in my code!
 
Hi Michael,

You are welcome! I'm glad to hear that the problem is solved now : )

If you have any other question in the future, please don't hesitate to
contact us. It's always our pleasure to be of assistance!

Have a nice day!

Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
 
I think I have a similar problem. It goes like this:

I'm creating a software for some engineering calculations. I have a list of several objects of diferent classes. Some of them have similar properties (i.e. same name and type, NOT same ancestor) and I want my data grid to represent only the common properties to all. The goal is that when the user select a diferent set of objects, the columns and rows vary according to the properties and the objects respectively.

Since this is not a DB solution y created a ITypedList to expose only the similar properties and custom property descriptors that know how to handle SetValue and GetValue for objects who does not share same ancestors.

Everything works fine, when I change the selection the DataGrid vary as expected, but when I tried to edit some data (AFTER I vary the selection for first time) it crash with the following message:
"El indice -1 no contiene un valor" wich I translate to english as: "Index -1 thoes not contain a value".

The problem is that the exception is captured on Program.cs in the following line:

Application.Run(new frmMainForm());

Wich is not much of an information. I really don't know where the err may be. Any ideas?


EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng
 
in message
I think I have a similar problem. It goes like this:

I'm creating a software for some engineering calculations. I have a list
of several objects of diferent classes. Some of them have similar
properties (i.e. same name and type, NOT same ancestor) and I want my data
grid to represent only the common properties to all. The goal is that when
the user select a diferent set of objects, the columns and rows vary
according to the properties and the objects respectively.

Since this is not a DB solution y created a ITypedList to expose only the
similar properties and custom property descriptors that know how to handle
SetValue and GetValue for objects who does not share same ancestors.

Everything works fine, when I change the selection the DataGrid vary as
expected, but when I tried to edit some data (AFTER I vary the selection
for first time) it crash with the following message:
"El indice -1 no contiene un valor" wich I translate to english as:
"Index -1 thoes not contain a value".

The problem is that the exception is captured on Program.cs in the
following line:

Application.Run(new frmMainForm());

Wich is not much of an information. I really don't know where the err may
be. Any ideas?
Click to expand...

You put try/catches in the problem path to isolate where the problem starts.
 
Marcelino said:
I think I have a similar problem. It goes like this:

I'm creating a software for some engineering calculations. I have a list of several objects of diferent classes. Some of them have similar properties (i.e. same name and type, NOT same ancestor) and I want my data grid to represent only the common properties to all. The goal is that when the user select a diferent set of objects, the columns and rows vary according to the properties and the objects respectively.

Since this is not a DB solution y created a ITypedList to expose only the similar properties and custom property descriptors that know how to handle SetValue and GetValue for objects who does not share same ancestors.

Everything works fine, when I change the selection the DataGrid vary as expected, but when I tried to edit some data (AFTER I vary the selection for first time) it crash with the following message:
"El indice -1 no contiene un valor" wich I translate to english as: "Index -1 thoes not contain a value".

The problem is that the exception is captured on Program.cs in the following line:

Application.Run(new frmMainForm());

Wich is not much of an information. I really don't know where the err may be. Any ideas?
Click to expand...

The call stack included in the exception (or the inner exception of the
exception) tells you exactly where the exception originated.
 
Back
Top