Last post May 01, 2011 09:06 AM by VincentITA
Mar 07, 2011 06:26 AM|VincentITA|LINK
I should perform a Query like this
Select * from produtcts where model like @MODEL
Now if model is selected from combobox, it should be
Select * from produtcts where model like '%MODEL1%'
while, if selected chooise is null, it should be like each element in my combobox.
how should i do this (in SqlDataSource)
Mar 07, 2011 06:53 AM|MetalAsp.Net|LINK
Set your SQL to be this: Select * from produtcts where model like '%' + @MODEL + '%'.
Now if the user doesn't select model, you can set the parameter to empty string, so in essence, model is ignored.
Mar 07, 2011 10:16 AM|limno|LINK
Select * from produtcts where (model like '%'+@MODEL'%' OR @MODEL IS NULL)
In your SqlDataSource, you need to set this property to false:
Mar 07, 2011 03:17 PM|Naom|LINK
I suggest to either use MetalASP suggestion or use two separate paths (and use SP).
IF @Model IS NULL
select ... from ...
select .. from .. where Model like '%' + @Model + '%'
Mar 16, 2011 04:14 PM|VincentITA|LINK
And how should i perform a query in which number of OR condition is variable?
Mar 16, 2011 04:34 PM|Naom|LINK
You can create your query dynamically (either in your .NET code or in the T-SQL), but I think you need to provide a bigger picture of your problem.
Please tell us what exactly you're trying to achieve, what do you have in your ASP.NET page and how you're trying to solve it now and we may suggest solutions for the problem.
Also, please tell the version of SQL Server you're using/targetting and your .NET version.
Apr 19, 2011 05:31 AM|VincentITA|LINK
.Net framework 4.0
SqlServer 2005 Standard
What i mean is this:
if (combobox1.SelectedValue != "ALL")
SqlDataSource.Parameters.defaultValue = combobox1.SelectedValue
I have to search using a LIKE operator on all combobox contents
Apr 19, 2011 09:51 AM|limno|LINK
Please post your combobox1 markup and your SqlDataSource code. Thanks.
Apr 19, 2011 10:00 AM|VincentITA|LINK
Apr 19, 2011 10:30 AM|limno|LINK
You need a ControlParameter instead of the Sessionparameter to link your combobox.
You do need to show the code of the combobox to have your issue soved. Thanks.
Apr 19, 2011 10:49 AM|VincentITA|LINK
Here it is!
Apr 19, 2011 10:57 AM|limno|LINK
Do you really have a value ALL in your data column for the dropdownlist?
Apr 19, 2011 11:01 AM|VincentITA|LINK
Apr 19, 2011 11:53 AM|limno|LINK
Have you tried the code i posted early?
You need to modify this line to set NULL to your DropDownList ALL item:
WHERE clause: where (model like
@MODEL IS NULL)
you choose the ALL item, the parameter is NULL the search condition will be true all the time and it will return all records from your table base on this parameter.
Apr 19, 2011 04:32 PM|VincentITA|LINK
Thank you for your reply, but this solution is not suitable for my purpose.
I will try to explan better all situation to give a you a full understading.
I have a Master-Detail table made for researches.
In detail, i have
End user clicks on Type1 element into combobox, and can select a SubType.
If no subtype selected, i have to perform query to search in all Subtypes of selected Type.
Anyway, searches have to be performed only using subtypes, since field on which i have to search has inforamtion only about subtypes.
In this way, following your solutions, a NULL value will return all rows and not only the needed one.
This is not a mine database, i'm only working on!!
Apr 19, 2011 04:47 PM|limno|LINK
The sample is to illustrate the technique for how to handle ONE parameter. If you have more than one parameter you can choose from doing the same thing or not.
In your case, you need something like this:
...WHERE clause: where (subTypel like
@subType IS NULL) AND
Hope it helps you to understand this issue.
Apr 20, 2011 01:58 AM|VincentITA|LINK
The problem is that i can search only using subtypes: i have no type data field, it's visible only on website and can't add it.
I whink have to build a dynamic sql query.
Apr 21, 2011 09:59 AM|VincentITA|LINK
Is there definetly no solution?
May 01, 2011 02:14 AM|Naom|LINK
I think you need to create a stored procedure that will return data depending on the passed parameter (combobox value).
In the meantime I suggest you to review these blogs to understand better about this problem and performance implications of the solution suggested by limno (I don't recommend it):
Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog
Catch All Queries - short blog by Gail Shaw
Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch
May 01, 2011 09:06 AM|VincentITA|LINK