Convert text column to combobox column in an object bound DataGridView control

0
votes

I have a class called Contacts which contains a collection of objects called PhoneNumbers. Each PhoneNumbers object has several properties including PhoneNumber and PhoneType as well as the foreign key PhoneTypeFK. I assign this collection to a DataGridView control’s DataSource and hide the columns (properties) I don’t want to see in the DGV. This creates a bunch of text cells (rows/cols) in the DGV being for the Phone Number and the Type. All well and good except, I want a combobox to be shown in the Phone Type column populated with all the various Phone Types of the PhoneTypes Table with the appropriate Phone Type shown in the cell for that Phone Number.
I read somewhere that I need to add a combobox column at design time with the same DataPropertyName as the Property of the PhoneNumbers Object, i.e. PhoneType so that when the DGV is populated with columns it will use this column instead of creating a new one (for the PhoneType). I cannot, however, get this to work. I used the code below to populate the DGV and hide the irrelevant columns:

//Fill Grid
uxContactPhoneNumbersGrd.DataSource = contacts.PhoneNumbers;

//Hide non-required columns/rows
uxContactPhoneNumbersGrd.RowHeadersVisible = false;
string[] columnNamesToHide = { "ErrMsg", "ContactsFk", "PhoneNumbersPk", "PhoneTypesFk" };
SAPSCommon.Instance.HideGridColumns(ref uxContactPhoneNumbersGrd, columnNamesToHide);

When I do this, I get 2 columns for the PhoneType, one is the text cell created when populating the DGV, the other is the combobox column I added at design time (even though it has the same DataPropertyName as suggested).

How do I get only 1 column for PhoneType and how do I bind it to the PhoneTypes Table so that the data from the PhoneNumbers Objects sets the correct PhoneType for the respective PhoneNumber? (Do I need to bind the PhoneType combobox column 1st before populating the grid with the PhoneNumbers Objects?)

1
c#winformsdatagridviewcombobox
Is this question too hard? Did I phrase it wrong or provide insufficient detail? Anyone? – Walter Kiess

1 Answers

1
votes

OK, so after MANY hours of trial and error I seem to have found a solution.

The basic process is to:

  1. Place a DataGridView control on the form
  2. Create the 1st column of the grid as a textbox column object and set its DataPropertyName the same as the PhoneNumber Property name of the PhoneNumbers Object
  3. Create the 2nd column of the grid as a comboxbox column object and set its DataPropertyName the same as the PhoneType KEY Property name of the PhoneNumbers object
  4. Bind the combobox column to the supporting table
  5. Add the columns to the grid
  6. Set the grid's AutoGenerateColumns property to False
  7. Set the grids DataSource to the PhoneNumbers Object

The reason I name the DataPropertyName of the grid columns to be the same as the PhoneNumber object Property names is so that the grid automatically populates the correct columns with the data from the list of PhoneNumber objects. Disabling AutoGenerateColumns ensures only the named columns are populated, i.e. the grid does not automatically generate columns for the other properties of the PhoneNumber objects which are not required for this application.

This is the PhoneNumber class which is used to create a list of PhoneNumber objects in the Contacts object:

using System;
using System.Data;
using System.Text;

namespace SAPS
{
public class clsPhoneNumber
{
    #region Fields (4) 

    private int _contacts_FK;
    private string _errMsg;
    private string _phoneNumber;
    private int _phoneNumbers_PK;
    private int _phoneTypes_FK;
    private string _phoneType;

    #endregion Fields 

    #region Constructors (1) 

    public  clsPhoneNumber()
    {
        _errMsg = "";
        _phoneNumbers_PK = 0;
        _phoneTypes_FK = 0;
        _phoneType = "";
        _phoneNumber = "";
        _contacts_FK = 0;
    }

    #endregion Constructors 

    #region Properties (4) 

    public int ContactsFk
    {
        get { return _contacts_FK; }
        set { _contacts_FK = value; }
    }

    public string ErrMsg
    {
        get { return _errMsg; }
        set { _errMsg = value; }
    }

    public string PhoneNumber
    {
        get { return _phoneNumber; }
        set { _phoneNumber = SAPSCommon.Instance.StripNonNumerics(value); }
    }

    public int PhoneNumbersPK
    {
        get { return _phoneNumbers_PK; }
        set { _phoneNumbers_PK = value; }
    }

    public int PhoneTypesFK
    {
        get { return _phoneTypes_FK; }
        set { _phoneTypes_FK = value; }
    }

    public string PhoneType
    {
        get { return _phoneType; }
    }

    #endregion Properties 

    #region Methods (2) 

    // Public Methods (1) 

    /// <summary>
    /// Get the Notes for the specified key
    /// </summary>
    /// <param name="TableID">The Table Primary Key</param>
    /// <returns>An Object containing data for the specified Primary Key</returns>
    public clsPhoneNumber GetData(int TableID)
    {
        AssignProperties(SAPSCommon.Instance.ReadTable("PhoneNumbers", "PN_PhoneNumbers_PK", TableID));
        return this;
    }
    // Private Methods (1) 

