Diegoctn
Reputation: 55
C# Combobox and DataGridView
I've got a combobox which retrieve data from a select and a datagridview which retrieve data from another query. I would like to filter the datagridview using the comboboxvalue. I am trying everything but nothing work. Could you please help? Moreover, why when I declare dataview=((DataTable)datagridview.datasource.defaultview (first row in combobox_SelectedIndexChanged ) I can't see any values in the combobox anymore, instead I see System Data DataRowView but the first question is more important for me
private void Form5_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=xxxxx;Initial Catalog=xxxxx;Integrated Security=True;");
conn.Open();
SqlCommand sc = new SqlCommand(" SELECT id, customername+' - '+cast(inserted as varchar(19)) as targ FROM bf where customername>'a' order by customername asc, inserted desc ", conn);
SqlDataReader reader;
reader = sc.ExecuteReader();
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("targ", typeof(string));
dt.Load(reader);
comboBox1.ValueMember = "id";
comboBox1.DisplayMember = "targ";
comboBox1.DataSource = dt;
conn.Close();
var select = "SELECT [id],[CustomerName[email],[Capital] FROM baf order by id desc";
var c = new SqlConnection("Data Source=xxxxxx;Initial Catalog=xxxxx;Integrated Security=True;"); // Your Connection String here
var dataAdapter = new SqlDataAdapter(select, c);
var commandBuilder = new SqlCommandBuilder(dataAdapter);
var ds = new DataSet();
dataAdapter.Fill(ds);
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = ds.Tables[0];
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//var dataView = ((DataTable)dataGridView1.DataSource).DefaultView;
// if (comboBox1.Text == "Remove filter")
// {
// dataView.RowFilter = string.Empty;
// }
// else
// {
// //dataView.RowFilter = "id = {comboBox1.Text}";
// }
//}
}
Upvotes: 1
Views: 760
Answers (3)
Rupesh
Reputation: 342
For the viewers who are interested in finding how to make the code in question work successfully, I present the solution below. To achieve readiness to experiment, the data is hard coded which need be modified as per actual situation.
DataView dataView = null; // <<< Difference #1
private void Form1_Load(object sender, EventArgs e)
{
var dt = new DataTable();
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("targ", typeof(string));
dt.Rows.Add("1", "Targ-1"); // example code - remove
dt.Rows.Add("2", "Targ-2"); // example code - remove
dt.Rows.Add("3", "Targ-3"); // example code - remove
dt.Rows.Add("4", "Targ-4"); // example code - remove
comboBox1.ValueMember = "id";
comboBox1.DisplayMember = "targ";
comboBox1.DataSource = dt;
var bf = new DataTable("BF");
bf.Columns.Add("id", typeof(string)); // example code - remove
bf.Columns.Add("CustomerName", typeof(string)); // example code - remove
bf.Columns.Add("Email", typeof(string)); // example code - remove
bf.Columns.Add("Capital", typeof(string)); // example code - remove
bf.Rows.Add("1", "Customer-1", "Email-1", "Capital-1"); // example code - remove
bf.Rows.Add("1", "Customer-2", "Email-2", "Capital-1"); // example code - remove
bf.Rows.Add("2", "Customer-3", "Email-3", "Capital-2"); // example code - remove
bf.Rows.Add("3", "Customer-4", "Email-4", "Capital-3"); // example code - remove
bf.Rows.Add("3", "Customer-5", "Email-5", "Capital-3"); // example code - remove
bf.Rows.Add("3", "Customer-6", "Email-6", "Capital-3"); // example code - remove
bf.Rows.Add("4", "Customer-7", "Email-7", "Capital-4"); // example code - remove
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = bf;
dataView = bf.DefaultView; // <<< Difference #2
this.comboBox1.SelectedIndexChanged += new EventHandler(comboBox1_SelectedIndexChanged); // <<< Difference #3
}
void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.Text == "Remove filter")
{
dataView.RowFilter = string.Empty;
}
else
{
dataView.RowFilter = string.Format("id = '{0}'", comboBox1.SelectedValue); // <<< Difference #4
}
}
Upvotes: 0
Rupesh
Reputation: 342
You need to properly use BindingSource for the purpose. Below is the complete working example. To experiment with it what you need is as under:
- Create a blank form Q1
- Add one DataGridView control and one ComboBox control to the form
- Copy paste below code into Q1.cs file
- Run and experiment
I hope this will get you going nicely.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace WinFormQ
{
public partial class Q1 : Form
{
public Q1()
{
InitializeComponent();
}
BindingSource bs;
private void Q1_Load(object sender, EventArgs e)
{
// SqlConnection conn = new SqlConnection(@"Data Source=xxxxx;Initial Catalog=xxxxx;Integrated Security=True;");
// conn.Open();
// SqlCommand sc = new SqlCommand(" SELECT id, customername+' - '+cast(inserted as varchar(19)) as targ FROM bf where customername>'a' order by customername asc, inserted desc ", conn);
// SqlDataReader reader;
//
// reader = sc.ExecuteReader();
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("targ", typeof(string));
// dt.Load(reader);
dt.Rows.Add("1", "Targ-1"); // example code - remove
dt.Rows.Add("2", "Targ-2"); // example code - remove
dt.Rows.Add("3", "Targ-3"); // example code - remove
dt.Rows.Add("4", "Targ-4"); // example code - remove
comboBox1.ValueMember = "id";
comboBox1.DisplayMember = "targ";
comboBox1.DataSource = dt;
// var select = "SELECT [id],[CustomerName[email],[Capital] FROM baf order by id desc";
// var c = new SqlConnection("Data Source=xxxxxx;Initial Catalog=xxxxx;Integrated Security=True;"); Your Connection String here
// var dataAdapter = new SqlDataAdapter(select, c);
// var commandBuilder = new SqlCommandBuilder(dataAdapter);
DataSet ds = new DataSet();
DataTable bf = new DataTable("BF");
bf.Columns.Add("id", typeof(string)); // example code - remove
bf.Columns.Add("CustomerName", typeof(string)); // example code - remove
bf.Columns.Add("Email", typeof(string)); // example code - remove
bf.Columns.Add("Capital", typeof(string)); // example code - remove
ds.Tables.Add(bf);
bs = new BindingSource(ds, "BF");
// dataAdapter.Fill(bf);
bf.Rows.Add("1", "Customer-1", "Email-1", "Capital-1"); // example code - remove
bf.Rows.Add("1", "Customer-2", "Email-2", "Capital-1"); // example code - remove
bf.Rows.Add("2", "Customer-3", "Email-3", "Capital-2"); // example code - remove
bf.Rows.Add("3", "Customer-4", "Email-4", "Capital-3"); // example code - remove
bf.Rows.Add("3", "Customer-5", "Email-5", "Capital-3"); // example code - remove
bf.Rows.Add("3", "Customer-6", "Email-6", "Capital-3"); // example code - remove
bf.Rows.Add("4", "Customer-7", "Email-7", "Capital-4"); // example code - remove
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = bs;
this.comboBox1.SelectedIndexChanged += new EventHandler(comboBox1_SelectedIndexChanged);
}
void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.Text == "Remove filter")
{
bs.RemoveFilter();
}
else if (comboBox1.SelectedValue == null)
{
bs.RemoveFilter();
}
else
{
bs.Filter = "id = " + comboBox1.SelectedValue;
}
}
}
}
Upvotes: 1
Diegoctn
Reputation: 55
I resolved in this way (maybe not the most elegant but it does work):
private void Form5_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=xxxxx;Initial Catalog=xxxxx;Integrated Security=True;");
conn.Open();
SqlCommand sc = new SqlCommand(" SELECT id, customername+' - '+cast(inserted as varchar(19)) as targ FROM baf where customername>'a' order by customername asc, inserted desc ", conn);
SqlDataReader reader;
reader = sc.ExecuteReader();
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("targ", typeof(string));
dt.Load(reader);
comboBox1.ValueMember = "id";
comboBox1.DisplayMember = "targ";
comboBox1.DataSource = dt;
conn.Close();
var select = "SELECT [id],[CustomerName], [email],[CapActual] FROM baf order by id desc";
var c = new SqlConnection("Data Source=xxxx;Initial Catalog=xxxxx;Integrated Security=True;"); // Your Connection String here
var dataAdapter = new SqlDataAdapter(select, c);
var commandBuilder = new SqlCommandBuilder(dataAdapter);
var ds = new DataSet();
var bf = new DataTable("BF");
ds.Tables.Add(bf);
dataAdapter.Fill(bf);
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = bf;
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
var select = "SELECT [id],[CustomerName],[email],[CapActual] FROM baf where id="+comboBox1.SelectedValue+" order by id desc";
var c = new SqlConnection("Data Source=xxxxx;Initial Catalog=xxxxxx;Integrated Security=True;"); // Your Connection String here
var dataAdapter = new SqlDataAdapter(select,c);
var commandBuilder = new SqlCommandBuilder(dataAdapter);
var ds = new DataSet();
var bf = new DataTable("BF");
ds.Tables.Add(bf);
dataAdapter.Fill(bf);
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = bf;
}
}
}
Upvotes: 0