ASP.NET SelectCommand Syntax Question

I'm trying to add a simple keywords search to my ojbect datasource and am having trouble with the syntax.  I basically want to say 'select * from xyz where Description like '%The keywords from textbox%'.  How do I represent the "%" character in my SelectCommand?

It works fine when I put: Description = @Keywords, but I need the "%"s.
I tried the following which didn't work:
"Description like '%@Keywords%'"

Here's my complete SelectCommand:
SelectCommand="SELECT [RecID], [TrackTitle], [Composer], [Filename], [Popularity], [Time], [Description], [Category], [SubCategory], [Keywords], [BPM], [CDTitle], [IsVocal] FROM [metadata] WHERE Composer like @Artist AND Status = @Status AND Description like @Keywords"
JT_SIROAsked:
Who is Participating?
 
Göran AnderssonConnect With a Mentor Commented:
...right, and the wildcards. Concatenate the strings:

  ... AND [Description] like '%' + @Keywords + '%'
0
 
Göran AnderssonCommented:
Description is a reserved keyword. Put brackets around it:

  ... AND [Description] like @Keywords
0
 
guru_samiConnect With a Mentor Commented:
something like this:

 WHERE ([Keywords] LIKE '%' + @Keywords+ '%'

Use the SqlDataSource wizard and there if you use Where to add that, it should generate SelectCommand for you.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
guru_samiCommented:
oops my bad...forgot to refresh...
0
 
JT_SIROAuthor Commented:
Great, that worked!  Now how would account for the user leaving the keywords field blank.  With this syntax "AND [Description] like '%' + @Keywords + '%'", it requires that there be something in Keywords.  How would I add some "if not null" clause?
0
 
JT_SIROAuthor Commented:
FYI - I certainly know how to do this in code behind, but I'd like to utilize the object datasource.  I'm using it to save time, and it's wasting me time....  Uggg.  
0
 
Göran AnderssonCommented:
If the @Keywords parameter is an empty string, you can just leave it like it is, and it will produce the string '%%' which matches anything.

If the @Keywords parameter can be null, use the isnull function:

  AND [Description] like '%' + isnull(@Keywords, '') + '%'
0
 
cubaman_24Connect With a Mentor Commented:
WHERE ([Keywords] LIKE '%' + @Keywords+ '%' + 'OR @Keywords IS NULL '
0
 
kovilpattiBaluCommented:
try this..
store @keywords in a variable, and than assign the variable in the selectcommand

store in a variable..
string searchString = "%" + @keywords + "%"
assign it..
"AND Description like '" + searchString + "'"
0
 
JT_SIROAuthor Commented:
Thanks everyone!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.