Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to search across multiple colums?

Posted on 2013-01-16
9
Medium Priority
?
236 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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…
Loops Section Overview

972 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