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/subscripti...ult.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/subscripti...t/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[MSFT]" wrote:

> 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/subscripti...ult.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/subscripti...t/default.aspx.
> ==================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

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[MSFT]" wrote:

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

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

"Linda Liu[MSFT]" wrote:

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

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 Removed).

Thank you and I look forward to your reply!

Sincerely,
Linda Liu
Microsoft Online Community Support

I sent the zip file to both of your emails.

Did you recieve it?

"Linda Liu" wrote:

> 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 Removed).
>
> Thank you and I look forward to your reply!
>
> Sincerely,
> Linda Liu
> Microsoft Online Community Support
>
>
>