Data error DataGridView DataGridViewComboBoxColumn

  • Thread starter Thread starter QSIDeveloper
  • Start date Start date
Q

QSIDeveloper

I am trying to do what I think is a very basic binding operation. I have a
DataGridView with 2 columns. The first column is bound to Northwind db
Region and the second to Territories. When the form loads I get a Value not
valid exception on the Territories column. The error only occurs if I have
assigned a data source to the DataGridView. The data grid view is a simple
class populated with the ID’s of what should be the selected in the grid
combo boxes.

Code for the data class and the form load routine is included.


void FilterByCell_Load(object sender, EventArgs e)
{
DataSet regions = new DataSet("Regions");
DataSet territories = new DataSet("Territories");
regions = SelectRows(regions, m_connectionString, "Select
RegionID, RegionDescription from Region");
territories = SelectRows(territories, m_connectionString,
"Select TerritoryID, TerritoryDescription, RegionID from territories");

this.RegionsColumn.DataSource = regions.Tables[0];
this.RegionsColumn.DisplayMember = "RegionDescription";
this.RegionsColumn.ValueMember = "RegionID";

this.TerritoriesColumn.DataSource = territories.Tables[0];
this.TerritoriesColumn.DisplayMember = "TerritoryDescription";
this.TerritoriesColumn.ValueMember = "TerritoryID";

m_DataList = new List<ViewData>();
m_DataList.Add(new ViewData(3, 44122));
m_DataList.Add(new ViewData(3, 53404));
m_DataList.Add(new ViewData(2, 60601));
m_DataList.Add(new ViewData(2, 94105));

dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = m_DataList;
dataGridView1.Columns[0].DataPropertyName = "RegionID";
dataGridView1.Columns[1].DataPropertyName = "TerritoryID";
}



public class ViewData
{
private int m_Region;
private int m_Territory;

public ViewData(int Region, int Territory)
{
m_Region = Region;
m_Territory = Territory;
}

public int RegionID
{
get { return m_Region; }
set { m_Region = value; }
}

public int TerritoryID
{
get { return m_Territory; }
set { m_Territory = value; }
}
}
 
Hi,

I performed a test based on your sample code but didn't reproduce the
problem on my side.

I create a WinForm application project and add a DataGridView on the form.
The following is the code in the form.

public partial class Form1 : Form
{
List<ViewData> m_DataList;
private void Form1_Load(object sender, EventArgs e)
{
DataSet regions = new DataSet("Regions");
DataSet territories = new DataSet("Territories");

DataTable regiontable = new DataTable();
DataColumn col = new DataColumn("RegionID", typeof(int));
regiontable.Columns.Add(col);
col = new DataColumn("RegionDescription");
regiontable.Columns.Add(col);
regions.Tables.Add(regiontable);

DataTable territorytable = new DataTable();
col = new DataColumn("TerritoryID", typeof(int));
territorytable.Columns.Add(col);
col = new DataColumn("TerritoryDescription");
territorytable.Columns.Add(col);
col = new DataColumn("RegionID", typeof(int));
territorytable.Columns.Add(col);
territories.Tables.Add(territorytable);

DataRow row = regions.Tables[0].NewRow();
row[0] = 3;
row[1] = "region 3";
regions.Tables[0].Rows.Add(row);

row = regions.Tables[0].NewRow();
row[0] = 2;
row[1] = "region 2";
regions.Tables[0].Rows.Add(row);


row = territories.Tables[0].NewRow();
row[0] = 44122;
row[1] = "terri 44122";
row[2] = 3;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 53404;
row[1] = "terri 53404";
row[2] = 3;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 60601;
row[1] = "terri 60601";
row[2] = 2;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 94105;
row[1] = "terri 94105";
row[2] = 2;
territories.Tables[0].Rows.Add(row);

this.RegionsColumn.DataSource = regions.Tables[0];
this.RegionsColumn.DisplayMember = "RegionDescription";
this.RegionsColumn.ValueMember = "RegionID";

this.TerritoriesColumn.DataSource = territories.Tables[0];
this.TerritoriesColumn.DisplayMember = "TerritoryDescription";
this.TerritoriesColumn.ValueMember = "TerritoryID";

m_DataList = new List<ViewData>();
m_DataList.Add(new ViewData(3, 44122));
m_DataList.Add(new ViewData(3, 53404));
m_DataList.Add(new ViewData(2, 60601));
m_DataList.Add(new ViewData(2, 94105));

dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = m_DataList;
dataGridView1.Columns[0].DataPropertyName = "RegionID";
dataGridView1.Columns[1].DataPropertyName = "TerritoryID";
}
}
public class ViewData
{
private int m_Region;
private int m_Territory;

public ViewData(int Region, int Territory)
{
m_Region = Region;
m_Territory = Territory;
}

public int RegionID
{
get { return m_Region; }
set { m_Region = value; }
}

public int TerritoryID
{
get { return m_Territory; }
set { m_Territory = value; }
}
}
}

