Change Datagridview cell entyre column to combobox with combobox items list.

Question

Hi,

On a form, there is a datagridview that I fill with the help of a query that reads a SQlite table.
After the datagridview shows the data I want to provide 1 column with a combobox and in the list of the combobox all values ​​from that column must be included.
What I have so far does not work properly. I can click twice on a cell and choose a value from the combobox list. Sometimes it works and sometimes I get:
"Message" Operation is not valid because it results in a reentrant call to the SetCurrentCellAddressCore function. "
It seems like it only works in the first 2 rows.

What is wrong?

private DataGridViewComboBoxCell comboCell;
private List<string> exportLocation = new();

private void DataGridViewQgroups_CellEnter(object sender, DataGridViewCellEventArgs e)
{
    this.exportLocation.Clear();

    this.comboCell = new();
    this.comboCell = this.CreateComboBoxItemlist(this.DataGridViewQgroups);

    if (e.ColumnIndex == 3 && this.comboCell.DataSource != null)
    {
        this.DataGridViewQgroups.Rows[e.RowIndex].Cells[3] = (DataGridViewComboBoxCell)this.comboCell;

        this.DataGridViewQgroups.BeginEdit(true);
        ((ComboBox)this.DataGridViewQgroups.EditingControl).DroppedDown = true;
    }
}


private DataGridViewComboBoxCell CreateComboBoxItemlist(DataGridView dgv)
{
    // Create the list only once
    if (this.exportLocation.Count == 0)
    {
        string columnName = "Export_locatie";

        foreach (DataGridViewRow item in this.DataGridViewQgroups.Rows)
        {
            if (item.Cells[columnName].Value != null)
            {
                this.exportLocation.Add(item.Cells[columnName].Value.ToString());
            }
        }

        DataGridViewComboBoxCell combo = new DataGridViewComboBoxCell();
        combo.DataSource = this.exportLocation;

        combo.AutoComplete = true;
        combo.MaxDropDownItems = 10;

        combo.FlatStyle = FlatStyle.Flat;
        combo.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;  // You see only a combobox when editing

        return combo;
    }
    else
    {
        return null;
    }
}

Answer 1

Hello,

The following code sample uses SQL-Server yet is irrelevant as how the code is setup the data can come from any database e.g. SQlite, Oracle, SQL-Server, MS-Access etc.

• As Shown in the image there are two DataGridViewComboBox columns.
• To get current row values, see this code which if needed you can send back to the database.
• Data operations are in the following class.

Now one might ask, is the easier way, well that depends, yes and no while I wrote this to be optimal rather than think about simple as simple may work but usually is missing something or is not wise e.g. access cells of the DataGridView for values rather than from a BindingSouce (and a BindingSource has many benefits to check out).

Data class

using System.Data;  
using System.Data.SqlClient;  
  
namespace DataGridViewCombo1.Classes  
{  
    public class Operations  
    {  
        /*  
         * Make sure the databasescript file is executed and that the  
         * Data Source points to the correct server if not SQL-Server Express.  
         */  
        private string ConnectionString = "Data Source=.\\sqlexpress;Initial " +  
            "Catalog=DataGridViewCodeSample;Integrated Security=True";  
  
        public DataTable ColorDataTable { get; set; }  
        public DataTable VendorDataTable { get; set; }  
  
        private DataTable _customerTable = new DataTable();  
        public DataTable CustomerDataTable  
        {  
            get => _customerTable;  
            set => _customerTable = value;  
        }  
  
        /// <summary>  
        /// Load main table for DataGridView  
        /// </summary>  
        public void LoadCustomerDataTable()  
        {  
            CustomerDataTable = new DataTable();  
  
            using (var cn = new SqlConnection { ConnectionString = ConnectionString })  
            {  
                using (var cmd = new SqlCommand { Connection = cn })  
                {  
                    cn.Open();  
                    cmd.CommandText = "SELECT id,Item,ColorId,CustomerId, qty, InCart, VendorId  FROM Product";  
                    CustomerDataTable.Load(cmd.ExecuteReader());  
                }  
            }  
        }  
  
        /// <summary>  
        /// Load color reference table  
        /// </summary>  
        public void LoadColorsReferenceDataTable()  
        {  
            ColorDataTable = new DataTable();  
  
            using (var cn = new SqlConnection { ConnectionString = ConnectionString })  
            {  
                using (var cmd = new SqlCommand { Connection = cn })  
                {  
                    cn.Open();  
                    cmd.CommandText = "SELECT ColorId,ColorText FROM Colors";  
                    ColorDataTable.Load(cmd.ExecuteReader());  
                }  
            }  
        }  
  
        /// <summary>  
        /// Load vendor reference table  
        /// </summary>  
        public void LoadVendorsReferenceDataTable()  
        {  
            VendorDataTable = new DataTable();  
  
            using (var cn = new SqlConnection { ConnectionString = ConnectionString })  
            {  
                using (var cmd = new SqlCommand { Connection = cn })  
                {  
                    cn.Open();  
                    cmd.CommandText = "SELECT VendorId,VendorName FROM dbo.Vendors";  
                    VendorDataTable.Load(cmd.ExecuteReader());  
                }  
            }  
        }  
    }  
}  
  

Frontend code

using System;  
using System.Data;  
using System.Drawing;  
using System.Linq;  
using System.Threading;  
using System.Threading.Tasks;  
using System.Windows.Forms;  
using DataGridViewCombo1.Classes;  
  
namespace DataGridViewCombo1  
{  
    public partial class Form1 : Form  
    {  
        readonly Operations _operations = new Operations();  
  
        readonly BindingSource _customerBindingSource = new BindingSource();  
        readonly BindingSource _vendorBindingSource = new BindingSource();   
        readonly BindingSource _colorBindingSource = new BindingSource();   
  
        public Form1()  
        {  
            InitializeComponent();  
  
            CustomersDataGridView.CurrentCellDirtyStateChanged += _CurrentCellDirtyStateChanged;  
        }  
        private void Form1_Load(object sender, EventArgs e)  
        {  
            Setup();  
  
            CustomersDataGridView.AllowUserToAddRows = false ;  
            _customerBindingSource.PositionChanged += _customerBindingSource_PositionChanged;  
  
            LoadData();  
            CurrentValuesView();  
  
            ActiveControl = CustomersDataGridView;  
  
        }  
  
        private void _customerBindingSource_PositionChanged(object sender, EventArgs e)  
        {  
            CurrentValuesView();  
        }  
  
