Cascading ComboBoxes within a Telerik GridView based on a SQL Server table

1 Answer 28 Views
ComboBox GridView
Daniel
Top achievements
Rank 1
Daniel asked on 09 Dec 2023, 07:32 AM

Hi,

I have a problem, that I cannot solve. I have a Grid View (see attachment) based on a SQL Table. Now have replaced some colums with a comboBox.

The comboBoxes receive the data from the following table.

| ID  |  Country |  Plant  |  Area  |  Machine |
------------------------------------------------
|  1  |   DE     |   MUC   |  BA    |   A      |
|  2  |   DE     |   MUC   |  BA    |   A      |
|  3  |   AT     |   VIE     |  BE    |    1      |
|  4  |   AT     |   VIE     |  BE    |    2      |

F.e. The column "Country" in my GridView has as ItemSource a grouping of the column Country. Now I want to update the comboBox "Plant" depending on the value of the ComboBox "Country" meaning that when "DE" is chosen, it should only show "MUC" as a possible entry. How can I do that? Here is my code.

Thanks.


 public partial class MainWindow : Window
 {
     private SqlConnection connection;
     private string dbName = string.Empty;
     
     public MainWindow()
     {
         InitializeComponent();
         LoadData();
     }

     private void LoadData()
     {

         try
         {
             string connectionString = "Data Source=localhost;Initial Catalog=Test_DB;Integrated Security=True";
             using (SqlConnection connection = new SqlConnection(connectionString))
             {
                 connection.Open();
                 string query = "SELECT * FROM dbo.MachineAreas";
                 SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                 DataTable dataTable = new DataTable();
                 adapter.Fill(dataTable);

                 gridMachineAreas.ItemsSource = dataTable.DefaultView;
             }
         }
         catch (Exception ex)
         {
             MessageBox.Show("Error: " + ex.Message);
         }
     }

     private void GridMachineAreas_AutoGeneratingColumn(object sender, GridViewAutoGeneratingColumnEventArgs e)
     {            

         switch ((e.Column as GridViewDataColumn).DataMemberBinding.Path.Path)
         {
             case "ID":
                 var newColumn1 = new GridViewDataColumn();
                 newColumn1.CopyPropertiesFrom(e.Column);
                 newColumn1.Header = "ID";
                 newColumn1.Width = 60;
                 e.Column = newColumn1;
                 break;

             case "Country":
                 var newColumn2 = new GridViewComboBoxColumn();
                 newColumn2.CopyPropertiesFrom(e.Column);
                 newColumn2.Header = "Country";
                 newColumn2.Width = 60;
                 newColumn2.UniqueName = "Country";
                 newColumn2.IsComboBoxEditable = false;
                 newColumn2.ItemsSource = MakeCountryCollection();                    
                 
                 e.Column = newColumn2;
                 break;
             case "Plant":
                 var newColumn3 = new GridViewComboBoxColumn();
                 newColumn3.CopyPropertiesFrom(e.Column);
                 newColumn3.Header = "Plant";
                 newColumn3.Width = 60;                    
                 e.Column = newColumn3;
                 break;
             case "Area":
                 var newColumn4 = new GridViewComboBoxColumn();
                 newColumn4.CopyPropertiesFrom(e.Column);
                 newColumn4.Header = "Area";
                 newColumn4.Width = 60;
                 e.Column = newColumn4;
                 break;
             case "Machine":
                 var newColumn5 = new GridViewComboBoxColumn();
                 newColumn5.CopyPropertiesFrom(e.Column);
                 newColumn5.Header = "Machine";
                 newColumn5.Width = 60;
                 e.Column = newColumn5;
                 break;
         }

     }

     private List<string> MakeCountryCollection()
     {
         List<string> countryCollection = new List<string>();
         string query = "SELECT Country FROM dbo.MachineAreas Group By Country";
         
         try
         {
             string connectionString = "Data Source=localhost;Initial Catalog=Test_DB;Integrated Security=True";
             using (SqlConnection connection = new SqlConnection(connectionString))
             {
                 connection.Open();


                 using (SqlCommand command = new SqlCommand(query, connection))
                 {
                     using (SqlDataReader reader = command.ExecuteReader())
                     {


                         while (reader.Read())
                         {
                             string value = reader.GetString(0);
                             countryCollection.Add(value);
                         }
                         
                     }
                 }
             }
         }
         catch (Exception ex)
         {
             MessageBox.Show("Error: " + ex.Message);
         }
         return countryCollection;
     }
 }


1 Answer, 1 is accepted

Sort by
0
Accepted
Dimitar
Telerik team
answered on 12 Dec 2023, 11:01 AM

Hi Daniel,

You can use the PreparedCellForEdit event and set the data source of the editor: 

private void RadGridView1_PreparedCellForEdit(object sender, GridViewPreparingCellForEditEventArgs e)
{
    var col = (e.Column as GridViewComboBoxColumn);
    if (col != null && col.DataMemberBinding.Path.Path == "Plant")
    {
        var value = (e.Row.DataContext as DataRow)["Country"].ToString();  

        var editor = e.EditingElement as RadComboBox;

        if (value == "DE")
        {
            editor.ItemsSource = new List<string> { "Item 1" };
        }

        if (value == "AT")
        {
            editor.ItemsSource = new List<string> { "Item 2" };
        }
    }
}

We have another example of this in our SDK repo: xaml-sdk/GridView/CascadingComboboxColumns at master.

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,
Dimitar
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Tags
ComboBox GridView
Asked by
Daniel
Top achievements
Rank 1
Answers by
Dimitar
Telerik team
Share this question
or