CODEWITHSUNDEEP
asp.netsqlsearchselectgridview
user1698144
user1698144

Reputation: 764

Select Command with Multiple LIKEs

Functionality

User inputs text into textboxA. Search the database records for First Names AND Last Names corresponding to the user's input.

The Problem:

It appears the results I am getting are only searching "First Name" field and not BOTH the First Name and Last Name Fields

Example:

*A search for "Mike" returns:*

Mike Smith

Mike Jones

A search for "Jones":

Mike Jones is not returned

CODE:

Protected Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click

    If txtSearch.Text = "" Then

    Else

        Dim ConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("oakfratintdbConnectionString").ConnectionString
        Dim Conn As New SqlConnection(ConnString)
        Dim searchCMD As New SqlCommand("SELECT [FirstName], [LastName], [MidInitName], [NameSuffix], [NamePrefix], [DOB], [DOD], [BurialDate], [GeoDirection], [Space], [Lot], [Row], [IntermentSpec], [AddtlInfo] FROM [OFCInterments] WHERE ([FirstName] LIKE @FirstName)", Conn)
        Dim searchDT As DataTable = GetData(searchCMD)
        GridView1.DataSource = searchDT
        GridView1.DataBind()
    End If

End Sub

Code Explanation:

txtSearch is the user's search input

Upvotes: 2

Views: 313

Answers (2)

Dr. Wily's Apprentice
Dr. Wily's Apprentice

Reputation: 10280

Below is the SQL query with the WHERE clause adjusted to give you the flexibility to search by only first name, only last name, or both.

SELECT [FirstName], [LastName], [MidInitName], [NameSuffix], [NamePrefix], [DOB], [DOD], [BurialDate], [GeoDirection], [Space], [Lot], [Row], [IntermentSpec], [AddtlInfo] FROM [OFCInterments] WHERE
    (@FirstName IS NULL OR [FirstName] LIKE @FirstName)
    AND (@LastName IS NULL OR [LastName] LIKE @LastName)

With this query I'm making the assumption that if the user searches for FirstName: "Mike" and LastName: "Jones" that the search results should only contain "Mike Jones", not "Mike Smith", "Mike Jones", and "Paul Jones".

Note that if you use this approach, you will need to make sure not to populate your parameters with an empty string in the case where the user did not supply a value for a criteria. Alternatively, you could check for empty strings in the query in addition to null.

Upvotes: 1

Nikshep
Nikshep

Reputation: 2115

Change 

SELECT [FirstName], [LastName], [MidInitName], [NameSuffix], [NamePrefix], [DOB], 
[DOD], [BurialDate], [GeoDirection], [Space], [Lot], [Row], [IntermentSpec], 
[AddtlInfo] FROM [OFCInterments] WHERE ([FirstName] LIKE @FirstName)

to 

SELECT [FirstName], [LastName], [MidInitName], [NameSuffix], [NamePrefix], [DOB], 
[DOD], [BurialDate], [GeoDirection], [Space], [Lot], [Row], [IntermentSpec], 
[AddtlInfo] FROM [OFCInterments] WHERE ([FirstName] LIKE @FirstName or 
LastName like @LastName)

Upvotes: 3

Related Questions