DataGridView filter, number filter

Question

Good afternoon, I made a telephone directory that takes data from an excel table (according to the standard), you need to make a filter

1) My filter is only looking for a COMPLETE input match in a TextBox. How to make the Filtering start from the first letter?

I begin to write P (everything that does not start from p) disappears.

2) And the second question is how to make it so that it also filters by numbers? Thank you in advance

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.OleDb; using System.IO; using System.Reflection; namespace WindowsFormsApplication3 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } OleDbConnection conn = new OleDbConnection(Excel_Load()); DataSet da = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(); private static string Excel_Load() //Берем файл excel из папки сборки { string path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); path = Path.Combine(path, "textfortest.xlsx"); string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + @"; Extended Properties=""Excel 12.0 Macro;HDR=Yes;ImportMixedTypes=Text;TypeGuessRowsIMEX=1;TypeGuessRows=0"""; return ConnectionString; } private void DataView(DataSet da,string filtre ="") { dataGridView1.DataSource = da.Tables[0]; DataTableCollection tables = da.Tables; DataView view1 = new DataView(tables[0]); BindingSource source1 = new BindingSource(); source1.DataSource = view1; dataGridView1.DataSource = source1; if(String.IsNullOrWhiteSpace(filtre)==false)//Фильтр source1.Filter = "Подразделение = '" + filtre + "' OR ФИО = '" + filtre +"' OR Должность = '" + filtre+"'"; dataGridView1.Columns[0].Width = 55; dataGridView1.Columns[1].Width = 200; dataGridView1.Columns[2].Width = 55; dataGridView1.Columns[3].Width = 70; dataGridView1.Columns[5].Width = 200; dataGridView1.Dock = DockStyle.Fill; } private void DateBase(OleDbConnection conn, string s ="") { conn = new OleDbConnection(Excel_Load()); string Strcmd = "select * from [List$A1:G350]"; OleDbCommand cmd = new OleDbCommand(Strcmd, conn); try { conn.Open(); da.Clear(); adapter.SelectCommand = cmd; adapter.Fill(da); DataView(da,s); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } } private void Form1_Load(object sender, EventArgs e) { pictureBox2_MouseClick(new object { }, new EventArgs { }); } private void pictureBox2_MouseClick(object sender, EventArgs e) { DateBase(conn); } private void textBox1_TextChanged(object sender, EventArgs e) { DateBase(conn,textBox1.Text); } } }

Ella Svetlaya Ella Svetlaya 1.810 one ten 24 · Accepted Answer · 2016-07-26T05:27:17

To filter numeric fields, you must first convert them to text either via CAST or CONVERT .

Example:

 "CAST(MyIntField AS NVARCHAR(MAX)) LIKE '" + filter + "%'"

Another example:

 private void textBox1_TextChanged(object sender, EventArgs e) { try { DataView DV = new DataView(dbdataset); DV.RowFilter = string.Format("Convert([MyIntField], System.String) LIKE '%{0}%'", textBox1.Text); dataGridView1.DataSource = DV; } catch (Exception ex) { ErrorLabel.Text = ex.Message; } }

Answer 2 · 2016-07-25T10:04:45

The filter should look like this:

 "Подразделение LIKE '" + filter + "%' OR ФИО LIKE '" + filter + "%' OR Должность LIKE '" + filter + "%'"

Instead of the = sign, you must use the word LIKE and the symbol % or * as a wildcard, meaning any number of any characters.

Help on filtering expressions.

I would also like to note that in the TextChanged event TextChanged you should not query the database every time. The data is already loaded into the DataSet . In this event, just call your DataView method.

And generally it is not necessary to re-create the DataView , BindingSource every time, set the data binding. All this should be done once when loading data from the database. And in the TextChanged event you only need to set a new filter.

Now I will try to "Optimize" Maybe you can tell me what can be done so that the filter works with numbers?
It does not matter at all what to enter into TextBox: letters, numbers, any other Unicode characters.

DataGridView filter, number filter

2 answers 2

More articles: