[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

FUNCTION TO SEARCH AND LOCATE DGV ROW

Hi All,

I want to have a function that could search and locate dgv row.

The function should be able to search multiple columns.
Those columns could be string, number and date.

My problem is how to create parameter contains :

1. Columns Name
2. Columns Type
3. Value to Search

and how to compare it.

How could I do it ?

Thank you.
0
emi_sastra
Asked:
emi_sastra
  • 13
  • 10
  • 3
1 Solution
 
adriankohwsCommented:
You can use unbound columns to add them manually into a datagridview.
The datagridview allows you to name the columns.

To access them, just name the column name, you don't need to specify column types to access them. There are so many events that you can use in a datagridview to access data. Give you an example below when an user double clicks on a cell:

Private Sub dgvPricematrix_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvPricematrix.CellDoubleClick
        Dim x As Int16 = dgvPricematrix.Rows(dgvPricematrix.CurrentCellAddress.Y).Cells("CustomerCode").RowIndex
        If MsgBox("Confirm deleting this record at row " & x & " ?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            ePriceCurRowID = x
            ePriceMaxRow -= 1
            eDeletePrice()
            dgvPricematrix.Rows.RemoveAt(x)
        End If
    End Sub
0
 
emi_sastraAuthor Commented:
Hi adriankohws,

I think you missed my question.
I want to make a function to search and locate dgv row.

Below is one column search code.

   Public Shared Function Binary_Search_GridView(ByVal dgvData As DataGridView, _
                                                  ByVal strKey As String, _
                                                  ByVal strColumnToSearch As String, _
                                                  Optional ByVal blnTrimSearch As Boolean = False) As Integer

        Dim strColumnSearch As String = ""

        If dgvData.Rows.Count = 0 Then Exit Function

        If IsNumeric(strColumnToSearch) Then
            strColumnSearch = dgvData.Columns(CInt(strColumnToSearch)).Name
        Else
            strColumnSearch = strColumnToSearch
        End If

        Dim strValue As String

        Binary_Search_GridView = 0

        If dgvData.RowCount <= 1 Then Exit Function

        With dgvData

            Dim intCurrentRow = .CurrentRow.Index

            For intRow As Integer = 0 To .Rows.Count - 1

                strValue = dgvData.Rows(intRow).Cells(strColumnSearch).Value.ToString.Trim

                If blnTrimSearch Then
                    Dim intLength As Integer = Len(strKey.Trim)

                    If strValue.Trim.Length < intLength Then
                        intLength = strValue.Trim.Length
                    End If

                    strValue = strValue.Substring(0, intLength)
                End If

                If strValue.Trim = strKey.Trim Then
                    Return intRow
                End If

            Next

            Return intCurrentRow

        End With

    End Function

How could I make it to search multiple columns ?

Thank you.
0
 
CodeCruiserCommented:
You can write another function which takes a list of column names and then calls this function in a loop for each column
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
emi_sastraAuthor Commented:
Hi CodeCruiser,

-You can write another function which takes a list of column names
What should I use for list of columns ? Array, list, collection ?

- and then calls this function in a loop for each column
Have no idea yet.

Thank you.
0
 
adriankohwsCommented:
You have to think a bit yourself. CodeCruiser meant that since my code can get "one column", why you can't change the code to a loop and get all columns? Be it "column names" or "column indexes", it shouldn't be a problem do figure out something like this.
0
 
CodeCruiserCommented:
Here is just an example

Public Shared Function Binary_Search_GridView_AllColumns(ByVal dgvData As DataGridView, _
                                                  ByVal strKey As String, _
                                                  ByVal strColumnsToSearch As List(of String), _
                                                  Optional ByVal blnTrimSearch As Boolean = False) As List(Of Integer)

Dim res As New List(of Integers)
For Each Col As String in strColumnsToSearch
      res.Add(Binary_Search_GridView(dgvData, strKey, Col)
Next

Return res
End Function
0
 
emi_sastraAuthor Commented:
Hi CodeCruiser,

Perhaps my explanation is not clear enough.

What I mean of multiple columns is not search for several rows, but searching one row by matching several columns.

Thank you.
0
 
CodeCruiserCommented:
What if multiple rows match? Take first one?
0
 
emi_sastraAuthor Commented:
- What if multiple rows match? Take first one?
Will not be the case, but if it is then take first one.
As soon as we get the first row match, should be return to caller.

Thank you.
0
 
emi_sastraAuthor Commented:
By the way, could we use function/method from VB to search it ?

Thank you.
0
 
CodeCruiserCommented:
Is this grid bound to a datatable? If so, you can use the RowFilter of dataview to do the filtering.
0
 
emi_sastraAuthor Commented:
-Is this grid bound to a datatable?
Yes, it is.

-If so, you can use the RowFilter of dataview to do the filtering.
But I don't want to filter it. I want to show all data and just point the row I want.
The purpose is after I add a data, I reopen the data and I want to locate to the new data just inputed.

Thank you.
0
 
CodeCruiserCommented:
Ok here is your updated function

Public Shared Function Binary_Search_GridView_AllCols(ByVal dgvData As DataGridView, _
                                                  ByVal strKey As String, _
                                                  ByVal strColumnsToSearch As List(Of String), _
                                                  Optional ByVal blnTrimSearch As Boolean = False) As Integer

        Dim strColumnSearch As String = ""

        If dgvData.Rows.Count = 0 Then Exit Function

        Dim strValue As String

        Binary_Search_GridView = 0

        If dgvData.RowCount <= 1 Then Exit Function

        With dgvData

            Dim intCurrentRow = .CurrentRow.Index

            For intRow As Integer = 0 To .Rows.Count - 1
              For Each strColumnSearch As String in strColumnsToSearch
                   If IsNumeric(strColumnSearch) Then
                      strColumnSearch = dgvData.Columns(CInt(strColumnSearch)).Name
                   End If

                strValue = dgvData.Rows(intRow).Cells(strColumnSearch).Value.ToString.Trim

                If blnTrimSearch Then
                    Dim intLength As Integer = Len(strKey.Trim)

                    If strValue.Trim.Length < intLength Then
                        intLength = strValue.Trim.Length
                    End If

                    strValue = strValue.Substring(0, intLength)
                End If

                If strValue.Trim = strKey.Trim Then
                    Return intRow
                End If
              Next
            Next

            Return intCurrentRow

        End With

    End Function

Open in new window



This code is untested and you may have to tweak it.
0
 
emi_sastraAuthor Commented:
Hi CodeCruiser,

Public Shared Function Binary_Search_GridView_AllCols(ByVal dgvData As DataGridView, _
                                                  ByVal strKey As String, _
                                                  ByVal strColumnsToSearch As List(Of String), _
                                                  Optional ByVal blnTrimSearch As Boolean = False) As Integer
End Sub

I think  ByVal strKey As String should be changed to ByVal strKey As List(Of String). Since we want to check every column, we should pass its value for each of the column.

Now, for example we have 3 columns  to search. We will call :

Dim strKeys As New List(Of String)
Dim strColumns As New List(Of String)

strKeys.Add("A")
strKeys.Add(1)
strKeys.Add("02/03/2013")

strColumns .Add("TrsNo")
strColumns .Add("ItemSeq")
strColumns .Add("TrsDate")

Binary_Search_GridView_AllCols(dgvData, strKeys, strColumns, false)

Now, the problem is how to compare them for all of the columns ?
It should match all of the columns then return found row,  otherwise return intCurrentRow.

Thank you.




Thannk you.
0
 
CodeCruiserCommented:
I thought you are looking for same value in multiple columns.
0
 
emi_sastraAuthor Commented:
-I thought you are looking for same value in multiple columns.
Well, not actually. My mistake if my question is not clear enough.

Logically thinking, when we want to locate a dgv row, then those columns to search normally will be at one row.

Thank you.
0
 
adriankohwsCommented:
What are you talking about? Regardless you want to search or not, what columns are there are there, and they exist in all rows. What logically thinking is that? It is hard to understand your question.
0
 
CodeCruiserCommented:
Can you give us some background as to why you need to do this?
0
 
emi_sastraAuthor Commented:
-Can you give us some background as to why you need to do this?
Sometimes a table has composite keys. Thus when we add data to it. The composite keys should be unique.

What I want to search is those composite keys columns.

Hope this is clear enough.

Thank you.
0
 
CodeCruiserCommented:
That's not the background information I was hoping for. Why the search has to be in datagridview?
0
 
emi_sastraAuthor Commented:
-Why the search has to be in datagridview?
Because datagridview is at parent form and input form is a child form.
But it could be at the same form, just locate it side by side.
Just want to show to user that the data inputed has saved.

Thank you.
0
 
CodeCruiserCommented:
I finally got chance to try this. Here is the code that I tried and it worked

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim dtable As New DataTable
        dtable.Columns.Add("Col1", GetType(String))
        dtable.Columns.Add("Col2", GetType(String))
        dtable.Columns.Add("Col3", GetType(String))
        dtable.Columns.Add("Col4", GetType(String))

        For i As Integer = 1 To 10
            dtable.Rows.Add(New Object() {"Col1Val" & i, "Col2Val" & i, "Col3Val" & i, "Col4Val" & i})
        Next
        DataGridView1.DataSource = dtable
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim dict As New Dictionary(Of String, String)
        dict.Add("Col1", "Col1Val4")
        dict.Add("Col2", "Col2Val4")
        dict.Add("Col3", "Col3Val4")
        dict.Add("Col4", "Col4Val4")

        DataGridView1.CurrentCell = DataGridView1.Item(0, Binary_Search_GridView_AllCols(DataGridView1, dict, False))
    End Sub

    Public Function Binary_Search_GridView_AllCols(ByVal dgvData As DataGridView, _
                                                  ByVal strColumnsToSearch As Dictionary(Of String, String), _
                                                  Optional ByVal blnTrimSearch As Boolean = False) As Integer

        If dgvData.Rows.Count = 0 Then Exit Function

        Dim strValue As String
        Dim matchedColCount As Integer

        With dgvData

            Dim intCurrentRow = .CurrentRow.Index

            For intRow As Integer = 0 To .Rows.Count - 1
                For Each strColumnSearch As String In strColumnsToSearch.Keys
                    If IsNumeric(strColumnSearch) Then
                        strColumnSearch = dgvData.Columns(CInt(strColumnSearch)).Name
                    End If

                    strValue = dgvData.Rows(intRow).Cells(strColumnSearch).Value.ToString.Trim

                    If strValue.Trim = strColumnsToSearch.Item(strColumnSearch).Trim Then
                        matchedColCount += 1
                    End If
                Next
                If matchedColCount = strColumnsToSearch.Keys.Count Then
                    Return intRow
                Else
                    matchedColCount = 0
                End If
            Next

            Return intCurrentRow

        End With

    End Function
End Class

Open in new window

0
 
emi_sastraAuthor Commented:
Hi CodeCruiser,

I think below code would have problem.

strValue = dgvData.Rows(intRow).Cells(strColumnSearch).Value.ToString.Trim

                    If strValue.Trim = strColumnsToSearch.Item(strColumnSearch).Trim Then
                        matchedColCount += 1
                    End If

The strValue is string, while data at gridview could be string, numeric or date.

Am I correct ?

Thank you.
0
 
CodeCruiserCommented:
Give it a try with and without trim functions.
0
 
emi_sastraAuthor Commented:
-Give it a try with and without trim functions.
Not working, especially with date data.

I try below:

Public Shared Function Binary_Search_GridView_AllCols(ByVal dgvData As DataGridView, _
                                                  ByVal strColumnsToSearch As Dictionary(Of String, String), _
                                                  Optional ByVal blnTrimSearch As Boolean = False) As Integer

        If dgvData.Rows.Count = 0 Then Return 0

        Dim strValue As String = ""

        Dim matchedColCount As Integer = 0
        Dim intCurrentRow = dgvData.CurrentRow.Index

        Try

            With dgvData

                For intRow As Integer = 0 To .Rows.Count - 1
                    For Each strColumnSearch As String In strColumnsToSearch.Keys
                        If IsNumeric(strColumnSearch) Then
                            strColumnSearch = dgvData.Columns(CInt(strColumnSearch)).Name
                        End If

                        strValue = dgvData.Rows(intRow).Cells(strColumnSearch).Value.ToString.Trim

                        If IsDate(strValue) Then
                            If Format(CDate(strValue.Trim), "yyyyMMdd") = Format(CDate(strColumnsToSearch.Item(strColumnSearch)), "yyyyMMdd") Then
                                matchedColCount += 1
                            End If
                        Else
                            If strValue.Trim = strColumnsToSearch.Item(strColumnSearch).Trim Then
                                matchedColCount += 1
                            End If
                        End If

                    Next

                    If matchedColCount = strColumnsToSearch.Keys.Count Then
                        Return intRow
                    Else
                        matchedColCount = 0
                    End If
                Next

            End With

        Catch ex As Exception

        End Try

        Return intCurrentRow

    End Function

Could I do something about it ?

Thank you.
0
 
emi_sastraAuthor Commented:
Hi CodeCruiser,

I think I get some inspiration from your code.

Thank you very much for your help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 13
  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now