Solved

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

Posted on 2011-02-13
7
742 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 41

Expert Comment

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

Accepted Solution

by:
Manoj Patil earned 250 total points
ID: 34886019
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
ID: 34886074
To get ParameterCount you can create an array of user input and check the count.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:Murray Brown
ID: 34886196
Hi. How would I create that array. Thanks
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 34886389
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
ID: 34886660
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:Murray Brown
ID: 34917261
thanks very much
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 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