Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-13
7
Medium Priority
?
744 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 1000 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

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

Assisted Solution

by:Lowfatspread
Lowfatspread earned 1000 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

Technology Partners: 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!

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

730 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