If possible, could you please create a simple project that could just
reproduce the problem and send it to me? To get my actual email address,
remove 'online' from my displayed email address.

I look forward to your reply!

Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
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.
 
I sent you the project on 2/19, did you recieve it?

Linda Liu said:
Hi,

I performed a test based on your sample code but didn't reproduce the
problem on my side.

I create a WinForm application project and add a DataGridView on the form.
The following is the code in the form.

public partial class Form1 : Form
{
List<ViewData> m_DataList;
private void Form1_Load(object sender, EventArgs e)
{
DataSet regions = new DataSet("Regions");
DataSet territories = new DataSet("Territories");

DataTable regiontable = new DataTable();
DataColumn col = new DataColumn("RegionID", typeof(int));
regiontable.Columns.Add(col);
col = new DataColumn("RegionDescription");
regiontable.Columns.Add(col);
regions.Tables.Add(regiontable);

DataTable territorytable = new DataTable();
col = new DataColumn("TerritoryID", typeof(int));
territorytable.Columns.Add(col);
col = new DataColumn("TerritoryDescription");
territorytable.Columns.Add(col);
col = new DataColumn("RegionID", typeof(int));
territorytable.Columns.Add(col);
territories.Tables.Add(territorytable);

DataRow row = regions.Tables[0].NewRow();
row[0] = 3;
row[1] = "region 3";
regions.Tables[0].Rows.Add(row);

row = regions.Tables[0].NewRow();
row[0] = 2;
row[1] = "region 2";
regions.Tables[0].Rows.Add(row);


row = territories.Tables[0].NewRow();
row[0] = 44122;
row[1] = "terri 44122";
row[2] = 3;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 53404;
row[1] = "terri 53404";
row[2] = 3;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 60601;
row[1] = "terri 60601";
row[2] = 2;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 94105;
row[1] = "terri 94105";
row[2] = 2;
territories.Tables[0].Rows.Add(row);

this.RegionsColumn.DataSource = regions.Tables[0];
this.RegionsColumn.DisplayMember = "RegionDescription";
this.RegionsColumn.ValueMember = "RegionID";

this.TerritoriesColumn.DataSource = territories.Tables[0];
this.TerritoriesColumn.DisplayMember = "TerritoryDescription";
this.TerritoriesColumn.ValueMember = "TerritoryID";

m_DataList = new List<ViewData>();
m_DataList.Add(new ViewData(3, 44122));
m_DataList.Add(new ViewData(3, 53404));
m_DataList.Add(new ViewData(2, 60601));
m_DataList.Add(new ViewData(2, 94105));

dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = m_DataList;
dataGridView1.Columns[0].DataPropertyName = "RegionID";
dataGridView1.Columns[1].DataPropertyName = "TerritoryID";
}
}
public class ViewData
{
private int m_Region;
private int m_Territory;

public ViewData(int Region, int Territory)
{
m_Region = Region;
m_Territory = Territory;
}

public int RegionID
{
get { return m_Region; }
set { m_Region = value; }
}

public int TerritoryID
{
get { return m_Territory; }
set { m_Territory = value; }
}
}
}

