none
How to do the Search Next if there are duplicate value? RRS feed

  • Question

  • Afternoon,

    I have a bit of trouble with my searching. I have the code which have ability only to search for one specific value at a time but problem is that IA Code can be duplicated. For example: Code: 0001 can have more than 1 or even up to 5 with different Name, DOB ...

     Dim strExel As String = "select [Name], [Sex], [DateofBirth], [PrimaryTelephone], [2ndTelephone], [NationalIDCard], [IACode] from [Sheet1$] where [PrimaryTelephone] = '" & txtPhoneSearch.Text & "' OR [2ndTelephone] ='" & txtPhoneSearch.Text & "' OR [IACode] ='" & txtPhoneSearch.Text & "'"
    
            Dim MyCom As New System.Data.OleDb.OleDbDataAdapter(strExel, MyConnection)
    
            MyCom.Fill(dt)
    
            For Each row As DataRow In dt.Rows
                tbNAME.Text = row("Name").ToString()
                tbSEX.Text = row("Sex").ToString()
                tbDOB.Text = row("DateofBirth").ToString()
                tbP1.Text = row("PrimaryTelephone").ToString()
                tbP2.Text = row("2ndTelephone").ToString()
                tbIDNo.Text = row("NationalIDCard").ToString()
                tbCODE.Text = row("IACode").ToString()
    
            Next
                If dt.Rows.Count < 1 Then
                MsgBox("No Found")
            End If

    To sum up, I type code in textbox then when I click search it shows Name, Sex, etc. but if those are not the name I need, I click Search again it will show me the next one that has different name, sex, etc. but same code as i type it.

    Hope it is possible to do and not that complicated. Very appreciate your time and thank so much.

    Wednesday, April 18, 2018 6:40 AM

All replies

  • Hi

    Maybe consider using a Filter to list all matches.

    Here is some code as a stand alone project to illustrate. It shows some of the methods you could adapt to suit your own project.

    ' This example needs a Form1 with
    ' a blank DataGridView1, TextBox1,
    ' Button1, Button2, Button3
    ' and ListBox1
    
    ' DataTable, Search, Match, Filter, DataBinding
    ' TextBox, Binding
    Option Strict On
    Option Explicit On
    Public Class Form1
      ' set path for example save/load data
      Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.Desktop & "\MyData.xml"
    
      Dim myTable As New DataTable("Freddy")
      Dim view As New DataView(myTable)
      Dim Prs As New List(Of Pr)
      Dim Hfnt As Font = New Font("Arial", 12, FontStyle.Bold)
      Dim HcolF As Color = Color.Blue
      Dim HcolB As Color = Color.Yellow
      Dim defColF As Color = Color.Black
      Dim defColB As Color = Color.White
      Dim defFnt As Font = Nothing
    
      ' ADDED ***********
      Dim CM As CurrencyManager
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    	'  add some dummy data 
    	' (not needed once a Save has been done)
    	With myTable
    	  .Columns.Add("Name", GetType(String))
    	  .Columns.Add("Roll Number", GetType(String))
    	  .Columns.Add("Subject1", GetType(String))
    	  .Columns.Add("Subject2", GetType(String))
    	  .Columns.Add("Score", GetType(String))
    	  .Rows.Add("N1", 5, "Math", "Phy", 20.7456D)
    	  .Rows.Add("N2", 3, "Social", "Chem", 15.5D)
    	  .Rows.Add("N1", 1, "Math", "Phy", 11.5D)
    	  .Rows.Add("N2", 3, "Social", "Math", 25.7D)
    	  .Rows.Add("N1", 2, "Math", "Phy", 20.9D)
    	  .Rows.Add("N2", 1, "Social", "Math", 18.5D)
    	  .Rows.Add("N1", 2, "English", "Phy", 23.5D)
    	  .Rows.Add("N2", 3, "Social", "Math", 25.7D)
    	End With
    
    	' ADDED ***********
    	CM = CType(BindingContext(myTable), CurrencyManager)
    
    	' adjust DGV properties
    	With DataGridView1
    	  .DataSource = view
    	  .MultiSelect = False
    	  .RowHeadersWidth = 24
    	  .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
    	  .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    	  .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
    	  .SelectionMode = DataGridViewSelectionMode.CellSelect
    	  For i As Integer = 0 To 4
    		.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    	  Next
    	End With
    	' grab initial font
    	defFnt = DataGridView1.DefaultCellStyle.Font
    
    	' ADDED ***********
    	TextBox2.DataBindings.Add("Text", myTable, "Name")
    	TextBox3.DataBindings.Add("Text", myTable, "Roll Number")
    	TextBox4.DataBindings.Add("Text", myTable, "Subject1")
    	TextBox5.DataBindings.Add("Text", myTable, "Subject2")
    	TextBox6.DataBindings.Add("Text", myTable, "Score")
      End Sub
    
      ' ADDED ***********
      Private Sub DataGridView1_RowEnter(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
    	CM.Position = e.RowIndex
      End Sub
    
      Sub Srch(s As String)
    	ListBox1.Items.Clear()
    	Prs.Clear()
    	DataGridView1.ClearSelection()
    	For Each r As DataGridViewRow In DataGridView1.Rows
    	  If Not r.Index = DataGridView1.NewRowIndex Then
    		For Each c As DataGridViewCell In r.Cells
    		  If s.Length > 0 AndAlso c.Value.ToString.ToLower.Contains(s.ToLower) Then
    			c.Style.ForeColor = HcolF
    			c.Style.BackColor = HcolB
    			c.Style.Font = Hfnt
    			ListBox1.Items.Add("Row " & c.RowIndex.ToString & " Col " & c.ColumnIndex.ToString & " Title " & c.OwningColumn.HeaderText)
    			Prs.Add(New Pr With {.Row = c.RowIndex, .Column = c.ColumnIndex})
    		  Else
    			c.Style.ForeColor = defColF
    			c.Style.BackColor = defColB
    			c.Style.Font = defFnt
    		  End If
    		Next
    	  End If
    	Next
      End Sub
      Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    	Srch(Trim(TextBox1.Text))
      End Sub
      Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
    	Dim lb As ListBox = DirectCast(sender, ListBox)
    	DataGridView1.ClearSelection()
    	DataGridView1(Prs(ListBox1.SelectedIndex).Column, Prs(ListBox1.SelectedIndex).Row).Selected = True
    	TextBox1.Select()
      End Sub
      Class Pr
    	Property Row As Integer
    	Property Column As Integer
      End Class
    
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    	' save all the data
    	myTable.WriteXml(DataPath)
      End Sub
    
      Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    	' load all data
    	myTable.ReadXml(DataPath)
      End Sub
    
      Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    	' clear out all data
    	myTable.Clear()
      End Sub
    End Class


    Regards Les, Livingston, Scotland

    Wednesday, April 18, 2018 12:50 PM
  • I think you need another button, in order to move to the next item in the DataTable. Instead of using For Each...Next, assign the first row to the TextBoxes on your Form (if any rows are returned). Here is a simple example:

    Dim currentIndex As Integer = 0 'search and fill the DataTable 'display first row on form if available If dt.Rows.Count <> 0 Then tbName.Text = dt.Rows(0)("Name").ToString() tbSEX.Text = dt.Rows(0)("Sex").ToString() '... '... End If '------------------------------------------------- '--Next button click 'Display next row on Form if available

    currentIndex = currentIndex + 1 If currentIndex < dt.Rows.Count Then tbName.Text = dt.Rows(currentIndex)("Name").ToString() tbSEX.Text = dt.Rows(currentIndex)("Sex").ToString() '... '... Else 'no more rows End If



    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 18, 2018 1:31 PM
  • Thank you so much. I tried it but it started getting complicated for some reason.
    Wednesday, April 18, 2018 1:51 PM
  • Sir,

    Is it possible to use the same button, because the form is too neat for more button.

    Wednesday, April 18, 2018 1:53 PM
  • From which row in your code do you think the data are in the textboxes?

    There is no selection, it is the last row of the table. 

    If you want a selection of what you did then use for instance a DataGridView

      MyCom.Fill(dt)
    
                If dt.Rows.Count < 1 Then
                MsgBox("No Found")
            else
            myDataGridView.DataSource = dt
            end if



    Success
    Cor

    Wednesday, April 18, 2018 3:06 PM
  • You can use a IEnumerator for your issue.

    currently, you create a query, and fill a DataTable with the result. You do not show how your UI gets filled. I assume it currently presents Rows[0] or maybe just the whole collection.

    Instead you could create a class Search.

    The DataTable you fill will be a field in your Search class ("ResultTable"). You also create a Property "NextResult" in your Search class (of type Row) it will have a getter only. You also introduce a IEnumartor<DataRow> Property "ResultEnumerator" in your Search class.

    You have a Method accepting the query and store it in a property.

    If the query given to the method changed, you execute the search and set the ResultTable you also set the ResultEnumerator to null. Regardless of the change or not of the query, you return NextResult. The getter of NextResult will execute MoveNext on the ResultEnumerator and return ResultEnumerator.Current. If the MoveNext returns false (meaning no more entries) you show a MsgBox ("No more entries") and set the ResultENumerator to null again (you also return null ofc). The ResultEnumerator will have a getter that creates a new IEnumertor by callling ResultTable.Rows.GetEnumertor() if the ResultEnumerator is currently null.


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.

    Wednesday, April 18, 2018 3:40 PM
  • This example shows how a button can be both.  It uses the data posted by leshay.  The example requires a button and a textbox

        Private myTable As New DataTable("Freddy")
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            'find / next 'Name' - see shown event
            Static lastSearch As String = ""
            Static fndRws As List(Of DataRow)
            Static idx As Integer
            If txtbName.Text.Trim <> "" Then 'Name to search for
                If txtbName.Text <> lastSearch Then
                    'new search
                    lastSearch = txtbName.Text.Trim
                    'do the 'Name' search
                    fndRws = (From r In myTable.Rows
                              Let rw As DataRow = DirectCast(r, DataRow)
                              Where rw("Name").ToString = lastSearch Select rw).ToList
    
                    idx = 0
                End If
    
                If fndRws.Count > 0 Then
                    If idx >= fndRws.Count Then
                        idx = 0 'recycle to beginning
                    End If
                    Dim curRW As DataRow = fndRws(idx)
                    Debug.WriteLine("{0}  {1}  {2}", curRW("Name").ToString, curRW("Roll Number").ToString, curRW("Score").ToString)
                    idx += 1
                Else
                    'no rows matched
                End If
            Else
                'nothing to search for
                lastSearch = ""
            End If
        End Sub
    
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            With myTable
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(String))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
                .Rows.Add("N1", 5, "Math", "Phy", 20.7456D)
                .Rows.Add("N2", 3, "Social", "Chem", 15.5D)
                .Rows.Add("N1", 1, "Math", "Phy", 11.5D)
                .Rows.Add("N2", 3, "Social", "Math", 25.7D)
                .Rows.Add("N1", 2, "Math", "Phy", 20.9D)
                .Rows.Add("N2", 1, "Social", "Math", 18.5D)
                .Rows.Add("N1", 2, "English", "Phy", 23.5D)
                .Rows.Add("N2", 3, "Social", "Math", 25.7D)
            End With
    
        End Sub
    


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.

    Wednesday, April 18, 2018 4:07 PM
  • Sir,

    Is it possible to use the same button, because the form is too neat for more button.

    Yes it's possible to use a single button. You can keep track of the current IA Code value in a variable. When the button is clicked if it's the same as the previous search then skip the first part of my example and execute the second part, which increments the currentIndex. When a new IA code is entered it will be different than the variable and you can run the query again and display the first row.

    If you need help more help with the code logic, please let me know. This should get you started:

    Dim currentIACode As String = String.Empty 'defined at module level
    
    'in click event
    If currentIACode <> tbCode.Text Then
        'new search
        'display first row if available
        currentIACode = tbCode.Text
    Else
        'display next row if available
    End If


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 18, 2018 5:32 PM
  • Yes it's possible to use a single button. You can keep track of the current IA Code value in a variable. When the button is clicked if it's the same as the previous search then skip the first part of my example and execute the second part, which increments the currentIndex. When a new IA code is entered it will be different than the variable and you can run the query again and display the first row.

    If you need help more help with the code logic, please let me know. This should get you started:

    Dim currentIACode As String = String.Empty 'defined at module level
    
    'in click event
    If currentIACode <> tbCode.Text Then
        'new search
        'display first row if available
        currentIACode = tbCode.Text
    Else
        'display next row if available
    End If


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thank so much everyone (sir) for guide me through, in fact I am not that professional at coding at all so most of them start getting complicated every step i try.

    Thank from heart to Sir Paul. Your guide is quite simple for me. I try to figure out your code by making NEXT button ... I hope it won't bother you too much if you can help me a bit without NEXT button. (I unable to get the next row display)

    Thursday, April 19, 2018 4:06 AM
  • Yes it's possible to use a single button. You can keep track of the current IA Code value in a variable. When the button is clicked if it's the same as the previous search then skip the first part of my example and execute the second part, which increments the currentIndex. When a new IA code is entered it will be different than the variable and you can run the query again and display the first row.

    If you need help more help with the code logic, please let me know. This should get you started:

    Dim currentIACode As String = String.Empty 'defined at module level
    
    'in click event
    If currentIACode <> tbCode.Text Then
        'new search
        'display first row if available
        currentIACode = tbCode.Text
    Else
        'display next row if available
    End If


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thank so much everyone (sir) for guide me through, in fact I am not that professional at coding at all so most of them start getting complicated every step i try.

    Thank from heart to Sir Paul. Your guide is quite simple for me. I try to figure out your code by making NEXT button ... I hope it won't bother you too much if you can help me a bit without NEXT button. (I unable to get the next row display)


    I get it too work... but problem is that if i change the number/text in txtPhoneSearch (search bar) the same result still there nothing change. I am not sure where I did wrong. here is my code
     If txtPhoneSearch.TextLength = 0 Then
                MessageBox.Show(String.Format("Please Fill in Phone Number or IA Code"))
            Else
                '====
    
                Dim strExel As String = "select [Name], [Sex], [DateofBirth], [PrimaryTelephone], [2ndTelephone], [NationalIDCard], [IACode] from [Sheet1$] where [PrimaryTelephone] = '" & txtPhoneSearch.Text & "' OR [2ndTelephone] ='" & txtPhoneSearch.Text & "' OR [IACode] ='" & txtPhoneSearch.Text & "'"
    
                Dim MyCom As New System.Data.OleDb.OleDbDataAdapter(strExel, MyConnection)
    
                MyCom.Fill(dt)
                If dt.Rows.Count >= 1 Then
                    btnCheck.Visible = True
                    btnReset.Visible = True
    
                    If currentIACODE <> tbCODE.Text Then
    
                        tbNAME.Text = dt.Rows(1)("Name").ToString()
                        tbSEX.Text = dt.Rows(1)("Sex").ToString()
                        tbDOB.Text = dt.Rows(1)("DateofBirth").ToString()
                        tbP1.Text = dt.Rows(1)("PrimaryTelephone").ToString()
                        tbP2.Text = dt.Rows(1)("2ndTelephone").ToString()
                        tbIDNo.Text = dt.Rows(1)("NationalIDCard").ToString()
                        tbCODE.Text = dt.Rows(1)("IACode").ToString()
                        currentIACODE = tbCODE.Text
                    Else
                        currentINDEX = currentINDEX + 1
                        tbNAME.Text = dt.Rows(currentINDEX)("Name").ToString()
                        tbSEX.Text = dt.Rows(currentINDEX)("Sex").ToString()
                        tbDOB.Text = dt.Rows(currentINDEX)("DateofBirth").ToString()
                        tbP1.Text = dt.Rows(currentINDEX)("PrimaryTelephone").ToString()
                        tbP2.Text = dt.Rows(currentINDEX)("2ndTelephone").ToString()
                        tbIDNo.Text = dt.Rows(currentINDEX)("NationalIDCard").ToString()
                        tbCODE.Text = dt.Rows(currentINDEX)("IACode").ToString()
                    End If
                    'For Each row As DataRow In dt.Rows
                    '    tbNAME.Text = row("Name").ToString()
                    '    tbSEX.Text = row("Sex").ToString()
                    '    tbDOB.Text = row("DateofBirth").ToString()
                    '    tbP1.Text = row("PrimaryTelephone").ToString()
                    '    tbP2.Text = row("2ndTelephone").ToString()
                    '    tbIDNo.Text = row("NationalIDCard").ToString()
                    '    tbCODE.Text = row("IACode").ToString()
    
                    'Next
    
                Else
                    MsgBox("No Found")
                End If
            End If
    


    Thursday, April 19, 2018 7:16 AM
  • SoNewBie,

    This does not go because it is only one row more which works. But we don't see the method in which you located it. And therefore we see in fact nothing but there is only a lot guessing. 

    If you show in which method you use this than maybe somebody can help you and is it just a small change of your code. 

    The chalenge is to know the previous index and to know if the search attributes changed.

    Dbasnett made a piece of code which fits probably to that, although there are many other ways. 

    But at least I avoid guessing as that is advised not to do.


    Success
    Cor

    Thursday, April 19, 2018 10:28 AM
  • You don't want to re-run the query unless the tbCODE value has changed.

    If currentIACODE <> tbCODE.Text Then
       Dim strExel As String = "select [Name], [Sex], [DateofBirth], [PrimaryTelephone], [2ndTelephone], [NationalIDCard], [IACode] from [Sheet1$] where [PrimaryTelephone] = '" & txtPhoneSearch.Text & "' OR [2ndTelephone] ='" & txtPhoneSearch.Text & "' OR [IACode] ='" & txtPhoneSearch.Text & "'"
    
       Dim MyCom As New System.Data.OleDb.OleDbDataAdapter(strExel, MyConnection)
       MyCom.Fill(dt)
       If dt.Rows.Count >= 1 Then
    	btnCheck.Visible = True
    	btnReset.Visible = True
    Plus, the first row has an index of 0 and not 1.


    tbNAME.Text = dt.Rows(0)("Name").ToString()
    


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, April 19, 2018 12:42 PM
  • SoNewBie,

    This does not go because it is only one row more which works. But we don't see the method in which you located it. And therefore we see in fact nothing but there is only a lot guessing. 

    If you show in which method you use this than maybe somebody can help you and is it just a small change of your code. 

    The chalenge is to know the previous index and to know if the search attributes changed.

    Dbasnett made a piece of code which fits probably to that, although there are many other ways. 

    But at least I avoid guessing as that is advised not to do.


    Success
    Cor


    Sorry sir, I actually try to upload image but internet got cut off these few days. https://imgur.com/a/FNlgguA here is the interface of what the search show.

    please ignore next and previous button as I just try to figure out if the next button work. I actually dont use it

    Sunday, April 22, 2018 3:02 AM
  • You don't want to re-run the query unless the tbCODE value has changed.

    If currentIACODE <> tbCODE.Text Then
       Dim strExel As String = "select [Name], [Sex], [DateofBirth], [PrimaryTelephone], [2ndTelephone], [NationalIDCard], [IACode] from [Sheet1$] where [PrimaryTelephone] = '" & txtPhoneSearch.Text & "' OR [2ndTelephone] ='" & txtPhoneSearch.Text & "' OR [IACode] ='" & txtPhoneSearch.Text & "'"
    
       Dim MyCom As New System.Data.OleDb.OleDbDataAdapter(strExel, MyConnection)
       MyCom.Fill(dt)
       If dt.Rows.Count >= 1 Then
    	btnCheck.Visible = True
    	btnReset.Visible = True
    Plus, the first row has an index of 0 and not 1.


    tbNAME.Text = dt.Rows(0)("Name").ToString()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    When I put it like this .. it said
    tbNAME.Text = dt.Rows(0)("Name").ToString()
                        tbSEX.Text = dt.Rows(0)("Sex").ToString()
                        tbDOB.Text = dt.Rows(0)("DateofBirth").ToString()
                        tbP1.Text = dt.Rows(0)("PrimaryTelephone").ToString()
                        tbP2.Text = dt.Rows(0)("2ndTelephone").ToString()
                        tbIDNo.Text = dt.Rows(0)("NationalIDCard").ToString()
                        tbCODE.Text = dt.Rows(0)("IACode").ToString()
    Result not found ... I really almost break out crying because of these.

    Sunday, April 22, 2018 3:06 AM
  • Please post all of the code in the routine. I cannot troubleshoot pieces of code. If you have a MsgBox that displays "Result not found" then I need to see that code as well.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, April 22, 2018 3:18 PM
  • Please post all of the code in the routine. I cannot troubleshoot pieces of code. If you have a MsgBox that displays "Result not found" then I need to see that code as well.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    If you look at my previous code that I paste on the section above you will see that at the end of if I put msgbox for if result not found. But i test the code and even remove the msgbox but nothing appear only after i put like this

            tbNAME.Text = dt.Rows(1)("Name").ToString()

    but you told me the index suppose to be 0 not 1 ... However, I write some logic on my book and after which I put index 1 ... the search appear just fine but the problem is that even after I type in new number to search .. the same result still appear.

    :( since I just too new at coding. I think i will work around by make new form with datagridview.

    Monday, April 23, 2018 3:03 AM