Hi kana250688,
Instead of looping through the BindingSource.DataSource, loop through the SortableBindingList i.e. filtered record of _criteriasBindingList.
Refer below sample.
VB.Net
Imports System.ComponentModel
Imports System.Data.OleDb
Imports Dapper
Public Class Form1
Private PurchaseDetails, SaleDetails As New List(Of Detail)
Private _criteriasBindingList As New SortableBindingList(Of StockCards)()
Private bindingSource As BindingSource = Nothing
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Stockscard.accdb;Persist Security Info=False;"
End Function
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'LoadData()
End Sub
Private Sub BtxtcodeproductIN_KeyPress(sender As Object, e As KeyPressEventArgs) Handles BtxtcodeproductIN.KeyPress
If e.KeyChar = Convert.ToChar(Keys.Enter) Then
LoadData()
End If
End Sub
Private Sub LoadData()
Using Connection = New OleDbConnection(GetOledbConnectionString())
PurchaseDetails = CType(Connection.Query(Of Detail)("SELECT * FROM Stocksin"), List(Of Detail))
SaleDetails = CType(Connection.Query(Of Detail)("SELECT * FROM Stocksout"), List(Of Detail))
End Using
Dim ps = From pd In PurchaseDetails
Where pd.CodeProduct = BtxtcodeproductIN.Text
Select
Invodate = pd.InvnoDate,
pd.CodeProduct,
pd.Criteria1,
pd.Criteria2,
pd.Criteria3,
pd.Criteria4,
pd.Criteria5,
[IN] = pd.Qty,
[OUT] = 0,
BLC = pd.Qty
Order By Invodate
Dim ss = From sd In SaleDetails
Where sd.CodeProduct = BtxtcodeproductIN.Text
Select
Invodate = sd.InvnoDate,
sd.CodeProduct,
sd.Criteria1,
sd.Criteria2,
sd.Criteria3,
sd.Criteria4,
sd.Criteria5,
[IN] = 0,
[OUT] = sd.Qty,
BLC = -sd.Qty
Order By Invodate
Dim Card_temp = ps.Union(ss).OrderBy(Function(w) w.Invodate)
Dim Card As New List(Of StockCards)
Dim RunningBalance As Integer = 0
For Each ct In Card_temp
Dim sc As New StockCards
With sc
.InvnoDate = ct.Invodate
.CodeProduct = ct.CodeProduct
.Criteria1 = ct.Criteria1
.Criteria2 = ct.Criteria2
.Criteria3 = ct.Criteria3
.Criteria4 = ct.Criteria4
.Criteria5 = ct.Criteria5
.IN = ct.IN
.OUT = ct.OUT
.BLC = RunningBalance + ct.BLC
RunningBalance = .BLC
End With
Card.Add(sc)
Next
_criteriasBindingList = New SortableBindingList(Of StockCards)(CType(Card, IList(Of StockCards)))
bindingSource = New BindingSource With {.DataSource = _criteriasBindingList}
DataGridView1.DataSource = bindingSource
DataGridView1.AutoResizeColumns()
DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
End Sub
Private Sub myFilter(str1 As String, str2 As String, str3 As String, str4 As String, str5 As String)
Dim _cbl As List(Of StockCards) = New List(Of StockCards)
If (String.IsNullOrEmpty(str1) AndAlso String.IsNullOrEmpty(str2) AndAlso String.IsNullOrEmpty(str3) AndAlso String.IsNullOrEmpty(str4) AndAlso String.IsNullOrEmpty(str5)) Then
_cbl = _criteriasBindingList.ToList()
ElseIf (String.IsNullOrEmpty(str1)) Then
_cbl = _criteriasBindingList.Where(Function(c) c.Criteria2.ToLower().Contains(str2) AndAlso c.Criteria3.ToLower().Contains(str3) AndAlso c.Criteria4.ToLower().Contains(str4) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
ElseIf (String.IsNullOrEmpty(str2)) Then
_cbl = _criteriasBindingList.Where(Function(c) c.Criteria1.ToLower().Contains(str1) AndAlso c.Criteria3.ToLower().Contains(str3) AndAlso c.Criteria4.ToLower().Contains(str4) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
ElseIf (String.IsNullOrEmpty(str3)) Then
_cbl = _criteriasBindingList.Where(Function(c) c.Criteria1.ToLower().Contains(str1) AndAlso c.Criteria2.ToLower().Contains(str2) AndAlso c.Criteria4.ToLower().Contains(str4) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
ElseIf (String.IsNullOrEmpty(str4)) Then
_cbl = _criteriasBindingList.Where(Function(c) c.Criteria1.ToLower().Contains(str1) AndAlso c.Criteria2.ToLower().Contains(str2) AndAlso c.Criteria3.ToLower().Contains(str3) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
Else
_cbl = _criteriasBindingList.Where(Function(c) c.Criteria1.ToLower().Contains(str1) AndAlso c.Criteria2.ToLower().Contains(str2) AndAlso c.Criteria3.ToLower().Contains(str3) AndAlso c.Criteria4.ToLower().Contains(str4) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
End If
bindingSource.DataSource = _cbl
Dim runningBalance As Integer = 0
For Each ct As StockCards In _cbl
runningBalance = (runningBalance + ct.IN) - ct.OUT
ct.BLC = runningBalance
Next
End Sub
Private Sub txt_TextChanged(sender As Object, e As EventArgs) Handles txtCriteria1.TextChanged, txtCriteria2.TextChanged, txtCriteria3.TextChanged, txtCriteria4.TextChanged, txtCriteria5.TextChanged
Dim str1 = txtCriteria1.Text.Trim().ToLower()
Dim str2 = txtCriteria2.Text.Trim().ToLower()
Dim str3 = txtCriteria3.Text.Trim().ToLower()
Dim str4 = txtCriteria4.Text.Trim().ToLower()
Dim str5 = txtCriteria5.Text.Trim().ToLower()
myFilter(str1, str2, str3, str4, str5)
End Sub
End Class
Public Class StockCards
Public Property InvnoDate() As DateTime
Public Property CodeProduct() As String
Public Property Criteria1() As String
Public Property Criteria2() As String
Public Property Criteria3() As String
Public Property Criteria4() As String
Public Property Criteria5() As String
Public Property [IN] As Integer
Public Property [OUT] As Integer
Public Property BLC As Integer
End Class
Public Class Detail
Public Property InvnoDate() As DateTime
Public Property CodeProduct() As String
Public Property Criteria1() As String
Public Property Criteria2() As String
Public Property Criteria3() As String
Public Property Criteria4() As String
Public Property Criteria5() As String
Public Property Qty() As Integer
End Class
Screenshot
