none
Bind ComboBoxes in DataGridView to DataTable RRS feed

  • Question

  • I would like to bind 2 combo boxes in my DataGridView to 2 fields (one field per combo box) in a DataTable so that if the DataTable already has a value, that value will show in the combobox. If the combo boxes don't have a value, I would like the combo box to show a list of values the user can select from. I have created a binding source and have set the DataGridView (dgdMembers) datasource to that binding source. I have also set the binding source datasource to a DataTable. When I fill the DataTable the rows appear in my DataGridView. The SELECT statement to fill the datatable is selecting the fields relActMSPStatus and relTransActionCd. These are the fields that I would like to bind to the 2 comboboxes.

    Need a example of how to do this, my code is not working. Thank you.

    private void frmOutReach_Grid_Load(object sender, EventArgs e)
            {
                cboGroups.Items.AddRange(GetGroups());
    
                bindingSource1 = new BindingSource();
    
    
                // Bind the DataGridView to the BindingSource
                dgdMembers.DataSource = bindingSource1;
    
    
            }
    
    private void btnShow_Click(object sender, EventArgs e)
            {
                if (cboGroups.SelectedIndex == -1)
                {
                    message = "Please select an insurer group";
                    caption = "Insurer Group Select";
                    MessageBoxButtons buttons = MessageBoxButtons.OK;
    
                    // Display the MessageBox.
                    MessageBox.Show(this, message, caption, buttons);
    
                    return;
                   
                }
    
                GetCOBNonHorizonRecsForGrp();
    
            }
    
     private void GetCOBNonHorizonRecsForGrp()
            {
    
                CreateDataTable();
    
                string GroupName = cboGroups.Text;
    
                //Populate dataTable
                SqlCommand cmd1 = new SqlCommand("Insurer_Select_Grid", sqlConnection1);
                cmd1.Parameters.Clear();
                cmd1.Parameters.Add(new SqlParameter("@InsurerGroup", GroupName));
                cmd1.CommandType = CommandType.StoredProcedure;
    
    
                da = new SqlDataAdapter(cmd1);
                da.Fill(dataTable);
                bindingSource1.DataSource = dataTable;
               
    
               
    
                sqlConnection1.Close();
    
                // Resize the DataGridView columns to fit the newly loaded content.
                dgdMembers.AutoResizeColumns(
                    DataGridViewAutoSizeColumnsMode.AllCells);
    
    
                setUpMemberGrid();
            }
    
     private void CreateDataTable()
            {
                //Create dataset
                cobNonHorizon = new DataSet("cobNonHorizon");
    
                //Create dataTable
                dataTable = new DataTable("dataTable");
    
                //Create table for IKA spans
                dtIKASpans = new DataTable("dtIKASpans");
            }
    
    
    private void setUpMemberGrid()
            {
    
                //Refresh Data Grid View
                dgdMembers.Refresh();
    
                //Freeze columns for scrolling
                this.dgdMembers.Columns["relHICN"].Frozen = true;
                this.dgdMembers.Columns["relRelation"].Frozen = true;
                this.dgdMembers.Columns["relSubscriberFirst"].Frozen = true;
                this.dgdMembers.Columns["relSubscriberLast"].Frozen = true;
    
                //Set row number
                setRowNumber();
    
                //Create MSP ComboBox
                dgdMembers.Columns["relActMSPStatus"].Visible = true;
                DataGridViewComboBoxColumn cmbMSP = new DataGridViewComboBoxColumn();
                cmbMSP.Items.Add(" ");
                cmbMSP.Items.Add("MSP");
                cmbMSP.Items.Add("Partial MSP");
                cmbMSP.Items.Add("No MSP");
                cmbMSP.HeaderText = "Actual MSP";
                cmbMSP.Name = "MSPVal";
                cmbMSP.FlatStyle = FlatStyle.Flat;
                cmbMSP.DataSource = dataTable;
                cmbMSP.DisplayMember = "dataTable.relActMSPStatus";
                cmbMSP.ValueMember = "dataTable.relHICN";
                //dgdMembers.Columns.Add(cmbMSP);
                dgdMembers.EditingControlShowing += new DataGridViewEditingControlShowingEventHandler(dgdMembers_EditingControlShowing);
    
                //Create Transaction Code ComboBox
                dgdMembers.Columns["relTransactionCd"].Visible = true;
                DataGridViewComboBoxColumn cmbTransCd = new DataGridViewComboBoxColumn();
                LoadTrans();
                cmbTransCd.DataSource = Srcds.Tables[0];
                cmbTransCd.DisplayMember = "SrcVal";
                cmbTransCd.ValueMember = "SrcVal";
                cmbTransCd.HeaderText = "Trans Action";
                cmbTransCd.Name = "TransAction";
                cmbTransCd.FlatStyle = FlatStyle.Flat;
                cmbTransCd.DropDownWidth = 200;
                cmbTransCd.Width = 200;
                dgdMembers.Columns.Add(cmbTransCd);
                
    
                //Don't display group name
                dgdMembers.Columns["Group Name"].Visible = true;
    
                //Gray out rows where the Trans Action code and MSP values have been determined
                GrayOutRow();
    
                //Call procedure to load IKA spans
                RefreshDataGrid();
    
                /* DetermineStatus(); */
    
            }

    Tuesday, February 6, 2018 9:20 PM

All replies

  • Will repost
    Tuesday, February 6, 2018 11:58 PM
    Moderator
  • Okay, here is what I believe you are after (correct me if I'm wrong). 

    Two DataGridViewComboBox controls, if no formal selection present them with a message to select and within it have list.

    I took the following code sample as a base and added another table to add a second ComboBox to the DataGridView.

    Products table

    Vendor table

    Colors table

    Customers table

    Code to load all data in a separate class from the form.

    using System.Data;
    using System.Data.SqlClient;
    
    namespace DataGridViewComboCS.Classes
    {
        public class Operations
        {
            private string ConnectionString = "Data Source=KARENS-PC;Initial " + 
                "Catalog=DataGridViewCodeSample;Integrated Security=True";
    
            public DataTable ColorTable { get; set; }
            public DataTable VendorTable { get; set; }
    
            private DataTable _CustomerTable = new DataTable();
            public DataTable CustomerTable
            {
                get
                {
                    return _CustomerTable;
                }
                set
                {
                    _CustomerTable = value;
                }
            }
            /// <summary>
            /// Load products by customer id
            /// </summary>
            /// <param name="pCustomerId"></param>
            public void LoadDataGridViewTable()
            {
                CustomerTable = new DataTable();
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cn.Open();
                        cmd.CommandText = "SELECT id,Item,ColorId,CustomerId, qty, InCart, VendorId  FROM Product"; 
                        CustomerTable.Load(cmd.ExecuteReader());
                    }
                }
            }
            /// <summary>
            /// Load customers
            /// </summary>
            /// <returns></returns>
            public DataTable LoadCustomers()
            {
                DataTable custTable = new DataTable();
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cn.Open();
                        cmd.CommandText = "SELECT CustomerId,FirstName + ',' + LastName AS FullName FROM Customer";
                        custTable.Load(cmd.ExecuteReader());
                    }
                }
                return custTable;
            }
            /// <summary>
            /// Load color options for DataGridView ComboBox
            /// </summary>
            public void LoadColorsReferenceTable()
            {
                ColorTable = new DataTable();
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cn.Open();
                        cmd.CommandText = "SELECT ColorId,ColorText FROM Colors";
                        ColorTable.Load(cmd.ExecuteReader());
                    }
                }
            }
            public void LoadVendorsReferenceTable()
            {
                VendorTable = new DataTable();
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cn.Open();
                        cmd.CommandText = "SELECT VendorId,VendorName FROM dbo.Vendors";
                        VendorTable.Load(cmd.ExecuteReader());
                    }
                }
            }
        }
    }

    Form code

    using DataGridViewComboCS.Classes;
    using DataGridViewComboCS.Controls;
    using DataGridViewComboCS.Extensions;
    using System;
    using System.Data;
    using System.Linq;
    using System.Threading;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace DataGridViewComboCS
    {
        public partial class Form1 : Form
        {
            Operations ops = new Operations();
            BindingSource bsItems = new BindingSource();
            public Form1()
            {
                InitializeComponent();
                DataGridView1.CurrentCellDirtyStateChanged += _CurrentCellDirtyStateChanged;
                DataGridView1.EditingControlShowing += DataGridView1_EditingControlShowing;
            }
            private void Form1_Load(object sender, EventArgs e)
            {
                Setup();
                DataGridView1.AllowUserToAddRows = false ;
                LoadData();
            }
            void Setup()
            {
    
                ops.LoadColorsReferenceTable();
                ops.LoadVendorsReferenceTable();
    
                ColorComboBoxColumn.DisplayMember = "ColorText";
                ColorComboBoxColumn.ValueMember = "ColorId";
                ColorComboBoxColumn.DataPropertyName = "ColorId";
                ColorComboBoxColumn.DataSource = ops.ColorTable;
                ColorComboBoxColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
    
    
                VendorComboBoxColumn.DisplayMember = "VendorName";
                VendorComboBoxColumn.ValueMember = "VendorId";
                VendorComboBoxColumn.DataPropertyName = "VendorId";
                VendorComboBoxColumn.DataSource = ops.VendorTable;
                VendorComboBoxColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
    
                QtyNumericUpDownColumn.DataPropertyName = "qty";
                InCartCheckBoxColumn.DataPropertyName = "InCart";
    
            }
            void LoadData()
            {
    
                ops.LoadDataGridViewTable();
    
                DataGridView1.AutoGenerateColumns = false;
    
                ItemTextBoxColumn.DataPropertyName = "Item";
                bsItems.DataSource = ops.CustomerTable;
    
                DataGridView1.DataSource = bsItems;
    
            }
            private void _CurrentCellDirtyStateChanged(object sender, EventArgs e)
            {
                if (DataGridView1.CurrentCell is DataGridViewCheckBoxCell)
                {
                    DataGridView1.CurrentCellDirtyStateChanged -= _CurrentCellDirtyStateChanged;
                    DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit);
                    DataGridView1.CurrentCellDirtyStateChanged += _CurrentCellDirtyStateChanged;
    
                    int ItemPrimaryKeyValue = ((DataRowView)bsItems.Current).Row.Field<int>("id");
    
                }
            }
            private void DataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
            {
    
                if (DataGridView1.CurrentCell.IsComboBoxCell())
                {
                    if (DataGridView1.Columns[DataGridView1.CurrentCell.ColumnIndex].Name == "ColorComboBoxColumn")
                    {
                        ComboBox cb = e.Control as ComboBox;
                        cb.SelectionChangeCommitted -= _SelectionChangeCommittedForColorColumn;
                        cb.SelectionChangeCommitted += _SelectionChangeCommittedForColorColumn;
                    }
                }
    
            }
    
            void _SelectionChangeCommittedForColorColumn(object sender, EventArgs e)
            {
                int ItemPrimaryKeyValue = ((DataRowView)bsItems.Current).Row.Field<int>("id");
                int ColorIdPrimaryKeyValue = ((DataRowView)(((DataGridViewComboBoxEditingControl)sender)
                    .SelectedItem)).Row.Field<int>("ColorId");
            }
            private void button1_Click(object sender, EventArgs e)
            {
                // here 4 is "Select" because no vendor was set
                var allRowsHaveVendorSelected = ((DataTable)bsItems.DataSource)
                    .AsEnumerable().Any(row => row.Field<int>("VendorId") == 4);
    
                if (allRowsHaveVendorSelected)
                {
                    MessageBox.Show("One or more fields needs a vendor");
                }
            }
        }
    }
    
    

    Form at design time


    Run time

    Button1 code allows us to see if any row has no vendor

    If I'm off base, sorry (but it was fun to modify the original code sample to get this).


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, February 7, 2018 12:47 AM
    Moderator