How to create a function for search operation

Question

Nebil 5 Junior Poster

11 Years Ago

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

vb.net

2 Contributors
5 Replies
195 Views
1 Week Discussion Span
Latest Post 10 Years Ago Latest Post by Begginnerdev

All 5 Replies

Begginnerdev 256 Junior Poster

11 Years Ago

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

Edited 11 Years Ago by Begginnerdev

Begginnerdev 256 Junior Poster

11 Years Ago

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

Edited 11 Years Ago by Begginnerdev

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Nebil 5 Junior Poster · Answer 1 · 2013-04-20T08:42:14+00:00

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)

Nebil 5 Junior Poster · Answer 2 · 2013-04-29T12:35:36+00:00

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 256 Junior Poster · Answer 3 · 2013-04-29T18:49:43+00:00

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"

How to create a function for search operation

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers