Filter DataGridView records using TextBox KeyUp event in VB.Net

kana2506...

SOLVED CLOSED ACTIVE ANSWERED
User: kana250688
Posted: on Aug 21, 2024 04:03 AM
Forum: Windows Basics
Answer: 1
Views: 1541
Sample Code: Download

Dear Sir,

I'm trying to use event filter in the textbox keyup not work but the mastextbox keyup works with the bindingsource in VB.Net

Is there anything wrong with my code please guide me?

What's the problem because I'm putting multiple controls together in one event?

Thanks

Video result code

Link database ms access

Imports System.Data.OleDb
Imports System.Globalization
Imports Dapper

Public Class Form1
    Private bindingSource As BindingSource = Nothing
    Private _criteriasBindingList As New SortableBindingList(Of People)()
    Dim Peopleservice As New PeopleService()

    Public Sub New()
        InitializeComponent()
        Dim CheckedBoxColumn As New DataGridViewCheckBoxColumn
        CheckedBoxColumn.Width = 40
        CheckedBoxColumn.Name = "checkboxcolumn"
        CheckedBoxColumn.HeaderText = "Check"
        CheckedBoxColumn.ReadOnly = False
        DataGridView1.Columns.Insert(0, CheckedBoxColumn)
    End Sub
    Private Sub Listpeople()
        _criteriasBindingList = New SortableBindingList(Of People)(CType(Peopleservice.Getpeople(), IList(Of People)))
        bindingSource = New BindingSource With {.DataSource = _criteriasBindingList}

        DataGridView1.DataSource = bindingSource 'Set the data source.
        DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
    End Sub
    Private Sub myFilter(str1 As String, str2 As String, str3 As String)
        Listpeople()
        If (String.IsNullOrEmpty(str1) AndAlso String.IsNullOrEmpty(str2)) Then
            bindingSource.DataSource = _criteriasBindingList
        ElseIf (String.IsNullOrEmpty(str1)) Then
            bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.DATETRANS.ToString("dd-MM-yyyy").Contains(str2) AndAlso c.DAYS.ToLower().Contains(str3)).ToList()
        ElseIf (String.IsNullOrEmpty(str2)) Then
            bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.IDPEOPLE.ToLower().Contains(str1) AndAlso c.DAYS.ToLower().Contains(str3)).ToList()
        ElseIf (String.IsNullOrEmpty(str3)) Then
            bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.IDPEOPLE.ToLower().Contains(str1) AndAlso c.DATETRANS.ToString("dd-MM-yyyy").Contains(str2)).ToList()
        Else
            bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.IDPEOPLE.ToLower().Contains(str1) AndAlso c.DATETRANS.ToString("dd-MM-yyyy").Contains(str2) AndAlso c.DAYS.ToLower().Contains(str3)).ToList()
        End If
    End Sub
    Private Sub TXT_FILTER(sender As Object, e As KeyEventArgs) Handles MaskedTextBoxDATETRANS.KeyUp, txtIDPEOPLE.KeyUp, txtDAYS.KeyUp
        Dim str1 = txtIDPEOPLE.Text.Trim().ToLower()
        Dim str2 = MaskedTextBoxDATETRANS.Text.Trim().ToLower()
        Dim str3 = txtDAYS.Text.Trim().ToLower()
        Dim dt As Date
        Dim isValid As Boolean = DateTime.TryParseExact(str2, "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, dt)
        If isValid Then
            myFilter(str1, str2, str3)
        Else
            myFilter(String.Empty, String.Empty, String.Empty)
        End If
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Listpeople()
    End Sub

 Private Sub Btncheckforcheckboxdgvuncheck_Click(sender As Object, e As EventArgs) Handles Btncheckforcheckboxdgvuncheck.Click

 End Sub
End Class
Public Class People
    Public Property [DATETRANS] As DateTime
    Public Property IDPEOPLE As String
    Public Property [DAYS] As String
    Public Property CRITERIA1 As String
    Public Property CRITERIA2 As String
    Public Property CRITERIA3 As String
End Class
Public Class PeopleService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\test21082024.accdb;Persist Security Info=False;"
    End Function
    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()
    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub
    Public Function Getpeople() As IEnumerable(Of People)
        Dim sql = "SELECT PEOPLE.DATETRANS AS [DATETRANS],format(PEOPLE.DATETRANS,'dddd') AS [DAYS],PEOPLE.IDPEOPLE AS [IDPEOPLE],PEOPLE.CRITERIA1 AS [CRITERIA1],PEOPLE.CRITERIA2 AS [CRITERIA2],PEOPLE.CRITERIA3 AS [CRITERIA3] FROM PEOPLE"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of People)(sql).ToList()
        End Using
    End Function
