question

Hans-7463 avatar image
0 Votes"
Hans-7463 asked karenpayneoregon commented

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

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;
     }
 }
dotnet-csharpwindows-forms
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, I do not see it. My datagridview does not have column names when the form starts. The column names come from the query result. So something like "ColorComboBoxColumn.DisplayMember = ...;" will not work.
Is there a way to connect a combobox to the datagridview cell after the view shows the data?

Greetings

0 Votes 0 ·

There is only one line in my code where is goed wrong. this is:

 this.DataGridViewQgroups.Rows[e.RowIndex].Cells[3] = (DataGridViewComboBoxCell)this.comboCell;

When the cell in which I click is empty I get the error: Operation is not valid because it results in a reentrant call to the SetCurrentCellAddressCore function


When the cell I click on has a value it works, I get a combobox with a list of items.

So the empty cell seems the problem for me.

0 Votes 0 ·

When the cell in which I click is empty I get the error: Operation is not valid because it results in a reentrant call to the SetCurrentCellAddressCore function

Exactly but you should not be asking a cell for a value, instead look at the data container be it a instance of a specific class or a DataRow when the DataGridView data source is set.

So if you don't want to try my suggestions I have nothing else to offer.
0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered karenpayneoregon edited

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}";
         }
     }
 }

84270-f1.png

84219-f2.png

84287-f3.png





f1.png (27.1 KiB)
f2.png (17.0 KiB)
f3.png (17.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.