Solved

ASP.NET SelectCommand Syntax Question

Posted on 2010-09-20
10
353 Views
Last Modified: 2013-11-07
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"
0
Comment
Question by:JT_SIRO
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 33721359
Description is a reserved keyword. Put brackets around it:

  ... AND [Description] like @Keywords
0
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 42 total points
ID: 33721370
...right, and the wildcards. Concatenate the strings:

  ... AND [Description] like '%' + @Keywords + '%'
0
 
LVL 41

Assisted Solution

by:guru_sami
guru_sami earned 42 total points
ID: 33721399
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
 
LVL 41

Expert Comment

by:guru_sami
ID: 33721404
oops my bad...forgot to refresh...
0
 

Author Comment

by:JT_SIRO
ID: 33721501
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:JT_SIRO
ID: 33721504
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
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 33724661
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
 
LVL 8

Assisted Solution

by:cubaman_24
cubaman_24 earned 41 total points
ID: 33724710
WHERE ([Keywords] LIKE '%' + @Keywords+ '%' + 'OR @Keywords IS NULL '
0
 
LVL 7

Expert Comment

by:kovilpattiBalu
ID: 33725157
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
 

Author Comment

by:JT_SIRO
ID: 33727587
Thanks everyone!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
A short film showing how OnPage and Connectwise integration works.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now