Solved

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

Posted on 2011-02-13
7
736 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
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:murbro
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:murbro
ID: 34917261
thanks very much
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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