WORKING WITH DATAGRIDVIEWCOMBOBOXCELLS/COLUMNS:
Here am going to discuss how to work with DataGridviewcomboboxColumns/Cells in Windows based Appliation (c#.net 3.5).
I mainly concentrated on filtering means based on the selection of one comboboxcell filling the other comboboxcell, which will restrict user from wrong entries and data displaying when there is large amount of data.
Here I took three tables department, employee, transaction
Structures:
DEPT:
id varchar(5)
name varchar(30)
employee:
id varchar 5 0
name varchar 50 0
dept varchar 3 1
address varchar 50 1
active bit 1 0
transactions:
id int 4 0
empid varchar 5 1
dept varchar 5 1
remarks varchar 50 1
initially am taking data which in transactions table in into a datatable ‘empTrans’
that coding is in page_load event
DataTable empTrans;
//Page_load
da = new SqlDataAdapter("SELECT * FROM transactions", con);
ds = new DataSet();
da.Fill(ds, "Emptran");
empTrans = ds.Tables[0];
GridColumns();
fillGridView();
Declare enumerate
enum ColumntType
{
Desc,
Value
};
The datasource of dept combobox cell is
private DataTable Populatedept()
{
DataTable deptTbl = new DataTable("dept");
deptTbl.Columns.Add("Value");
deptTbl.Columns.Add("Desc");
if (con.State == ConnectionState.Closed)
con.Open();
cmd = new SqlCommand("select id, name from dept", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
string id = dr["id"].ToString();
string name = dr["name"].ToString();
deptTbl.Rows.Add(id, name);
}
dr.Close();
con.Close();
return deptTbl; ;
}
For employee combo we have to display based on the selected department so pass dept as parameter and retrieve that department employee and fill that combo
private DataTable PopulateEmp(string dept)
{
DataTable empnameTbl = new DataTable("empnames");
empnameTbl.Columns.Add("Value");
empnameTbl.Columns.Add("Desc");
if (con.State == ConnectionState.Closed)
con.Open();
cmd = new SqlCommand("select id, name from employee where dept='" + dept + "'", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
string id = dr["id"].ToString();
string name = dr["name"].ToString();
empnameTbl.Rows.Add(id, name);
}
dr.Close();
con.Close();
return empnameTbl;
}
Initially the column am declaring to datagridview as
private void GridColumns()
{
dataGridView2.Columns.Add("dept", "DEPT");
dataGridView2.Columns.Add("emp", "EMP");
dataGridView2.Columns.Add("remarks", "REMARKS");
}
And fill the datagridview like this
private void fillGridView()
{
foreach (DataRow row in empTrans.Rows)
{
DataGridViewRow dgRow = new DataGridViewRow();
DataGridViewComboBoxCell combodept = new DataGridViewComboBoxCell();
dgRow.Cells.Add(combodept);
combodept.DataSource = Populatedept();
combodept.ValueMember = ColumntType.Value.ToString();
combodept.DisplayMember = combodept.ValueMember;
dgRow.Cells[0].Value = row["dept"].ToString();
DataGridViewComboBoxCell comboEmp = new DataGridViewComboBoxCell();
comboEmp.ValueMember = ColumntType.Value.ToString();
comboEmp.DisplayMember = comboEmp.ValueMember;
comboEmp.DataSource = PopulateEmp(row["dept"].ToString());
dgRow.Cells.Add(comboEmp);
dgRow.Cells[1].Value = row["empid"].ToString();
DataGridViewTextBoxCell TxtRem = new DataGridViewTextBoxCell();
dgRow.Cells.Add(TxtRem);
dgRow.Cells[2].Value = row["remarks"].ToString();
dataGridView2.Rows.Add(dgRow);
}
}
For new row call below method
private void NewRow()
{
DataGridViewRow dgRow = new DataGridViewRow();
DataGridViewComboBoxCell combodept = new DataGridViewComboBoxCell();
dgRow.Cells.Add(combodept);
combodept.DataSource = Populatedept();
combodept.ValueMember = ColumntType.Value.ToString();
combodept.DisplayMember = combodept.ValueMember;
DataGridViewComboBoxCell comboEmp = new DataGridViewComboBoxCell();
comboEmp.ValueMember = ColumntType.Value.ToString();
comboEmp.DisplayMember = comboEmp.ValueMember;
dgRow.Cells.Add(comboEmp);
DataGridViewTextBoxCell TxtRem = new DataGridViewTextBoxCell();
dgRow.Cells.Add(TxtRem);
int rowIndex = dataGridView2.Rows.Count;
dataGridView2.Rows.Insert(rowIndex, dgRow);
}
Now the actual play begins
Raise a ‘EditingControlShowing’ event to that DataGRidview
And for row index declara a variable
int selrowIndex;
Assign the selected rowindex to that variable and we want to filter according to firstcell only. And raise select index changed event to that combocell
private void dataGridView2_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
selrowIndex = dataGridView2.CurrentRow.Index;
if (dataGridView2.CurrentCell.ColumnIndex == 0)
{
ComboBox comboCell = e.Control as ComboBox;
if (comboCell != null)
{
comboCell.SelectedIndexChanged += new EventHandler(comboCell_SelectedIndexChanged);
}
}
}
So in that combobox selectindexchanged event create a combocell and place that in that row at respected position
void comboCell_SelectedIndexChanged(object sender, EventArgs e)
{
if (dataGridView2.CurrentCell.ColumnIndex == 0)
{
ComboBox combo = sender as ComboBox;
if (combo.SelectedIndex > -1)
{
DataGridViewComboBoxCell comboEmp = new DataGridViewComboBoxCell();
string dept = combo.SelectedValue.ToString();
comboEmp.ValueMember = ColumntType.Value.ToString();
comboEmp.DisplayMember = comboEmp.ValueMember;
comboEmp.DataSource = PopulateEmp(dept);
dataGridView2.Rows[selrowIndex].Cells[1] = comboEmp;
}
}
}
That’s it……….
you can follow this for more cells also based on your requirement
happy coding
--N@ren
vnreddy@vercom.com