Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 748
  • Last Modified:

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

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
Murray Brown
Asked:
Murray Brown
2 Solutions
 
SharathData EngineerCommented:
You need dynamic sql. How are you getting the parameters?
0
 
Manoj PatilSr. Software EngineerCommented:
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
 
Manoj PatilSr. Software EngineerCommented:
To get ParameterCount you can create an array of user input and check the count.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. How would I create that array. Thanks
0
 
LowfatspreadCommented:
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
 
Manoj PatilSr. Software EngineerCommented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now