How to DataGridViewComboBoxColumn set value from dataset

Question

Let's say i have two tables:

 work_hours
 work_hours_id | date  | _project_id
     1            1.2.      10
     2            1.2.      11
     3            1.2.      10 

 project
 project_id | project_name
    10           pr1
    11           pr2
    12           pr3

In DataGridView i want to see this:

 work_hours_id | date | _person_id | project_name(DataGridViewComboBoxColumn)
     1            1.2.     10            pr1
     2            1.2.     11            pr2
     3            1.2.     10            pr1

1. How can i do that? 2. Is possible to save changes in table work_hours, if i change pr1 (work_hours_id = 3) to pr3 (DataGridViewComboBoxColumn) with SqlCommandBuilder?

 string query = "SELECT work_hours.work_hours_id, work_hours.date FROM work_hours
           LEFT OUTER JOIN project ON work_hours._project_id = project.project_id ORDER BY work_hours.date;
         SELECT * FROM project ORDER BY project_name";

            SqlCommand sqlcmd = new SqlCommand(query, conn);
            da = new SqlDataAdapter(query, connectionString);
            cBuilder = new SqlCommandBuilder(da);
            dt = new DataTable();
            ds = new DataSet();
            da.Fill(dt);
            da.Fill(ds);

            DataGridViewComboBoxColumn columnCb = new DataGridViewComboBoxColumn();
            columnCb.DataPropertyName = "_project_id";

            columnCb.DataSource = ds.Tables[1];
            columnCb.ValueMember = "project_id";
            columnCb.DisplayMember = "project_name";

            bSource = new BindingSource();
            bSource.DataSource = dt;
            dataGridView1.DataSource = bSource;
            dataGridView1.Columns.Add(columnCb);

Here is example:

score 2 · Accepted Answer · answered Jun 06 '12 at 21:24

if i don't misunderstand you want to set the datagridview's combobox column's datasource..You can set by this :

/// if you use OfType Method then you will get advantage of to use as normal control
/// means this method will give you advantage of can use all method and properties
/// of the control which you want to implement in DataGridView

/// Controls.OfType method will check all the columns and get an array with 
/// what you give as search criteria..here, the criteria is ComboboxColumn..
/// depends of your need you can give comboboxcell also..

/// Element at method selects the zero based index in array which filtered by criteria
/// if you use only one of the given type then you can use .First() instead of .ElementAt()


yourDataGridViewName.Controls.OfType<DataGridViewComboBoxColumn>()
.ElementAt(indexNoOfTheDGVComboBox).DataSource = YourDataRetrievingMethod; 
//i.e. ds.Tables[indexNoOfTheTable].Columns[IndexOfTheColumn]

/// and you can set DisplayMember and ValueMember as the same way.. 
/// i give combocell and combocolumn together to show the syntax.

yourDataGridViewName.Controls.OfType<DataGridViewComboBoxCell>()
.ElementAt(0).DisplayMember = "YourDisplay";

yourDataGridViewName.Controls.OfType<DataGridViewComboBoxCell>()
.ElementAt(0).ValueMember = "YourValue";

About the second Question.. if you ask me then i prefer : To control "accidentally savings" put a buttonCell at the end of the row..when user change something on row should click the save button..And then in your button click method you can save changes as normal insert or update method..

just 2 differences there..

1-) you need to use Cell_Click Event and to be sure that is button check with OfType() method instead of normal Button _Click event

2-) Depends to project needs, you will need to get DataGridView's RowIndex and / or Cell Address() before to get and pass values to the SQLQuery

thanks, did it in similar way as you mentioned. I thought if is some 'easyer' way with `SqlCommandBuilder` but i didn't found it. — JanOlMajti, Jun 15 '12 at 10:01
@JanOlMajti you can't do everything with commbuilder and you don't need to ;) if i needed to create a control as yours then i preferred this way : i create a table which has integers (i.e. your combobox column's selectedindex no and others' date and id etc.) and crud (ins-upd-del) procedures for this table..if project_name must have some attributes then another table for these and bring id/name to the combobox from there with datareader(dreader is faster)..if project_name has only needs name and id then i prefer to create a dictionary and set the dgv combocell's datasource with it — sihirbazzz, Jun 16 '12 at 00:49
(as a securer and faster option always prefer use datareader and stored procedures for your Sql actions..) and after these steps i can pass row values to the stored procedure as much as easier and securer than sqlcommandbuilder and query — sihirbazzz, Jun 16 '12 at 00:56

How to DataGridViewComboBoxColumn set value from dataset

1 Answers1