        private void Setup()  
        {  
  
            _operations.LoadColorsReferenceDataTable();  
            _operations.LoadVendorsReferenceDataTable();  
            _vendorBindingSource.DataSource = _operations.VendorDataTable;  
            _colorBindingSource.DataSource = _operations.ColorDataTable;  
  
  
            ColorComboBoxColumn.DisplayMember = "ColorText";  
            ColorComboBoxColumn.ValueMember = "ColorId";  
            ColorComboBoxColumn.DataPropertyName = "ColorId";  
            ColorComboBoxColumn.DataSource = _colorBindingSource;  
            ColorComboBoxColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;  
            VendorComboBoxColumn.SortMode = DataGridViewColumnSortMode.Automatic;  
  
            VendorComboBoxColumn.DisplayMember = "VendorName";  
            VendorComboBoxColumn.ValueMember = "VendorId";  
            VendorComboBoxColumn.DataPropertyName = "VendorId";  
            VendorComboBoxColumn.DataSource = _vendorBindingSource;  
            VendorComboBoxColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;  
            VendorComboBoxColumn.SortMode = DataGridViewColumnSortMode.Automatic;  
  
            QtyNumericUpDownColumn.DataPropertyName = "qty";  
            InCartCheckBoxColumn.DataPropertyName = "InCart";  
  
        }  
        private void LoadData()  
        {  
  
            _operations.LoadCustomerDataTable();  
  
            CustomersDataGridView.AutoGenerateColumns = false;  
  
            ItemTextBoxColumn.DataPropertyName = "Item";  
            _customerBindingSource.DataSource = _operations.CustomerDataTable;  
  
            CustomersDataGridView.DataSource = _customerBindingSource;  
  
            //see comments in event code.  
            //CustomersDataGridView.CellFormatting += CustomersDataGridView_CellFormatting;  
  
        }  
        /// <summary>  
        /// To be moved out of here, placed here to reply to a forum question.  
        /// </summary>  
        /// <param name="sender"></param>  
        /// <param name="e"></param>  
        private void CustomersDataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)  
        {  
  
            if (e.ColumnIndex == CustomersDataGridView.Columns["ColorComboBoxColumn"].Index && CustomersDataGridView.Rows[e.RowIndex].DataBoundItem != null)  
            {  
                var colorKeyValue = ((DataRowView)CustomersDataGridView.Rows[e.RowIndex].DataBoundItem).Row.Field<int>("ColorId");  
                if (colorKeyValue == 2)  
                {  
                    //CustomersDataGridView.Rows[e.RowIndex].Cells[CustomersDataGridView.Columns["ColorComboBoxColumn"].Index].Style = new DataGridViewCellStyle { ForeColor = Color.White, BackColor = Color.Tomato };  
  
                    CustomersDataGridView.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Yellow;  
                }  
                else  
                {  
                    //CustomersDataGridView.Rows[e.RowIndex].Cells[CustomersDataGridView.Columns["ColorComboBoxColumn"].Index].Style = null;  
                    CustomersDataGridView.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Empty;  
                }  
            }  
        }  
  
        private void _CurrentCellDirtyStateChanged(object sender, EventArgs e)  
        {  
            CustomersDataGridView.CurrentCellDirtyStateChanged -= _CurrentCellDirtyStateChanged;  
            CustomersDataGridView.CommitEdit(DataGridViewDataErrorContexts.Commit);  
            CustomersDataGridView.CurrentCellDirtyStateChanged += _CurrentCellDirtyStateChanged;  
        }  
         
  
        private void CurrentValuesView()  
        {  
  
            if (_customerBindingSource.Current == null)  
            {  
                return;  
            }  
  
            #region Get primary table information  
  
            var customerRow = ((DataRowView)_customerBindingSource.Current).Row;  
            var customerPrimaryKey = customerRow.Field<int>("Id");  
            var colorKey = customerRow.Field<int>("ColorId");  
            var vendorKey = customerRow.Field<int>("VendorId");  
  
            #endregion  
  
            #region Get child table information  
  
            var vendorName = ((DataTable)_vendorBindingSource.DataSource)  
                .AsEnumerable()  
                .FirstOrDefault(row => row.Field<int>("VendorId") == vendorKey)  
                .Field<string>("VendorName");  
  
            var colorName = ((DataTable)_colorBindingSource.DataSource)  
                .AsEnumerable()  
                .FirstOrDefault(row => row.Field<int>("ColorId") == colorKey)  
                .Field<string>("ColorText");  
  
            #endregion  
  
  
            DisplayInformationTextBox.Text =  
                $"PK: {customerPrimaryKey} Vendor key {vendorKey} vendor: {vendorName} color id: {colorKey} - {colorName}";  
        }  
    }  
}  
  
  

Answer 2

Look at the form designer code in my code sample, all that the designer does is

Creates a new column which you can do in the form in Setup() method, use var rather than private.

private System.Windows.Forms.DataGridViewComboBoxColumn ColorComboBoxColumn;

Then
Drop this code into Setup() and modify as needed

this.ColorComboBoxColumn.HeaderText = "Color";
this.ColorComboBoxColumn.Name = "ColorComboBoxColumn";
this.ColorComboBoxColumn.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic;

Then add the column to the collection of columns.

Otherwise look at this code sample which I dig up from VS2013