CODEWITHSUNDEEP
vb.net
TimmyHoHoo
TimmyHoHoo

Reputation: 61

Search records in database for those that match what a user enters

I'm trying to write a function that searches records in my database for the object of those that match the SearchCriteria. The functions parameters look like so:

RetrieveObject(SearchCriteria) As String (SearchCritera is a string aswell)

Right now for testing purposes I am using a console app that asks the user to search by first name.

Console.Writeline("Search by first name: ") Dim firstName = Console.Readline()

I then use my function: RetrieveObject(firstName)

I want my function to show all the values (lastname, titlename, state, zip) for that particular person that was passed to the RetrieveObject function.

The problem I am having is that I cannot seem to understand how I'm going to match what the user enters with the value in the database.

If anyone could just put me in the right direction to help me understand how to accomplish this, I'd be so grateful!

Here is my code so far:

Private Function RetrieveObject(SearchCriteria As String) As String

    Dim cn As OdbcConnection = New OdbcConnection(myCon)

    Dim myQuery = "SELECT * FROM Applicant WHERE [strFirstName] LIKE '%son'"

    Using com As New OdbcCommand(myQuery)

        cn.Open()
        com.Connection = cn
        com.CommandType = CommandType.Text
        com.CommandText = myQuery
        com.Parameters.AddWithValue("@strFirstName", SearchCriteria)

        Try
            com.ExecuteReader()
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        End Try

    End Using

    Return SearchCriteria

End Function

Thanks again!

Upvotes: 2

Views: 387

Answers (2)

Steve
Steve

Reputation: 216353

To create a WHERE condition you need to provide (at the most basic level) three informations to the database engine. The first bit is the column name that you want to search for, the second piece is the operator that you want to use for matching records, and finally the value to search for

SELECT * FROM table WHERE FieldName = 'AStringValue'

Of course we could have a lot of variations with operators and field datatype but this answer is limited to your actual situation.

It is important to note that your query could return more than one record (in particular if you add wildcards operators as LIKE, so you cannot simply return a single value with this query, but instead you return a DataTable where you can loop over the Rows collection to see all the records returned by the query

So your code should be changed to

Private Function RetrieveObject(SearchCriteria As String) As DataTable
    Dim myQuery = "SELECT * FROM Applicant WHERE [strFirstName] LIKE ?"
    Try
        Using cn = New OdbcConnection(myCon)
        Using da = new OdbcDataAdapter(myQuery, cn)
            da.SelectCommand.Parameters.Add("?", OdbcType.VarChar).Value = SearchCriteria
            Dim dt = new DataTable()
            da.Fill(dt)
            return dt
         End Using
         End Using
     Catch ex As Exception
         MsgBox(ex.Message.ToString())
         return Nothing
     End Try
End Function

Now you could call this method with something like 

Dim table = RetrieveObject("John%")
if table IsNot Nothing Then
    For Each row in table.Rows
       Console.WriteLine(row["strFirstName"].ToString())
    Next
End If

If you really need to return a json string with all the FirstName matched then you could add this to the last lines of the code above

.....
da.Fill(dt)
Dim names = dt.AsEnumerable().Select(Function(x) x.Field(Of String)("strFirstName")).ToArray()
string output = JsonConvert.SerializeObject(names);
return output;

And of course change again the return value to be a string.

Upvotes: 2

Tharif
Tharif

Reputation: 13971

You can also pass your search criteria into function which returns dataset as shown below , one more thing ,you can use the function in textbox textchange event in forms

Also while search include LIKE as LIKE '%" & @strFirstName & "%' which can help you narrow search results if needed

Public Function Search(ByVal Criteria As String) As DataSet
        Try
            Dim ds As New DataSet
            Using sqlCon As New SqlConnection(connStr)
            stQuery="SELECT * FROM Applicant WHERE [strFirstName] 
                     LIKE '%" & @strFirstName & "%'"
                Dim sqlCmd As New SqlCommand(stQuery, sqlCon)
                Dim sqlAda As New SqlDataAdapter(sqlCmd)
                sqlCmd.CommandType = CommandType.Text
                sqlCmd .Parameters.AddWithValue("@strFirstName", Criteria )
                sqlAda.Fill(ds)
            End Using
            Return ds
        Catch ex As Exception
           MsgBox(ex.Message.ToString())
        End Try
    End Function

Upvotes: 0

Related Questions