    /// <summary>
    /// Assigns the table's data to the properties of the Data Object.
    /// This method must be hand coded for each table.
    /// </summary>
    /// <param name="ds">A Dataset containing the data record read from the Table</param>
    private void AssignProperties(DataSet ds)
    {
        //Assign properties with database data
        try
        {
            //Primary Key for Table
            _phoneNumbers_PK = ds.Tables[0].Rows[0].Field<int>("PN_PhoneNumbers_PK");

            //The rest of the data fields
            _contacts_FK = ds.Tables[0].Rows[0].Field<int>("PN_Contacts_FK");
            _phoneNumber = FormatPhoneNumber(ds.Tables[0].Rows[0].Field<string>("PN_PhoneNum"));
            _phoneTypes_FK = ds.Tables[0].Rows[0].Field<int>("PN_PhoneTypes_FK");

            //Follow links of Foreign Keys
            DataTable dt = new DataTable();
            string sqlSelect =
                string.Format(
                    "SELECT PT_Description FROM Pensions.dbo.PhoneTypes WHERE PT_PhoneTypes_PK = '{0}'",
                    _phoneTypes_FK);             
            dt = SQLCommon.Instance.SQLSelect(sqlSelect);
            _phoneType = dt.Rows[0].Field<string>("PT_Description");
        }
        catch (Exception e)
        {
            _errMsg = e.Message;
            SAPSCommon.Instance.ShowErrorMsg(e.Message);
        }
    }

    /// <summary>
    /// Format an Australian Phone number
    /// </summary>
    /// <param name="Num">Phone Number to format in string format</param>
    /// <returns>Formatted Phone Number</returns>
    private string FormatPhoneNumber(string Num)
    {
        if (Num.Substring(0, 2) == "04") //Mobile Number
        {
            return string.Format("{0:0### ### ###}", Convert.ToInt64(Num));
        }
        return string.Format("{0:(0#) #### ####}", Convert.ToInt64(Num));
    }

    #endregion Methods 

    public string  Update()
    {
        StringBuilder sb = new StringBuilder("UPDATE [");
        sb.Append(Properties.Settings.Default.SQLDatabaseName);
        sb.Append("].[dbo].[PhoneNumbers] SET PN_Contacts_FK='");
        sb.Append(_contacts_FK);
        sb.Append("', PN_PhoneTypes_FK='");
        sb.Append(_phoneTypes_FK);
        sb.Append("', PN_PhoneNum='");
        sb.Append(_phoneNumber);
        sb.Append("' WHERE PN_PhoneNumbers_PK='");
        sb.Append(_phoneNumbers_PK);
        sb.Append("'");

        _errMsg = SQLCommon.Instance.SQLUpdate(sb.ToString());
        return _errMsg;
    }
}

}

This is the code that populates the DataGridView control:

        private void PopulatePhoneNumbers(clsContacts contacts)
    {
        //Create the 1st column as a textbox for the Phone Number
        DataGridViewTextBoxColumn tb = new DataGridViewTextBoxColumn();
        tb.Name = "PhoneNumber";
        tb.DataPropertyName = "PhoneNumber"; //This is the name of the PhoneNumber object Property for Phone Number

        //Create  2nd column as combobox for PhoneType
        DataGridViewComboBoxColumn cb = new DataGridViewComboBoxColumn();
        cb.Name = "PhoneTypes";
        cb.DataPropertyName = "PhoneTypesFK"; //This is the name of the PhoneNumber object Property for Phone Type

        //Bind the cb to the table
        string sqlQuery = "SELECT PT_PhoneTypes_PK, PT_Description " +
                          "FROM [Pensions].[dbo].[PhoneTypes] ";
        DataTable dtPhoneTypes = SQLCommon.Instance.SQLSelect(sqlQuery);
        cb.DataSource = dtPhoneTypes;
        cb.ValueMember = dtPhoneTypes.Columns["PT_PhoneTypes_PK"].ColumnName;
        cb.DisplayMember = dtPhoneTypes.Columns["PT_Description"].ColumnName;

        uxContactPhoneNumbersGrd.Columns.Add(tb);
        uxContactPhoneNumbersGrd.Columns.Add(cb);

        uxContactPhoneNumbersGrd.AutoGenerateColumns = false;

        if (contacts.PhoneNumbers != null)
        {
            //Show how many phone numbers
            uxContactPhoneNumbersLbl.Text = string.Format("Phone Numbers ({0})", contacts.PhoneNumbers.Count);
            uxContactPhoneNumbersLbl.Visible = true;
            //Fill Grid
            uxContactPhoneNumbersGrd.DataSource = contacts.PhoneNumbers;
            //Hide non-required columns/rows
            uxContactPhoneNumbersGrd.RowHeadersVisible = false;
        }

        //Adjust text column size and auto wrap
        uxContactPhoneNumbersGrd.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        uxContactPhoneNumbersGrd.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
   }

I hope this helps some other poor soul out there trying to make the DataGridView control display data from an object using a support table...