Objective
The purpose of this article is to provide a working example in C++/CLI of a combo column on a DataGridView whose dropdown list uses different subsets depending on the value selected on another combobox column.
Background
I found it very difficult to find an example of this, but two good sources in C# eventually turned up, one from MSDN and the other the DataGridViewFAQ document. They involved a confusing mix of internal Tables, DataSets, and BindingSources. This was my first time to meet these features. Because my dataset is small, I load the full range of values for the second combobox up front and filter internally in the code. This reduces database accesses but on a larger dataset could lead to inefficient memory use. I made an attempt to reload the second combo column every time the first one changed, but was unsuccessful. It looks possible in C#, because if my reading of it is correct, this is what the DataGridViewFaq does.
Example origin
The example is drawn from a ticketing system I am working on, where your choice of Stage is dependant on your choice of Route.
The idea is that passengers can be configured with a default route/stage combination to speed up the transaction process when they are purchasing new tickets.
The system has tables with all routes, all stages, and stages-on-route. In this system, stubs will simulate RouteMaster (for all routes) and RouteStage (for stages on each route). We will be filtering RouteStage depending on the route selected for each passenger.
The User Interface
The UI is designed using a new Windows Forms Application project in Visual Studio. The controls on the form are a small DataGridView called gridPassenger pulled onto the form from the Toolbox using the IDE, a pair of Labels, and the Exit button. The DataGridView has three columns, a text field, dgNumber, and the subsequent pair are both of type DataGridViewComboBoxColumn, dgRoute, and dgStage. The Labels are lblRoute and lblStage. I will be using these to show the impact of changing the combo fields. The final piece of the puzzle is the addition of an EditingControlShowing event on the DataGridView.
Required declarations
This is the list of specific declarations that need to be added to form1.h to carry out the task.
DataTable ^dtRoute;
DataTable ^dtStages;
DataSet ^dsRouteStages;
BindingSource ^bsRoute;
BindingSource ^bsRouteStages;
BindingSource ^filteredStages;
DataTable ^dtViewPassengerData;
^dtRoute is an internal table of route IDs and their descriptions. In this example, the fetch function in the I/O module DB_RouteManager will hard code in a sample set of values.^dtStages is an internal table of stage IDs, their descriptions, and the route ID on which it is used. A Stage may have multiple entries on this table. The fetch function in the I/O module DB_RouteStageManager will hard code in a sample set of values.^dsRouteStages will have the Stages and Route datatables as its constituents.^bsRoute helps tie the dsRouteStages dataset back to the dtRoute datatable. It performs a root role in our filtering process.^bsRouteStages helps tie the dsRouteStages dataset back to the dtStages datatable. It should be thought of as a child of bsRoute.^filteredStages is also linked to dtStages. It will hold the filtered set of stages for each route choice.^dtViewPassengerData is bound to the DataGridView (gridPassenger) itself. In your working example, this will be the interface to your primary datasource. In my case, it is my PassengerMaster table.
Putting the functionality in place
This section will take you through how the declarations above are applied to the UI as I have outlined it to deliver a filtered combo column on the DataGridView.
Initialization
The LaunchForm function carries out the critical initalization after it has built the form but before it loads any data.
First up, we have the code to allocate the memory for each of our data structures above:
dtRoute = gcnew DataTable("dtRoute");
dtStages= gcnew DataTable("dtStages");
dsRouteStages = gcnew DataSet();
bsRoute = gcnew BindingSource();
bsRouteStages = gcnew BindingSource();
filteredStages = gcnew BindingSource();
Next, we add the columns to each of our two datatables. These should reflect the columns you are reading in from your datasource. The names don't have to match up, but it leaves maintenance much easier when they do.
DataColumn ^col = gcnew DataColumn("RouteID", int::typeid);
dtRoute->Columns->Add(col);
col = gcnew DataColumn("RouteDescription");
dtRoute->Columns->Add(col);
col = gcnew DataColumn("StageID", int::typeid);
dtStages->Columns->Add(col);
col = gcnew DataColumn("StageDescription");
dtStages->Columns->Add(col);
col = gcnew DataColumn("RouteID", int::typeid);
dtStages->Columns->Add(col);
Now that our tables are fully created, we add them to the RouteStages dataset.
dsRouteStages->Tables->Add(dtRoute);
dsRouteStages->Tables->Add(dtStages);
The last piece of set up is to define and add the columns to the DataTable that is bound to the DataGridView. I have kept it separate, because it is not part of the filtering process, but I was unable to implement filtering without binding the grid to a DataTable, so here it is:
dtViewPassengerData = gcnew DataTable("ViewPassengerData");
dtViewPassengerData->Columns->Add("Number", Int32::typeid);
dtViewPassengerData->Columns->Add("RouteID", Int32::typeid);
dtViewPassengerData->Columns->Add("StageID", Int32::typeid);
Now that we are ready for our data, we call some functions to load routes, stages, and passengers, then complete the list setup. But all this gets you is a representation of what is on your primary data table - in my case, PassengerMaster. The real magic occurs in the Process_EditingControlShowing function which holds the code for the EditingControlShowing event.
Load_Routes
This is the primary table in my dual combo configuration. The stored data is read into a list which is used to populate the dtRoute datatable as follows:
DataRow ^row;
for each(CRouteMaster^ candidate in RouteList)
{
row = dsRouteStages->Tables["dtRoute"]->NewRow();
row[0] = candidate->p_Route_ID;
row[1] = candidate->p_Route_Name;
dsRouteStages->Tables["dtRoute"]->Rows->Add(row);
}
Take note that we are using the dtRoute DataTable within the dsRouteStages DataSet.
Then we tie it to the bsRoute bindingsource:
bsRoute->DataSource = dsRouteStages;
bsRoute->DataMember = "dtRoute";
dgRoute->DataSource = bsRoute;
dgRoute->DisplayMember = "RouteDescription";
dgRoute->ValueMember = "RouteID";
Pay particular attention to the last three lines above. Get them wrong and you will not see anything in your combo box, filtered or otherwise. DataSource is the link between the DataTable and the column where we expect to see the value list by making use of the BindingSource variable bsRoute, DisplayMember is what you will see on the combo's dropdown list, while ValueMember is what indexes back to the DataTable that supplies the combo box.
Load_All_RouteStages
Now it is the turn of the route stages to get loaded up.
DataRow ^row;
for each(CRouteStage^ candidate in RouteStageList)
{
row = dsRouteStages->Tables["dtStages"]->NewRow();
row[2] = candidate->p_Route_ID;
row[0] = candidate->p_Stage_ID;
row[1] = candidate->p_StageName;
dsRouteStages->Tables["dtStages"]->Rows->Add(row);
}
bsRouteStages->DataSource = dsRouteStages;
bsRouteStages->DataMember = "dtStages";
filteredStages->DataSource = dsRouteStages;
filteredStages->DataMember = "dtStages";
this->dgStage->DataSource = bsRouteStages;
this->dgStage->DisplayMember = "StageDescription";
this->dgStage->ValueMember = "StageID";
All very similar to what we did with the routes, except that we have a third column, the Route ID, which is what we will filter on, and the second BindingSource, filteredStages, that will handle our filtered results.
Load_Passengers
This function loads the DataTable dtViewPassengerData that is bound to the DataGridView gridPassenger. Consider it a catalyst for the filtered combo, in that while it has no role in the filtering process, reading my data directly onto the DataGridView was unsuccessful, so this construct had to be brought in as an intermediary. This is the key code:
DataRow ^dr;
for each(CPassengerMaster^ candidate in PassengerList)
{
dr = dtViewPassengerData->NewRow();
dr["Number"] = candidate->p_Person_ID;
if (candidate->p_Route_ID.HasValue)
dr["RouteID"] = candidate->p_Route_ID;
else
dr["RouteID"] = DBNull::Value;
if (candidate->p_Stage_ID.HasValue)
dr["StageID"] = candidate->p_Stage_ID;
else
dr["StageID"] = DBNull::Value;
dtViewPassengerData->Rows->Add(dr);
List_SetUp
List_SetUp completes the population of the DataGridView with:
gridPassenger->AutoGenerateColumns = false;
gridPassenger->DataSource = dtViewPassengerData;
gridPassenger->Columns[dgNumber->Index]->DataPropertyName = "Number";
gridPassenger->Columns[dgRoute->Index]->DataPropertyName = "RouteID";
gridPassenger->Columns[dgStage->Index]->DataPropertyName = "StageID";
AutoGenerateColumns is only there at false because I created the columns using the IDE. DataSource is a key attribute because it is what binds the DataGridView to the DataTable that holds the information to be shown on the grid. Finally, the three instances of DataPropertyName map the grid columns to the DataTable columns.
Process_EditingControlShowing
Process_EditingControlShowing operates on the dgStage column only. First up, a new variable ^control of type DataGridViewComboBoxEditingControl is defined and cast to e->Control, or in real terms, the dgStage column from the DataGridView.
DataGridViewComboBoxEditingControl ^control =
dynamic_cast<DataGridViewComboBoxEditingControl^>(e->Control);
Then we create a new BindingSource variable ^bs sourcing its data from control.
BindingSource ^bs = dynamic_cast<BindingSource^>(control->DataSource);
The final block of code is only performed when bs is not null, and after all the work we have done loading routes and stages, it will not be - however, the function does do a test, just in case. So carrying on, we set the filteredStages as the DataSource of the editing control.
(dynamic_cast<ComboBox^>(e->Control))->DataSource = filteredStages;
Next we declare an Object called ^Routevalue and use this to harvest the Route ID set on the current row of the DataGridView:
Object ^Routevalue = this->gridPassenger->Rows[
this->gridPassenger->CurrentCell->RowIndex]->Cells[dgRoute->Index]->Value;
If this value is null, we set the Filter on filteredStages a notional Route ID of -1 which will result in a null dropdown in the Stage combo, but if it has a value, this is used to set up a filtering string that will function in the same manner as a Where clause on a piece of SQL.
This is the statement that results in your second, in this case dgStage, combo column having a filtered list.
if (Routevalue == DBNull::Value || Routevalue == nullptr)
{
filteredStages->Filter = "RouteID=-1";
}
else
{
filteredStages->Filter = "RouteID=" + Routevalue->ToString();
}
After changing the contents of the combo box, I reset its SelectedValue property as seen here:
if (this->gridPassenger->CurrentCell->Value != DBNull::Value
&& this->gridPassenger->CurrentCell->Value != nullptr)
{
control->SelectedValue = this->gridPassenger->CurrentCell->Value;
}
}
Using the Filtered Combobox Column
So now the second combo will filter depending on the value of the first. This section will take you through the functions that interrogate the columns.
Process_RowEntered
Process_RowEntered is the function to handle the RowEnter event on the DataGridView. Its purpose is to perform specific tasks - in this case, read the current row, as soon as a user enters it. I mostly use it to record the current row contents for comparison against their final state when I am done with the row so that I can keep the number of fields to be written to the database as low as possible. Essentially, I want to avoid "Update TableA set X=1 where X=1". This is the code to extract the currently selected route ID from the dgRoute column:
array<DataRow^>^ row;
try
{
if (gridPassenger->Rows[e->RowIndex]->Cells[dgRoute->Name]->Value != nullptr
&& gridPassenger->Rows[e->RowIndex]->Cells[dgRoute->Name]->Value->ToString() != "")
{
String^ IndRoute =
gridPassenger->Rows[e->RowIndex]->Cells[dgRoute->Name]->Value->ToString();
row =dtRoute->Select(String::Format("RouteID={0}", IndRoute));
if (row->Length > 0)
lblRoute->Text = row[0]->ItemArray[1]->ToString();
}
else
lblRoute->Text = "";
}
catch (Exception ^e)
{.<
String ^MessageString = " Error reading internal Route table: " + e->Message;
MessageBox::Show(MessageString);
}
Not much going on here, but the value of the current entry is read into a string and used to form part of a "Where clause" quite like in the editing control. Then it is used as a parameter to the Select event on the dtRoute DataTable. The resultant row is interrogated to extract the route name for display in a label box so you can see the current state of the combo outside the DataGridView. Identical code is applied to the dgStage column to produce the same effect.
Process_CellValueChanged
Process_CellValueChanged is the function to handle the CellValueChanged event on the DataGridView. When the value of one or other of the combo cells is changed, it uses code identical to that shown for the RowEntered event to capture the new value.
Other interesing functions in this example
The only other activity other than the primary objective in this example are "one click" on the combo arrows, error icons, and improved error handling.
Process_CellClick
Process_CellClick holds the code to handle a CellClick event from the DataGridView. It is not essential, but without it, the combo cells will have to be clicked twice to activate the dropdowns. This is the engine of the function:
gridPassenger->BeginEdit(true);
(safe_cast<ComboBox^>(gridPassenger->EditingControl))->DroppedDown = true;
Process_CellEndEdit
Process_CellEndEdit is the function to handle the CellEndEdit event on the DataGridView. It will put a little red icon in any cell that needs one if you leave the row without filling in the required cells. It provides this functionality in coordination with the RowValidating event.
Process_RowValidating
Process_RowValidating handles the RowValidating event on the DataGridView. It uses an if statement to decide which columns are to be checked for errors. In this case, I am choosing to ban null values from the Number column. It sets up padding for the icon in the cell.
Process_DataError
I introduced the DataError event to handle unforeseen display failures on the DataGridView when I was putting together the enum example. You could live without it, but if you accidentally leave something out, then it may get called into action, and it provides useful information about what went wrong any time it does get invoked because it nicely catches unexpected display issues with the DataGridView.
Walkthrough the code
This time I gave strong consideration to leaving out this section, because this example is more focused on its core objective and as a result, you have seen all the significant code already. However, I have decided to stick with it, but will keep it brief just to give the different components some additional context. The solution was created around DataGridViewComb5.cpp and its associated Form1.h. These are compiled to form the EXE. The other modules are used to form the DB_PassengerManager, DB_RouteManager, and DB_RouteStageManager assemblies respectively. All are included in the same solution to simplify matters.
Form1.h
Form1.h begins with namespaces. These are added to the standard set:
using namespace System::Collections::Generic;using namespace DB_RouteManager;
using namespace DB_RouteStageManager;
using namespace DB_PassengerManager;
This is followed by the constructor which has a couple of set up lines for the error icon and a call to the LaunchForm function that gets everything up and going. This is succeeded by the code added by the IDE to build the form. Then there are the user defined functions and variables, most of which you have seen above, and finally by the event handler functions, all already visited earlier in the text.
DataGridViewComb5.cpp
Contains all the function code, including those of the event handlers who default to the .h but are redirected here via user defined functions. E.g., Process_RowEntered is the workhorse for gridPassenger_RowEnter. The only aspects we have not looked at are the additional grid aesthetics done in LaunchForm and the fetch calls in the Load... functions, but these are standard code.
DB_PassengerManager.h
DB_PassengerManager.h defines the Passenger class, the Get properties for it, and a communications class to bring back the values. DB_RouteManager.h and DB_RouteStageManager.h perform similar tasks for their tables.
DB_PassengerManager.cpp
DB_PassengerManager.cpp is stripped down to just a fetch function, with some values hard coded onto a list which is returned to the calling module. DB_RouteManager.cpp and DB_RouteStageManager.cpp perform similar tasks for their tables.
History
- 2011-08-24 - V1.0 - Initial submission.
