Solved

How to search across multiple colums?

Posted on 2013-01-16
9
225 Views
Last Modified: 2013-01-22
I am attempting to create a searchbox that allows a person to search for something across the columns. This works for a partial string but it does not move to the next record and I attempted to do a string array for my 7 column names with no avail. I looked on the internet for guidance and came up empty. Maybe someone here has written a similar function and can offer guidance. Thanks.


Private Function findPart(ByVal strSearch As String, ByVal colName As String) As Boolean

        Dim Found As Boolean
        Dim strToSearch As String = strSearch.Trim.ToUpper
        Dim curRowInt As Integer = 0

        dgViewClients.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        Try
            If dgViewClients.Rows.Count = 0 Then
                curRowInt = 0
            Else
                curRowInt = dgViewClients.CurrentRow.Index + 1
            End If
            If curRowInt > dgViewClients.Rows.Count Then
                curRowInt = dgViewClients.Rows.Count - 1
            End If
            If dgViewClients.Rows.Count > 0 Then
                For Each row As DataGridViewRow In dgViewClients.Rows
                    strToSearch = row.Cells(colName).Value.ToString.Trim.ToUpper
                    If strToSearch.Contains(strSearch) Then
                        Dim mCurrCell As DataGridViewCell =
                            row.Cells(colName)
                        dgViewClients.CurrentCell = mCurrCell
                        Found = True
                    End If
                    If Found Then
                        Exit For
                    End If
                Next
            End If
            If Not Found Then
                If dgViewClients.Rows.Count > 0 Then
                    Dim mFstCurCell As DataGridViewCell =
                        dgViewClients.Rows(0).Cells(colName)
                    dgViewClients.CurrentCell = mFstCurCell
                End If
            End If
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Information Or MsgBoxStyle.OkOnly)
        End Try
        Return Found
    End Function
0
Comment
Question by:shellee1983
  • 6
  • 3
9 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38784019
>For Each row As DataGridViewRow In dgViewClients.Rows
>strToSearch = row.Cells(colName).Value.ToString.Trim.ToUpper


To search across all columns, you would need to use an inner loop similar to below

For Each Cell As DataGridViewCell In row.Cells
   ...
Next




Would something like below make it easier?

http://www.codeproject.com/Articles/9947/DataGrid-with-built-in-filter-functionality
0
 

Author Comment

by:shellee1983
ID: 38784154
Would something like below make it easier?

http://www.codeproject.com/Articles/9947/DataGrid-with-built-in-filter-functionality

That is a great concept however I am trying to limit the things the user has to do so basically I have one search box where they enter a partial string and the code now only can search one column at a time. I tried to do an array of the columns outside the function because I am using it as a partial public class between two forms but that broke it so back to the drawing board. I will try this and see what happens. I also need to see how to make it loop to the next match because that does not appear to be working.
0
 

Author Comment

by:shellee1983
ID: 38784605
I figured out how to do it with an array, now I just can't figure out how to get it to cycle through the searched partial string to highlight the next found row. Anyone have ideas?

  Try

            If dgViewClients.Rows.Count = 0 Then
                curRowInt = 0
            Else
                curRowInt = dgViewClients.CurrentRow.Index + 1
            End If
            If curRowInt > dgViewClients.Rows.Count Then
                curRowInt = dgViewClients.Rows.Count - 1
            End If
            If dgViewClients.Rows.Count > 0 Then
                For Each row As DataGridViewRow In dgViewClients.Rows
                    'For Each cell As DataGridViewCell In row.Cells
                    For Each colName In colStr
                        strToSearch = row.Cells(colName).Value.ToString.Trim.ToUpper
                        If strToSearch.Contains(strSearch.ToUpper) Then
                            Dim mCurrCell As DataGridViewCell =
                                row.Cells(colName)
                            dgViewClients.CurrentCell = mCurrCell
                            Found = True
                        End If
                        If Found Then
                            Exit For
                        End If
                    Next
                Next
            End If
            'If Not Found Then
            '    If dgViewClients.Rows.Count > 0 Then
            '        Dim mFstCurCell As DataGridViewCell =
            '            dgViewClients.Rows(0).Cells(colName)
            '        dgViewClients.CurrentCell = mFstCurCell
            '    End If
            'End If
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Information Or MsgBoxStyle.OkOnly)
        End Try
        Return Found
    End Function
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38784733
Change this

dgViewClients.CurrentCell = mCurrCell

to

row.Selected = True


and remove

                        If Found Then
                            Exit For
                        End If
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:shellee1983
ID: 38784809
if it never exits the for will it have an infinite loop? I'm just curious because that's why I had the exit for in the first place. Also that code highlights all of them I was trying to think of a way to cycle through the next portion of it.  as in:

if found = true then
mCurrCell.next (I know thats not the code but I am trying to figure that out lol)
end if
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38784842
Its not infinite loop. It runs for each row.
0
 

Author Comment

by:shellee1983
ID: 38784936
I see the difference now I accidentally created an infinate loop by doing a do until.....it didn't end well. So now I just need to find how to iterate through the search and it will be golden. Thanks for your help thus far CodeCruiser.
0
 

Accepted Solution

by:
shellee1983 earned 0 total points
ID: 38789308
Found the solution. Co worker helped on it. Had to add a starting index and a loop iterator. Thanks.
0
 

Author Closing Comment

by:shellee1983
ID: 38804816
The reason was that I found the information via a co worker and the solutions provided did not assist in any manner as I did not modify the code as per the suggestions as can be seen here:

 Dim Found As Boolean
        Dim strToSearch As String = strSearch.Trim.ToUpper
        Dim curRowInt As Integer = 0
        Dim colStr As Array
        Dim colName As String
        Dim i As Integer
        'Dim findStr As Array

        colStr = {"Report_Type", "Client_Number", "Client_Name", "Client_Type", "Transmission_Type", "Local", "TOA", "Contact_Person", "Phone_Number", "EMail_Address"}

        dgViewClients.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        Found = False

        If strLastSearch <> txtSearch.Text Then
            iStartRow = 0
        End If

        Try

            If dgViewClients.Rows.Count = 0 Then
                curRowInt = 0
            Else
                curRowInt = dgViewClients.CurrentRow.Index + 1
            End If
            If curRowInt > dgViewClients.Rows.Count Then
                curRowInt = dgViewClients.Rows.Count - 1
            End If
            If dgViewClients.Rows.Count > 0 Then
                For i = iStartRow To dgViewClients.Rows.Count - 1
                    If Found = False Then
                        For Each colName In colStr
                            strToSearch = dgViewClients.Rows(i).Cells(colName).Value.ToString.Trim.ToUpper
                            If strToSearch.Contains(strSearch.ToUpper) Then
                                Dim mCurrCell As DataGridViewCell =
                                 dgViewClients.Rows(i).Cells(colName)
                                dgViewClients.CurrentCell = mCurrCell
                                Found = True
                                iStartRow = i + 1
                                Exit For
                            End If
                        Next colName
                    End If
                Next i
            End If
            If Not Found Then
                MsgBox("Searched Value  " & txtSearch.Text & "  Not Found. Please try again.", MsgBoxStyle.Information Or MsgBoxStyle.OkOnly)
            End If
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Information Or MsgBoxStyle.OkOnly)
        End Try
        Return Found
    End Function
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

13 Experts available now in Live!

Get 1:1 Help Now