none
Recursive search algorithm for dataset RRS feed

  • Question

  • Hi everyone,

    I am trying to create a function that recursively searches a specified dataset.

    The code I have is as follows:

    Public Function Search(ByVal input As String, n As Integer, x As Integer)
    		Try
    			If n <= testDataSet.Customer.Rows.Count - 1 Then
    				If TestDataSet.Customer.Rows(n).Item(x).ToString = input Then
    					Return n
    					Search(input, n + 1, 0)
    				Else
    					If Not x = TestDataSet.Customer.Columns.Count - 1 Then
    						Search(input, n, x + 1)
    					Else
    						Search(input, n + 1, 0)
    					End If
    				End If
    			Else
    				Return MsgBox("Not found")
    			End If
    		Catch
    		End Try
    
    	End Function
    The function only returns the index of the first record that contains the input, I'm assuming that this is due to "Return n" exiting the function? Can someone confirm this and potentially offer me an alternative solution? Thanks.

    Tuesday, April 9, 2019 8:01 PM

All replies

  • Hi

    Obviously, I don't have the data that you are wotking on, so here is an old Project I have which may illustrate how to Search with Row/Column indexes on a DataTable (I use MyTable which would equate to your testDataSet.Customer.

    If you want to try this stand alone example, you would need to add various controls in the Designer, and Copy/Replace default Form1 code with  this code.

    I added an extra Function called Search,to fit in with your question, and is activated via Button4 in the example (see comments)

    *

    Although it probably isn't what you want, the main part of this example is geared toward dynamic matching (as user enters into Textbox1), but the extra function just collects the data to a List on User click of Button4. To see the contents (which would match the already created ListBox contents), just put a BreakPoint on the End Sub of Button4 click event handler and check the values.

    Image

    Code

    ' This example needs a Form1 with
    ' a blank DataGridView1, TextBox1,
    ' Button1, Button2, Button3, Button4
    ' 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
    
            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
    
            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 TO PROVIDE A LIST
        ' FOR FORUM ANSWER
        ' =========================
        Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            Dim lst As New List(Of Pr)
    
            lst = Search(TextBox1.Text)
        End Sub
        Public Function Search(input As String) As List(Of Pr)
            Dim lst As New List(Of Pr)
            For r As Integer = 0 To myTable.Rows.Count - 1
                For c As Integer = 0 To myTable.Columns.Count - 1
                    If myTable(r).Item(c).ToString.ToLower = input.ToLower Then
                        lst.Add(New Pr With {.Row = r, .Column = c})
                    End If
                Next
            Next
            Return lst
        End Function
        ' =========================
    
    
        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

    Tuesday, April 9, 2019 9:03 PM
  • Hi,

    I think you can use the traversal method to search.

    Imports System.Text
    
    Public Class Form1
        Dim dt As New DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With dt
                .Columns.Add("Col1")
                .Columns.Add("Col2")
                .Columns.Add("Col3")
                .Columns.Add("Col4")
                .Rows.Add(New String() {"2", "33", "42", "16"})
                .Rows.Add(New String() {"51", "13", "18", "16"})
                .Rows.Add(New String() {"40", "3", "9", "36"})
                .Rows.Add(New String() {"24", "1", "12", "6"})
                .Rows.Add(New String() {"12", "3", "12", "16"})
            End With
            MsgBox(Search("16", 0, 0))
        End Sub
        Public Function Search(ByVal input As String, n As Integer, x As Integer)
            If n < dt.Rows.Count - 1 Then
                Dim str As New StringBuilder
                For i = 0 To dt.Rows.Count - 1
                    For j = 0 To dt.Columns.Count - 1
                        If input = dt.Rows(i)(j).ToString Then
                            str.Append(i & " ")
                            Exit For
                        End If
                    Next
                Next
                Return str.ToString
            Else
                Return "Not found"
    
            End If
    
        End Function
    End Class
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 10, 2019 2:21 AM
  • Consider Yield instead of Return: https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/yield-statement. The function will return an IEnumerable(Of Integer) that can be used in a loop.

    Maybe also put something inside the Catch to detect the problems.

    Wednesday, April 10, 2019 5:30 AM
  • 		Catch
    		End Try
    
    	End Function
    The function only returns the index of the first record that contains the input, I'm assuming that this is due to "Return n" exiting the function? .

    I guess the chance is higher because your modern version of Resume on Error in your code. Catch End Try


    Success
    Cor


    Wednesday, April 10, 2019 5:23 PM
  • Hi everyone,

    I am trying to create a function that recursively searches a specified dataset.

    The code I have is as follows:

    Public Function Search(ByVal input As String, n As Integer, x As Integer)
    		Try
    			If n <= testDataSet.Customer.Rows.Count - 1 Then
    				If TestDataSet.Customer.Rows(n).Item(x).ToString = input Then
    					Return n
    					Search(input, n + 1, 0)
    				Else
    					If Not x = TestDataSet.Customer.Columns.Count - 1 Then
    						Search(input, n, x + 1)
    					Else
    						Search(input, n + 1, 0)
    					End If
    				End If
    			Else
    				Return MsgBox("Not found")
    			End If
    		Catch
    		End Try
    
    	End Function
    The function only returns the index of the first record that contains the input, I'm assuming that this is due to "Return n" exiting the function? Can someone confirm this and potentially offer me an alternative solution? Thanks.

    No point in being recursive.  Change a few names and this should work.  Returns a list of row indices.

        Public Function Search(input As String, rwStrt As Integer, colStrt As Integer) As List(Of Integer)
            Dim rv As New List(Of Integer)
            If rwStrt < myTable.Rows.Count Then
                For rw As Integer = rwStrt To myTable.Rows.Count - 1
                    For col As Integer = colStrt To myTable.Columns.Count - 1
                        If myTable(rw)(col).ToString = input Then
                            rv.Add(rw)
                            Exit For
                        End If
                    Next
                Next
            End If
            Return rv
        End Function
    

    To test as is

        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
            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(Decimal))
                .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
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim rws As List(Of Integer) = Search("Math", 0, 0)
        End Sub
    
        Dim myTable As New DataTable("Test")
    


    Search Documentation

    SerialPort Info

    Multics - An OS ahead of its time.

     "Those who use Application.DoEvents have no idea what it does

        and those who know what it does never use it."    former MSDN User JohnWein

    Wednesday, April 10, 2019 5:53 PM