Solved

FUNCTION TO SEARCH AND LOCATE DGV ROW

Posted on 2013-06-07
30
437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
  • 3
30 Comments
 
LVL 10

Expert Comment

by:adriankohws
ID: 39231098
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
ID: 39232319
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
ID: 39238981
You can write another function which takes a list of column names and then calls this function in a loop for each column
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 1

Author Comment

by:emi_sastra
ID: 39239850
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
ID: 39241131
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
ID: 39242506
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
ID: 39243184
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
ID: 39245215
What if multiple rows match? Take first one?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39246426
- 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
ID: 39247051
By the way, could we use function/method from VB to search it ?

Thank you.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39247156
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
ID: 39247209
-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
ID: 39247669
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 39249233
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
ID: 39249285
I thought you are looking for same value in multiple columns.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39249373
-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
ID: 39249563
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
ID: 39249857
Can you give us some background as to why you need to do this?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39250726
-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
ID: 39251221
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
ID: 39251253
-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
ID: 39268661
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
ID: 39268717
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
ID: 39268735
Give it a try with and without trim functions.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39268856
-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
ID: 39392547
Hi CodeCruiser,

I think I get some inspiration from your code.

Thank you very much for your help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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