If possible, could you please create a simple project that could just
reproduce the problem and send it to me? To get my actual email address,
remove 'online' from my displayed email address.

I look forward to your reply!

Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
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.
Click to expand...
 
Hi John,

Yes, I have received your sample project. I'm performing research on it now.

As soon as I finish, I will update in the newsgroup.

I appreciate your patience!

Sincerely,
Linda Liu
Microsoft Online Community Support
 
Hi John,

I have finished researching on this issue.

In the Form Load event handler, you fill the two datasets from database.
Because I don't have the database on my side, I comment out the lines of
code to fill the dataset and add some rows into the two datasets manually
as follows:

DataRow row = regions.Tables[0].NewRow();
row[0] = 3;
row[1] = "region 3";
regions.Tables[0].Rows.Add(row);

row = regions.Tables[0].NewRow();
row[0] = 2;
row[1] = "region 2";
regions.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 44122;
row[1] = "terri 44122";
row[2] = 3;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 53404;
row[1] = "terri 53404";
row[2] = 3;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 60601;
row[1] = "terri 60601";
row[2] = 2;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 94105;
row[1] = "terri 94105";
row[2] = 2;
territories.Tables[0].Rows.Add(row);

When the application is run, all works well without any exception. But if
the datatable in the territories dataset doesn't contain a datarow with
TerritoryID field of value 44122 or 60601, an ArgumentException exception
with the message "DataGridViewComboBoxCell value is no valid" is thrown.

I have searched in our internal database and found that this is a behavior
by design. The following is the comment:

The DataGridViewComboBox must hold all possible values that it will display
in it's items collection. For example, when binding to the Customers table
and having the CompanyName field a combobox the combobox must contain all
possible values. If the grid attempts to set the cell's value where the
combobox cell doesn't have the value then we throw the dataError event.

To workaround this, handle the DataError event of the DataGridView. For
example:

void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs
e)
{
// ignore the data error occurring under the territoryComboBoxColumn
if (e.ColumnIndex != territoryComboBoxColumn.Index)
{
MessageBox.Show("Data error occurs:" + e.Exception.Message);
}
}

In addition, I noticed that you handle the CellBeiginEdit and CellEndEdit
event of the DataGridView in the sample project. Based on my understanding,
you'd like to implement cascade comboboxes in the DataGridView, i.e. when
selecting a value in the region column, the drop down items of the
territory column is changed to contain only the corresponding child values
and if changing the value in the region column, the value in the territory
column is cleared. If I'm off base, please feel free to let me know.

I have modified your sample project to meet the above requirement. The
basic theory is to handle the EditingControlShowing and CellValueChanged
event of the DataGridView. In the EditingControlShowing event handler,
change the data source of the editing
control(DataGridViewComboBoxEditingControl) to another data source and
filter the new data source. In the CellValueChanged event handler, if the
cell value under the region column is changed, clear the cell value under
the territory column in the same row.

The following is the code snippet:

void dataGridView1_EditingControlShowing(object sender,
DataGridViewEditingControlShowingEventArgs e)
{
if (this.dataGridView1.CurrentCell.ColumnIndex ==
territoryComboBoxColumn.Index)
{
DataGridViewComboBoxEditingControl control = e.Control as
DataGridViewComboBoxEditingControl;
BindingSource bs = control.DataSource as BindingSource;
if (bs != null)
{
// set the filteredChildBS as the DataSource of the
editing control
(e.Control as ComboBox).DataSource = filteredChildBS;


object regionvalue =
this.dataGridView1.Rows[this.dataGridView1.CurrentCell.RowIndex].Cells[regio
nComboBoxColumn.Index].Value;
if (regionvalue == DBNull.Value || regionvalue == null)
{
filteredChildBS.Filter = "RegionID=-1";
}
else
{
filteredChildBS.Filter = "RegionID=" +
regionvalue.ToString();
}
// set the SelectedValue property of the editing control
if (this.dataGridView1.CurrentCell.Value !=
DBNull.Value && this.dataGridView1.CurrentCell.Value != null)
{
control.SelectedValue =
this.dataGridView1.CurrentCell.Value;
}
}
}
}