End Class

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel


''' <summary>
''' Provides a generic collection that supports data binding and additionally supports sorting.
''' See http://msdn.microsoft.com/en-us/library/ms993236.aspx
''' If the elements are IComparable it uses that; otherwise compares the ToString()
''' </summary>
''' <typeparam name="T">The type of elements in the list.</typeparam>
Public Class SortableBindingList(Of T As Class)
		Inherits BindingList(Of T)

		Private _isSorted As Boolean
		Private _sortDirection As ListSortDirection = ListSortDirection.Ascending
		Private _sortProperty As PropertyDescriptor

		''' <summary>
		''' Initializes a new instance of the <see cref="SortableBindingList{T}"/> class.
		''' </summary>
		Public Sub New()
		End Sub

		''' <summary>
		''' Initializes a new instance of the <see cref="SortableBindingList{T}"/> class.
		''' </summary>
		''' <param name="list">An <see cref="T:System.Collections.Generic.IList`1" /> of items to be contained in the <see cref="T:System.ComponentModel.BindingList`1" />.</param>
		Public Sub New(ByVal list As IList(Of T))
			MyBase.New(list)
		End Sub

		''' <summary>
		''' Gets a value indicating whether the list supports sorting.
		''' </summary>
		Protected Overrides ReadOnly Property SupportsSortingCore As Boolean
			Get
				Return True
			End Get
		End Property

		''' <summary>
		''' Gets a value indicating whether the list is sorted.
		''' </summary>
		Protected Overrides ReadOnly Property IsSortedCore As Boolean
			Get
				Return _isSorted
			End Get
		End Property

		''' <summary>
		''' Gets the direction the list is sorted.
		''' </summary>
		Protected Overrides ReadOnly Property SortDirectionCore As ListSortDirection
			Get
				Return _sortDirection
			End Get
		End Property

		''' <summary>
		''' Gets the property descriptor that is used for sorting the list if sorting is implemented in a derived class; otherwise, returns null
		''' </summary>
		Protected Overrides ReadOnly Property SortPropertyCore As PropertyDescriptor
			Get
				Return _sortProperty
			End Get
		End Property

		''' <summary>
		''' Removes any sort applied with ApplySortCore if sorting is implemented
		''' </summary>
		Protected Overrides Sub RemoveSortCore()
			_sortDirection = ListSortDirection.Ascending
			_sortProperty = Nothing
			_isSorted = False 'thanks Luca
		End Sub

