CODEWITHSUNDEEP
c#winformsdatagridview
JanOlMajti
JanOlMajti

Reputation: 1397

How to DataGridViewComboBoxColumn set value from dataset

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:

Upvotes: 4

Views: 3491

Answers (1)

sihirbazzz
sihirbazzz

Reputation: 718

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

Upvotes: 2

Related Questions