asked on

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"

Göran Andersson

Description is a reserved keyword. Put brackets around it:

... AND [Description] like @Keywords

ASKER CERTIFIED SOLUTION

Göran Andersson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

guru_sami

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

guru_sami

oops my bad...forgot to refresh...

JT_SIRO

ASKER

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?

JT_SIRO

ASKER

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.

Göran Andersson

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, '') + '%'

SOLUTION

cubaman_24

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kovilpattiBalu

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 + "'"

JT_SIRO

ASKER

Thanks everyone!