CODEWITHSUNDEEP
sqlvb.netvisual-studio-2010string-matchingsql-like
Mark
Mark

Reputation: 197

Search database for substring based on user input using SQL

I am doing a uni project where I need to search for a user from a database in Visual Studio 2010, based on their FirstName, SurName, EmailAddress or SkillSet using a SQL command in vb.net.

So from the start... The use registers and includes a skill set that is a single textbox that they type in the likes of "Java", "C#", etc.

I can search for the everything fine, apart from the SkillSet if the user has more than one skill.

I want to search for anything using "LIKE" in the SQL command.

Here's what I have at the minute for searching for a user:

SQL command to select the details I want

Dim command As New SqlCommand("SELECT [FirstName], [SurName], [EmailAddress], [SkillSet], [UserID] FROM [Users] WHERE ([FirstName] = @FirstName OR [SurName] = @SurName OR [EmailAddress] = @EmailAddress OR [SkillSet] LIKE '%' @SkillSet '%')", connection)

Creating the Paramaters

    Dim firstnameParam As New SqlParameter("@FirstName", Me.UserSearchTextBox.Text)
    Dim surnameParam As New SqlParameter("@SurName", Me.UserSearchTextBox.Text)
    Dim emailParam As New SqlParameter("@EmailAddress", Me.UserSearchTextBox.Text)
    Dim skillSetParam As New SqlParameter("@SkillSet", Me.UserSearchTextBox.Text)

    command.Parameters.Add(firstnameParam)
    command.Parameters.Add(surnameParam)
    command.Parameters.Add(emailParam)
    command.Parameters.Add(skillSetParam)`

Upvotes: 2

Views: 2956

Answers (3)

Mark
Mark

Reputation: 197

Got it working now. I was missing another set of brackets. Now I can retrieve results if only a few characters are inserted by the user. Thanks for your help guys!!

Dim command As New SqlCommand("SELECT [FirstName], [SurName], [EmailAddress], [UserID], [SkillSet] FROM [Users] WHERE ([FirstName] = @FirstName OR [SurName] = @SurName OR [EmailAddress] = @EmailAddress OR [SkillSet] LIKE '%@SkillSet%' )", connection)

Upvotes: 0

A. Gilfrin
A. Gilfrin

Reputation: 302

Rather than LIKE you would want to use IN 

Dim command As New SqlCommand("SELECT [FirstName], [SurName], [EmailAddress], [SkillSet], [UserID] FROM [Users] WHERE ([FirstName] = @FirstName OR [SurName] = @SurName OR [EmailAddress] = @EmailAddress OR [SkillSet] IN (@SkillSet)", connection)

The SkillSet would need to be passed in a format such as 

'SQL','PHP','JAVA'

I don't know Vb.net well enough to advise on formatting the user input in to the string needed.

Upvotes: 1

RemarkLima
RemarkLima

Reputation: 12037

A very quick Google reveals the following answers on this very site:

Use of SqlParameter in SQL LIKE clause not working

Howto? Parameters and LIKE statement SQL

So quite simply you need to use & (as you're in VB.NET - This will be + in C#.NET) to concatenate the string '%' with the parameter, like so:

Dim command As New SqlCommand("SELECT [FirstName], [SurName], [EmailAddress], [SkillSet], [UserID] FROM [Users] WHERE ([FirstName] = @FirstName OR [SurName] = @SurName OR [EmailAddress] = @EmailAddress OR [SkillSet] LIKE '%' & @SkillSet & '%')", connection)

or

Dim skillSetParam As New SqlParameter("@SkillSet", "%" & Me.UserSearchTextBox.Text & "%")

And remove the '%' from the query would yield the same results.

Upvotes: 0

Related Questions