How to create a function for search operation

[Nebil]

it's me again
i had a code that searches a record based on user option.And i had the idea of creating a single function that
accepts a query statement and execute and return it to the caller but i can't come up with anything(not make the code bulky).i have 3 d/t search functions with the same line of code,the only thing that is d/t is the string statement.
i hope you will get my idea

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If ComboBox1.SelectedItem = "" Then
            MsgBox("Please choose what you want to search", vbInformation, "Bridge Asset Management")
            ComboBox1.Focus()

        ElseIf ComboBox1.SelectedItem = "Bridge Number" Then
            If TextBox1.Text <> "" Then
                search()
            Else
                MsgBox("Please enter the bridge number to search", vbInformation, "Bridge Asset Management")
            End If
        ElseIf ComboBox1.SelectedItem = "Bridge Name" Then
            If TextBox1.Text <> "" Then
                search1()
            Else
                MsgBox("Please enter the bridge name to search", vbInformation, "Bridge Asset Management")
            End If
        ElseIf ComboBox1.SelectedItem = "Section" Then
            If ComboBox2.SelectedItem <> "" Then
                search2()
            Else
                MsgBox("Please choose the section to search", vbInformation, "Bridge Asset Management")
            End If
        ElseIf ComboBox1.SelectedItem = "Road Segment" Then
            If ComboBox3.SelectedItem <> "" Then
                search3()
            Else
                MsgBox("Please choose the section to search", vbInformation, "Bridge Asset Management")

            End If
        End If
    End Sub

 Public Sub search()
        Dim conn As OleDbConnection = GetDbConnection()
        bridgenumber = TextBox1.Text.ToString()
        ' Select records.
        comm = New OleDbCommand("SELECT * FROM AdigratT WHERE [Bridge Number]=@bridgenumber", conn)
        comm.Parameters.AddWithValue("@bridgenumber", bridgenumber)
        Dim data_reader As OleDbDataReader = comm.ExecuteReader()
        If data_reader.HasRows = True Then
            ListView1.Items.Clear()
            Do While data_reader.Read()
                Dim new_item As New ListViewItem(data_reader.Item("Bridge Number").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Name").ToString)
                new_item.SubItems.Add(data_reader.Item("District Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Section Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Road Segment Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Type").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Span").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Condition").ToString)
                new_item.SubItems.Add(data_reader.Item("Acquisition Year").ToString)
                new_item.SubItems.Add(data_reader.Item("Evaluation Year").ToString)
                new_item.SubItems.Add(data_reader.Item("End of Design Life Year").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Asset Value").ToString)
                ListView1.Items.Add(new_item)
            Loop
        Else
            MsgBox("There is no record in the database", vbCritical, "Bridge Asset Management")
            ListView1.Items.Clear()
        End If
        ' Close the connection.
        data_reader.Close()
        data_reader = Nothing
        comm.Dispose()
        comm = Nothing
        conn.Close()
        conn.Dispose()
        conn = Nothing
    End Sub

[Begginnerdev]

Pass your query and parameters into your Search Sub:

Dim Query As String = "SELECT * FROM tblTest WHERE [My Column]=@Value"
Dim Parameters As New Dictionary(Of String, String)



'Fill the dictionary
Parameters.Add("@Value", "ValueToSearchFor")





'Call the Sub
Search(Query, Parameters)



'Sub Declaration


Private Sub Search(ByVal sQuery As String, ByVal dctParameters As Dictionary(Of String, String))
    Try
        Dim conn As OleDbConnection = GetDbConnection()
        bridgenumber = TextBox1.Text.ToString()
        ' Select records.

        'Pass Query in Here
        comm = New OleDbCommand(sQuery, conn)

        For Each k As KeyValuePair(Of String, String) In dctParameters
            comm.Parameters.AddWithValue(k.Key, k.Value)
        Next

        Dim data_reader As OleDbDataReader = comm.ExecuteReader()

        If data_reader.HasRows = True Then
            ListView1.Items.Clear()
            Do While data_reader.Read()
                Dim new_item As New ListViewItem(data_reader.Item("Bridge Number").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Name").ToString)
                new_item.SubItems.Add(data_reader.Item("District Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Section Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Road Segment Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Type").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Span").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Condition").ToString)
                new_item.SubItems.Add(data_reader.Item("Acquisition Year").ToString)
                new_item.SubItems.Add(data_reader.Item("Evaluation Year").ToString)
                new_item.SubItems.Add(data_reader.Item("End of Design Life Year").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Asset Value").ToString)
                ListView1.Items.Add(new_item)
            Loop
        Else
            MsgBox("There is no record in the database", vbCritical, "Bridge Asset Management")
            ListView1.Items.Clear()
        End If
        ' Close the connection.
        data_reader.Close()
        data_reader = Nothing
        comm.Dispose()
        comm = Nothing
        conn.Close()
        conn.Dispose()
        conn = Nothing
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

[Nebil]

Hey Bigginerdev,after i posted this article i tried to come up with this code and it works well.take a look and if it has any effects comment on it.

Public Sub search(ByVal str)
        conn = GetDbConnection()
        comm = New OleDbCommand(str, conn)
        Dim data_reader As OleDbDataReader = comm.ExecuteReader()
        If data_reader.HasRows = True Then
            ListView1.Items.Clear()
            Do While data_reader.Read()
                Dim new_item As New ListViewItem(data_reader.Item("Bridge Number").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Name").ToString)
                new_item.SubItems.Add(data_reader.Item("District Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Section Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Road Segment Name").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Type").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Span").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Condition").ToString)
                new_item.SubItems.Add(data_reader.Item("Acquisition Year").ToString)
                new_item.SubItems.Add(data_reader.Item("Evaluation Year").ToString)
                new_item.SubItems.Add(data_reader.Item("End of Design Life Year").ToString)
                new_item.SubItems.Add(data_reader.Item("Bridge Asset Value").ToString)
                ListView1.Items.Add(new_item)
            Loop
        Else
            MsgBox("There is no record in the database", vbCritical, "Bridge Asset Management")
            ListView1.Items.Clear()
        End If
        ' Close the connection.
        data_reader.Close()
        data_reader = Nothing
        comm.Dispose()
        comm = Nothing
        conn.Close()
    End Sub
    //under the click event,here's a line of code that i managed to call the search function
    If ComboBox1.SelectedItem = "Bridge Name" Then
            If TextBox1.Text <> "" Then
                bridgename = TextBox1.Text
                Dim str1 As String = "SELECT * FROM AdigratT WHERE [Bridge Name]='" & bridgename & "'"
                search(str1)

[Begginnerdev]

That works well. The problem with this statement:

Dim str1 As String = "SELECT * FROM AdigratT WHERE [Bridge Name]='" & bridgename & "'"

Is leaving you open for SQL Injection attacks.

It is best to use parameterized queries:

  Dim str1 As String = "SELECT * FROM AdigratT WHERE [Bridge Name] = @Bridge"
  str1 = str1.Replace("@Bridge", "Test Bridge")

Try passing in the command:

If ComboBox1.SelectedItem = "Bridge Name" Then
    If TextBox1.Text <> "" Then
        Dim conn As New OleDBConnection("SELECT * FROM AdigratT WHERE [Bridge Name] = @Bridge", GetDBConnection())
        conn.Parameters.AddWithValue("@Bridge","Test Bridge")
        Search(conn)
        conn.Connection.Close()
        conn.Dispose()
    End If
End If

[Nebil]

hi Beginnerdev,
connection class doesn't have a parameter property.and also i tried to use the command class but there occurs an exception saying you can't convert a command argument into string.

[Begginnerdev]

Sorry for total blunder, but it conn is supposed to OleDBConnection.

As for the command argument, you can also set a value:

conn.Parameters("@Bridge").Value = "MyValue"