void dataGridView1_CellValueChanged(object sender,
DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == regionComboBoxColumn.Index)
{
// clear the cell value of the territory column in the same
row
if (this.dataGridView1.DataSource.GetType() ==
typeof(DataTable))
{

this.dataGridView1.Rows[e.RowIndex].Cells[territoryComboBoxColumn.Index].Val
ue = DBNull.Value;
}
else
{

this.dataGridView1.Rows[e.RowIndex].Cells[territoryComboBoxColumn.Index].Val
ue = null;
}
}
}

I will send the modified sample project to you. Please run it on your side
to see if there's any problem.

Hope this helps.
If you have anything unclear, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support
 
I found out what the problem is. Now I see that the TerritoryID of
Territories is nvarchar not int as is RegionID. That is why it only happens
with the DB

So changing dtViewData.Columns.Add("TerritoryID", typeof(Int32));
to dtViewData.Columns.Add("TerritoryID", typeof(string));
Made things work.

Thanks for you help

Linda Liu said:
Hi John,

I have finished researching on this issue.

In the Form Load event handler, you fill the two datasets from database.
Because I don't have the database on my side, I comment out the lines of
code to fill the dataset and add some rows into the two datasets manually
as follows:

DataRow row = regions.Tables[0].NewRow();
row[0] = 3;
row[1] = "region 3";
regions.Tables[0].Rows.Add(row);

row = regions.Tables[0].NewRow();
row[0] = 2;
row[1] = "region 2";
regions.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 44122;
row[1] = "terri 44122";
row[2] = 3;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 53404;
row[1] = "terri 53404";
row[2] = 3;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 60601;
row[1] = "terri 60601";
row[2] = 2;
territories.Tables[0].Rows.Add(row);

row = territories.Tables[0].NewRow();
row[0] = 94105;
row[1] = "terri 94105";
row[2] = 2;
territories.Tables[0].Rows.Add(row);

When the application is run, all works well without any exception. But if
the datatable in the territories dataset doesn't contain a datarow with
TerritoryID field of value 44122 or 60601, an ArgumentException exception
with the message "DataGridViewComboBoxCell value is no valid" is thrown.

I have searched in our internal database and found that this is a behavior
by design. The following is the comment:

The DataGridViewComboBox must hold all possible values that it will display
in it's items collection. For example, when binding to the Customers table
and having the CompanyName field a combobox the combobox must contain all
possible values. If the grid attempts to set the cell's value where the
combobox cell doesn't have the value then we throw the dataError event.

To workaround this, handle the DataError event of the DataGridView. For
example:

void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs
e)
{
// ignore the data error occurring under the territoryComboBoxColumn
if (e.ColumnIndex != territoryComboBoxColumn.Index)
{
MessageBox.Show("Data error occurs:" + e.Exception.Message);
}
}

In addition, I noticed that you handle the CellBeiginEdit and CellEndEdit
event of the DataGridView in the sample project. Based on my understanding,
you'd like to implement cascade comboboxes in the DataGridView, i.e. when
selecting a value in the region column, the drop down items of the
territory column is changed to contain only the corresponding child values
and if changing the value in the region column, the value in the territory
column is cleared. If I'm off base, please feel free to let me know.

I have modified your sample project to meet the above requirement. The
basic theory is to handle the EditingControlShowing and CellValueChanged
event of the DataGridView. In the EditingControlShowing event handler,
change the data source of the editing
control(DataGridViewComboBoxEditingControl) to another data source and
filter the new data source. In the CellValueChanged event handler, if the
cell value under the region column is changed, clear the cell value under
the territory column in the same row.

The following is the code snippet:

void dataGridView1_EditingControlShowing(object sender,
DataGridViewEditingControlShowingEventArgs e)
{
if (this.dataGridView1.CurrentCell.ColumnIndex ==
territoryComboBoxColumn.Index)
{
DataGridViewComboBoxEditingControl control = e.Control as
DataGridViewComboBoxEditingControl;
BindingSource bs = control.DataSource as BindingSource;
if (bs != null)
{
// set the filteredChildBS as the DataSource of the
editing control
(e.Control as ComboBox).DataSource = filteredChildBS;


object regionvalue =
this.dataGridView1.Rows[this.dataGridView1.CurrentCell.RowIndex].Cells[regio
nComboBoxColumn.Index].Value;
if (regionvalue == DBNull.Value || regionvalue == null)
{
filteredChildBS.Filter = "RegionID=-1";
}
else
{
filteredChildBS.Filter = "RegionID=" +
regionvalue.ToString();
}
// set the SelectedValue property of the editing control
if (this.dataGridView1.CurrentCell.Value !=
DBNull.Value && this.dataGridView1.CurrentCell.Value != null)
{
control.SelectedValue =
this.dataGridView1.CurrentCell.Value;
}
}
}
}

void dataGridView1_CellValueChanged(object sender,
DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == regionComboBoxColumn.Index)
{
// clear the cell value of the territory column in the same
row
if (this.dataGridView1.DataSource.GetType() ==
typeof(DataTable))
{

this.dataGridView1.Rows[e.RowIndex].Cells[territoryComboBoxColumn.Index].Val
ue = DBNull.Value;
}
else
{

this.dataGridView1.Rows[e.RowIndex].Cells[territoryComboBoxColumn.Index].Val
ue = null;
}
}
}

I will send the modified sample project to you. Please run it on your side
to see if there's any problem.

Hope this helps.
If you have anything unclear, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support
Click to expand...
 
Hi John,

Thank you for your reply! I'm glad to hear that you have solved the
problem.

In fact, since you didn't demonstrate the structure of the TerritoriesTable
explicitly in the sample project( you filled the datatable from your
database but I didn't have the database on my hand), I didn't find out the
real reason that caused your problem.

But I did realize that an exception will be thrown when a
DataGridViewComboBoxColumn is bound and a cell under this column is set to
a value that is not in the item collection.

In addition, I work out how to implement a cascade comboboxes in a
DataGridView. Hope this is also helpful to you.

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!

Sincerely,
Linda Liu
Microsoft Online Community Support
 
I am having a problem now with a 3rd column. When I select a value the combo
below the one that I was working with looses it display and a data error
occurs.

I have forwarded you the example with the 3rd column added
 
Hi John,

There's something wrong with my user account these two days so that I cannot
receive any email sent to my company email box.

Please send your example to my another email address:
(e-mail address removed).

Thank you and I look forward to your reply!

Sincerely,
Linda Liu
Microsoft Online Community Support
 
Hi John,

Yes, I have received your sample project.

I have run it on my machine and did see the problem on my side. After doing
some research, I found that this problem is caused by setting the
DataSource property of the col3ChoicesBS and the filteredcol3ChoicesBS
components to a DataTable, rather than a DataSet.

When the DataSource property of two BindingSource components are set to a
same DataTable, they are sharing the same copy of the DataTable. That is,
if you do a filter on one BindingSource component, the other BindingSource
component will reproduce the same result.

However, if the DataSource property of the two BindingSource components are
set to a DataSet and the DataMember property of them are set to a table
within it, the two BindingSource components are NOT sharing the same copy
of the data source. Doing a filter on one BindingSource component won't
affect the other BindingSource component.

So the workaround of your problem is to create a DataSet and add the
m_Col3Choices to this DataSet. The next step is to set the DataSource
property of the col3ChoicesBS and the filteredcol3ChoicesBS components to
this DataSet and the DataMember property to the Col3Choices table.

For example:

void FilterByCell_Load(object sender, EventArgs e)
{
...
DataSet col3Choices = new DataSet();
col3Choices.Tables.Add(m_Col3Choices);

col3ChoicesBS.DataSource = col3Choices;
// assume that the table name is "Col3ChoicesTable"
col3ChoicesBS.DataMember = "Col3ChoicesTable";

filteredcol3ChoicesBS.DataSource = col3Choices;
filteredcol3ChoicesBS.DataMember = "Col3ChoicesTable";
...
}

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

Sincerely,
Linda Liu
Microsoft Online Community Support
 
Back
Top