I have a simple 2 table setup, 'Teams' and 'Players'. Each player has a (nullable) int TeamID (preferrable AllowDBNull, but -1 as default might work too). I insert/update/delete records to both tables, and these are sound (mathich IDs in a small test-DB). The setup looks like this:
private void initDataSet()
{
dataSet = new DataSet( "DataBase" );
var dtPlayers = new DataTable( "Players" );
dtPlayers.Columns.Add( new DataColumn() {
ColumnName = "ID" ,
Caption = "ID" ,
DataType = typeof( int ) ,
Unique = true ,
AutoIncrement = true ,
} );
dtPlayers.Columns.Add( new DataColumn() {
ColumnName = "TeamID" ,
Caption = "Team" ,
DataType = typeof( int ) ,
//AllowDBNull = true ,
//DefaultValue = DBNull.Value ,
} );
dataSet.Tables.Add( dtPlayers );
var dtTeams = new DataTable( "Teams" );
dtTeams.Columns.Add( new DataColumn() {
ColumnName = "ID" ,
Caption = "ID" ,
DataType = typeof( int ) ,
Unique = true ,
AutoIncrement = true ,
} );
dtTeams.Columns.Add( new DataColumn() {
ColumnName = "FullName" ,
Caption = "Full Name" ,
DataType = typeof( string ) ,
} );
dataSet.Tables.Add( dtTeams );
dtPlayers.PrimaryKey = new DataColumn[] { dtPlayers.Columns[ "ID" ] };
dtTeams.PrimaryKey = new DataColumn[] { dtTeams.Columns[ "ID" ] };
dataSet.Relations.Add( "Team - Player" , dtTeams.Columns[ "ID" ] , dtPlayers.Columns[ "TeamID" ] );
}
Now I want to have a dropdown to display the teams in the players-table with a default empty selection (meaning no team selected - value eithe r-1 or null, which becomes DBNull later on).
For this, I setup the DataGridView and also an additional DataTable as .DataSource for the DataGridViewComboBoxColumn.
Each time the Dropdown is selected/edited, I update the dtTeamIds table, to reflect changes.
I also setup the proper .DisplayMember & .ValueMember fields.
private DataTable dtTeamIds = new DataTable( "TeamIds" ); // datasource for dorpdown-list only
private void updateTeamIds()
{
var teamsTable = dataSet.Tables[ "Teams" ] as DataTable;
var items = teamsTable.Rows.Cast<DataRow>()
.Select( row => new KeyValuePair<string,int>(
row.Field<string>( "FullName" ) ,
row.Field<int>( "ID" ) ) )
.Prepend( new KeyValuePair<string , int>( "---" , -1 ) )
.ToArray();
dtTeamIds.Rows.Clear();
foreach( var item in items )
{
var row = dtTeamIds.NewRow();
row.SetField( dtTeamIds.Columns[ "MyValue" ] , item.Value );
row.SetField( dtTeamIds.Columns[ "MyText" ] , item.Key );
dtTeamIds.Rows.Add( row );
}
}
private void initDataGridViews()
{
// Players
{
var dgv = dataGridViewPlayers;
dgv.AutoGenerateColumns = false;
dgv.CausesValidation = false;
var sourceTable = dataSet.Tables[ "Players" ];
var dataSrc = sourceTable;
dgv.Columns.Clear();
dgv.DataSource = new BindingSource() {
DataSource = dataSrc ,
};
dgv.Columns.Add( new DataGridViewTextBoxColumn() {
DataPropertyName = "ID" ,
Visible = false ,
} );
// dowpdown-list
{
dtTeamIds.Columns.Add( "MyText" );
dtTeamIds.Columns.Add( "MyValue" );
updateTeamIds();
dgv.Columns.Add( new DataGridViewComboBoxColumn() {
DataPropertyName = "TeamID" ,
HeaderText = dataSrc.Columns[ "TeamID" ].Caption ,
ToolTipText = "Press 'CTRL + 0' do delete the team!" ,
ValueType = typeof( int ) ,
DisplayMember = "MyText" ,
ValueMember = "MyValue" ,
DataSource = new BindingSource( dtTeamIds , null ) ,
} );
}
SetupDataGridViewDefaults( dgv );
// HACK: see: https://social.msdn.microsoft.com/Forums/en-US/243da031-760d-4e7f-b09f-be4cfa5a6a4b
dataSrc.ColumnChanging += ( s , e ) => {
if( e.Column == dataSrc.Columns[ "TeamID" ] )
{
var t2 = e.ProposedValue.GetType();
if( e.ProposedValue == null || ( e.ProposedValue is int val && val == -1 ) )
{
//e.ProposedValue = DBNull.Value; // -1 or NULL
}
}
};
//dgv.CellEnter += ( s , e ) => {
// if( e.ColumnIndex == dgv.Columns[ "TeamID" ].Index )
// {
// updateTeamIds();
// }
//};
//dgv.CellBeginEdit += ( s , e ) => {
// if( e.ColumnIndex == dgv.Columns[ "TeamID" ].Index )
// {
// updateTeamIds();
// }
//};
dgv.Click += ( s , e ) => {
updateTeamIds();
//if( e.ColumnIndex == dgv.Columns[ "TeamID" ].Index )
//{
// var cell = dgv[ e.ColumnIndex , e.RowIndex ] as DataGridViewComboBoxCell;
//}
};
dgv.CellFormatting += ( s , e ) => {
if( e.ColumnIndex == dgv.Columns[ "TeamID" ].Index )
{
var cell = dgv[ e.ColumnIndex , e.RowIndex ] as DataGridViewComboBoxCell;
//var _1 = cell.EditedFormattedValue;
//var _2 = cell.FormattedValue;
var _3 = cell.Value; // null
var _4 = e.Value; // null
var _5 = e.DesiredType; // string
}
};
}
// setup teams-table etc..
}
private void SetupDataGridViewDefaults( DataGridView dgv )
{
// TODO: figure out how to properly resize columns
foreach( var colIdx in Enumerable.Range( 0 , dgv.Columns.Count ) )
{
var col = dgv.Columns[ colIdx ];
col.Name = col.DataPropertyName;
col.MinimumWidth = 50;
col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
if( colIdx == dgv.Columns.Count - 1 )
col.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
col.Width = col.Width; //This is important, otherwise the following line will nullify your previous command
col.AutoSizeMode = DataGridViewAutoSizeColumnMode.NotSet;
}
dgv.AutoResizeColumns( DataGridViewAutoSizeColumnsMode.AllCells );
dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
dgv.Columns[ dgv.Columns.Count - 1 ].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgv.AllowUserToResizeColumns = true;
dgv.AllowUserToOrderColumns = false;
dgv.AllowUserToResizeRows = true;
dgv.Enabled = true;
dgv.Refresh();
}
Now, when I start the application, initially the dropdown-list is empty (no selection, not the "---"). When editing the combobox-cell the teams and the default "---" are shown and the selected item on top becomes the "---" item. When clicking in any other cell after doing any change to the combobox-cell to a team, I get a FormatException (CBox Cell Value is not valid).
I tried to get information on where or why the value isn't valid, checking CellBegin/EndEdit, Formattin, Changed etc Events, however I haven't done any progress on where this might come from.
Did I do something wrong in the setup? Is there an easier way?
