Solved

FUNCTION TO SEARCH AND LOCATE DGV ROW

Posted on 2013-06-07
30
431 Views
Last Modified: 2013-11-26
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
Comment
Question by:emi_sastra
  • 13
  • 10
  • 3
30 Comments
 
LVL 10

Expert Comment

by:adriankohws
Comment Utility
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
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
You can write another function which takes a list of column names and then calls this function in a loop for each column
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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
 
LVL 10

Expert Comment

by:adriankohws
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
What if multiple rows match? Take first one?
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
- 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
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
By the way, could we use function/method from VB to search it ?

Thank you.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Is this grid bound to a datatable? If so, you can use the RowFilter of dataview to do the filtering.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
-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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
I thought you are looking for same value in multiple columns.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
-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
 
LVL 10

Expert Comment

by:adriankohws
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Can you give us some background as to why you need to do this?
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
-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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
That's not the background information I was hoping for. Why the search has to be in datagridview?
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
-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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Give it a try with and without trim functions.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
-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
 
LVL 1

Author Closing Comment

by:emi_sastra
Comment Utility
Hi CodeCruiser,

I think I get some inspiration from your code.

Thank you very much for your help.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now