''' <summary>
''' Sorts the items if overridden in a derived class
''' </summary>
''' <param name="prop"></param>
''' <param name="direction"></param>
Protected Overrides Sub ApplySortCore(ByVal prop As PropertyDescriptor, ByVal direction As ListSortDirection)
        _sortProperty = prop
        _sortDirection = direction

        Dim list As List(Of T) = TryCast(Items, List(Of T))
        If list Is Nothing Then
            Return
        End If

        list.Sort(AddressOf Compare)

        _isSorted = True
        'fire an event that the list has been changed.
        OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
End Sub
Private Function Compare(ByVal lhs As T, ByVal rhs As T) As Integer
			Dim result = OnComparison(lhs, rhs)
			'invert if descending
			If _sortDirection = ListSortDirection.Descending Then
				result = -result
			End If
			Return result
		End Function

		Private Function OnComparison(ByVal lhs As T, ByVal rhs As T) As Integer
			Dim lhsValue As Object = If(lhs Is Nothing, Nothing, _sortProperty.GetValue(lhs))
			Dim rhsValue As Object = If(rhs Is Nothing, Nothing, _sortProperty.GetValue(rhs))
			If lhsValue Is Nothing Then
				Return If(rhsValue Is Nothing, 0, -1) 'nulls are equal
			End If
			If rhsValue Is Nothing Then
				Return 1 'first has value, second doesn't
			End If
			If TypeOf lhsValue Is IComparable Then
				Return DirectCast(lhsValue, IComparable).CompareTo(rhsValue)
			End If
			If lhsValue.Equals(rhsValue) Then
				Return 0 'both are the same
			End If
			'not comparable, compare ToString
			Return lhsValue.ToString().CompareTo(rhsValue.ToString())
		End Function
End Class

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 3

Reply
ANSWER
User: dharmendr
Replied: on Aug 22, 2024 01:00 AM
Modified: on Aug 26, 2024 10:25 PM
Report

Hi kana250688,

Refer below updated code.

Code

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Security;
using System.Text;
using System.Threading.Tasks;
using Microsoft.VisualBasic;
using System.Data.OleDb;
using Dapper;

public class Form1
{
    private BindingSource bindingSource = null/* TODO Change to default(_) if this is not a reference type */;
    private SortableBindingList<People> _criteriasBindingList = new SortableBindingList<People>();
    private PeopleService Peopleservice = new PeopleService();

    public Form1()
    {
        InitializeComponent();
        this.CenterToScreen();
        DataGridViewCheckBoxColumn CheckedBoxColumn = new DataGridViewCheckBoxColumn();
        CheckedBoxColumn.Width = 40;
        CheckedBoxColumn.Name = "checkboxcolumn";
        CheckedBoxColumn.HeaderText = "Check";
        CheckedBoxColumn.ReadOnly = false;
        DataGridView1.Columns.Insert(0, CheckedBoxColumn);
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        Listpeople();
    }

    private void Listpeople()
    {
        _criteriasBindingList = new SortableBindingList<People>((IList<People>)Peopleservice.Getpeople());
        bindingSource = new BindingSource() { DataSource = _criteriasBindingList };
        DataGridView1.DataSource = bindingSource; // Set the data source.
        DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
    }

    private void myFilter(string idPeople, string dateTrans, string days)
    {
        Listpeople();
        IEnumerable<People> data = _criteriasBindingList;
        if (!string.IsNullOrEmpty(idPeople))
            data = data.Where(c => c.IDPEOPLE.ToLower().StartsWith(idPeople)).ToList();
        if (!string.IsNullOrEmpty(dateTrans))
        {
            DateTime dt;
            bool isValid = DateTime.TryParseExact(dateTrans, "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, ref dt);
            if (isValid)
                data = data.Where(c => c.DATETRANS.ToString["dd-MM-yyyy"].Equals(dateTrans)).ToList();
        }
        if (!string.IsNullOrEmpty(days))
            data = data.Where(c => c.DAYS.ToLower().StartsWith(days)).ToList();
        _criteriasBindingList = new SortableBindingList<People>((IList<People>)data);
        bindingSource.DataSource = _criteriasBindingList;
    }

    private void TXT_FILTER(object sender, KeyEventArgs e)
    {
        var idPeople = txtIDPEOPLE.Text.Trim().ToLower();
        var dateTrans = MaskedTextBoxDATETRANS.Text.Trim().ToLower();
        var days = txtDAYS.Text.Trim().ToLower();
        myFilter(idPeople, dateTrans, days);
    }
}

public class People
{
    public DateTime DATETRANS { get; set; }
    public string IDPEOPLE { get; set; }
    public string DAYS { get; set; }
    public string CRITERIA1 { get; set; }
    public string CRITERIA2 { get; set; }
    public string CRITERIA3 { get; set; }
}

public class PeopleService
{
    public string GetOledbConnectionString()
    {
        return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\test21082024.accdb;Persist Security Info=False;";
    }

    private readonly OleDbConnection _conn;
    private string _connectionString = GetOledbConnectionString();
    public PeopleService()
    {
        _conn = new OleDbConnection(_connectionString);
    }

    public IEnumerable<People> Getpeople()
    {
        var sql = "SELECT PEOPLE.DATETRANS AS [DATETRANS],format(PEOPLE.DATETRANS,'dddd') AS [DAYS],PEOPLE.IDPEOPLE AS [IDPEOPLE],PEOPLE.CRITERIA1 AS [CRITERIA1],PEOPLE.CRITERIA2 AS [CRITERIA2],PEOPLE.CRITERIA3 AS [CRITERIA3] FROM PEOPLE";
        using (var _conn = new OleDbConnection(GetOledbConnectionString()))
        {
            return _conn.Query<People>(sql).ToList();
        }
    }
}

VB.Net

Imports System.Data.OleDb
Imports System.Globalization
Imports Dapper

Public Class Form1
    Private bindingSource As BindingSource = Nothing
    Private _criteriasBindingList As New SortableBindingList(Of People)()
    Dim Peopleservice As New PeopleService()

    Public Sub New()
        InitializeComponent()
        Me.CenterToScreen()
        Dim CheckedBoxColumn As New DataGridViewCheckBoxColumn
        CheckedBoxColumn.Width = 40
        CheckedBoxColumn.Name = "checkboxcolumn"
        CheckedBoxColumn.HeaderText = "Check"
        CheckedBoxColumn.ReadOnly = False
        DataGridView1.Columns.Insert(0, CheckedBoxColumn)
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Listpeople()
    End Sub

    Private Sub Listpeople()
        _criteriasBindingList = New SortableBindingList(Of People)(CType(Peopleservice.Getpeople(), IList(Of People)))
        bindingSource = New BindingSource With {.DataSource = _criteriasBindingList}
        DataGridView1.DataSource = bindingSource 'Set the data source.
        DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
    End Sub

    Private Sub myFilter(idPeople As String, dateTrans As String, days As String)
        Listpeople()
        Dim data As IEnumerable(Of People) = _criteriasBindingList
        If Not String.IsNullOrEmpty(idPeople) Then
            data = data.Where(Function(c) c.IDPEOPLE.ToLower().StartsWith(idPeople)).ToList()
        End If
        If Not String.IsNullOrEmpty(dateTrans) Then
            Dim dt As Date
            Dim isValid As Boolean = DateTime.TryParseExact(dateTrans, "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, dt)
            If isValid Then
                data = data.Where(Function(c) c.DATETRANS.ToString("dd-MM-yyyy").Equals(dateTrans)).ToList()
            End If
        End If
        If Not String.IsNullOrEmpty(days) Then
            data = data.Where(Function(c) c.DAYS.ToLower().StartsWith(days)).ToList()
        End If
        _criteriasBindingList = New SortableBindingList(Of People)(CType(data, IList(Of People)))
        bindingSource.DataSource = _criteriasBindingList
    End Sub

    Private Sub TXT_FILTER(sender As Object, e As KeyEventArgs) Handles MaskedTextBoxDATETRANS.KeyUp, txtIDPEOPLE.KeyUp, txtDAYS.KeyUp
        Dim idPeople = txtIDPEOPLE.Text.Trim().ToLower()
        Dim dateTrans = MaskedTextBoxDATETRANS.Text.Trim().ToLower()
        Dim days = txtDAYS.Text.Trim().ToLower()
        myFilter(idPeople, dateTrans, days)
    End Sub
End Class

Public Class People
    Public Property [DATETRANS] As DateTime
    Public Property IDPEOPLE As String
    Public Property [DAYS] As String
    Public Property CRITERIA1 As String
    Public Property CRITERIA2 As String
    Public Property CRITERIA3 As String
End Class

Public Class PeopleService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\test21082024.accdb;Persist Security Info=False;"
    End Function

    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()
    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub

    Public Function Getpeople() As IEnumerable(Of People)
        Dim sql = "SELECT PEOPLE.DATETRANS AS [DATETRANS],format(PEOPLE.DATETRANS,'dddd') AS [DAYS],PEOPLE.IDPEOPLE AS [IDPEOPLE],PEOPLE.CRITERIA1 AS [CRITERIA1],PEOPLE.CRITERIA2 AS [CRITERIA2],PEOPLE.CRITERIA3 AS [CRITERIA3] FROM PEOPLE"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of People)(sql).ToList()
        End Using
    End Function
End Class

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Filter DataGridView records using TextBox KeyUp event in VB.Net

Company

Explore

Follow us

Contact Us