Solved

ASP.NET SelectCommand Syntax Question

Posted on 2010-09-20
10
352 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

759 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

17 Experts available now in Live!

Get 1:1 Help Now