Solved

ASP.net Table Adapter SQL LIKE STATEMENT - unknown number criteria

Posted on 2011-02-13
7
732 Views
Last Modified: 2012-05-11
Hi

I have a table adapter in ASP.net 4 that uses the code in the code selection below.
The number of parameters passed in can vary between 2 and 20 so how do I set up
a SQL statement to handle varying numbers of parameters (not just 2 as shown below)
SELECT        colDate, colTime, colDetail
FROM            Table1
WHERE        (colDetail LIKE @Parameter1 + '%') OR
                         (colDetail LIKE @Parameter2 + '%')

Open in new window

0
Comment
Question by:murbro
7 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
You need dynamic sql. How are you getting the parameters?
0
 
LVL 19

Accepted Solution

by:
Manoj Patil earned 250 total points
Comment Utility
Hi you can write your query in for loop. For example

for (i=0; i<ParameterCount; i++)
{
      if(ParameterCount=1)
      {
         SELECT        colDate, colTime, colDetail  FROM Table1 WHERE (colDetail LIKE @Parameter1 + '%')
      }
      else if(ParameterCount=2)
      {
                 SELECT        colDate, colTime, colDetail FROM Table1 WHERE (colDetail LIKE @Parameter1 + '%') OR  (colDetail LIKE @Parameter2 + '%')
       }
and so on ...
     
}


0
 
LVL 19

Expert Comment

by:Manoj Patil
Comment Utility
To get ParameterCount you can create an array of user input and check the count.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:murbro
Comment Utility
Hi. How would I create that array. Thanks
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
Comment Utility
you could do it like this

always pass the parameters

adjust the nullif test for the data type if not character...
if empty pass a space or empty string.
SELECT        colDate, colTime, colDetail
FROM            Table1 as A

WHERE       Exists (select 'Y'
                      from (select distinct test
                             from (select @Parameter1 + '%' as Test
                                   union all select nullif(@parameter2,'') +'%'
                                   union all select nullif(@parameter3,'') +'%'
                                   union all select nullif(@parameter4,'') +'%'
                                   union all select nullif(@parameter5,'') +'%'
                                   union all select nullif(@parameter6,'') +'%'
                                   union all select nullif(@parameter7,'') +'%'
                                   union all select nullif(@parameter8,'') +'%'
                                   union all select nullif(@parameter9,'') +'%'
                                   union all select nullif(@parameter10,'')+'%'
                                   union all select nullif(@parameter11,'') +'%'
                                   union all select nullif(@parameter12,'') +'%'
                                   union all select nullif(@parameter13,'') +'%'
                                   union all select nullif(@parameter14,'') +'%'
                                   union all select nullif(@parameter15,'') +'%'
                                   union all select nullif(@parameter16,'') +'%'
                                   union all select nullif(@parameter17,'') +'%'
                                   union all select nullif(@parameter18,'') +'%'
                                   union all select nullif(@parameter19,'') +'%'
                                   union all select nullif(@parameter20,'')+'%'
                                  ) as x
                             where x.test is not null
                          ) as y
                     Where a.coldetail like y.test
                   )

Open in new window

0
 
LVL 19

Expert Comment

by:Manoj Patil
Comment Utility
When user add input or for example if he compare products , when he click on product, get the product ID and store it in array  
0
 

Author Closing Comment

by:murbro
Comment Utility
thanks very much
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

13 Experts available now in Live!

Get 1:1 Help Now