search query as per selected checkboxes
-
Hi,
I have build a search in asp.net as user will select values from dropdownlists e.g. state, city and then showing the result.
But now I have to refine it from Locality checkboxList. i.e. if user will select one checkbox then the search query will show results of that one locality; if user checks more than one locality checkboxes then the result query will show the results as per multiple locality ids...
For fixed values i can handle but for this I need some assistance.
Plz. give some suggestion.
Thanks & Regards,
Hem Singh
Thanks & Regards,
Hem Singh -
Please could you post the SQL queries you are currently using and the ASP code you have developed already.
First thoughts are: use the CASE/IF statement or the C#/VB.NET/whatever equivalent in your ASP page to differentiate, i.e.
If checkBox1 = 1 AND checkbox2 = Nothing Then
singleQuery; 'directs to query handling a single variable
Else
doubleQuery; ' directs to query handling more than 1 variable
End If
---
Then in the queries, use the IS (NOT) NULL predicate:
singleQuery
--------------
SELECT this
FROM that
WHERE NOT state IS NULL AND city IS NULL
doubleQuery
--------------
SELECT this
FROM that
WHERE NOT state IS NULL AND WHERE NOT city IS NULL
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me. -
This can be translated down to a T-SQL "IN" clause. You can have 1 or multiple values in an IN clause (as opposed to an equals "=" query).
The key would be to write your code so it concats the values into a comma delimited string. Verify there is no comma in the last or first character position, then pass that as a parameter to your SQL query.
In the database, your query would look something like this:
CREATE PROCEDURE schema.MyProc (@Location VARCHAR(xxxx))
--xxxx stands for the number of characters you need for this.
--your code will call this proc, passing in your delimited string as the @Location variable
AS
DECLARE @MySQLString VARCHAR(xxxx);
SET @MySQLString = 'SELECT Col1, Col2 FROM schema.MyTable
WHERE Location IN (' + @Location + ')';
sp_executesql @MySQLString;
GO
Does this make sense?
-
Hi,
Thanks for the reply. currently i m using the below query(in short here):
ALTER PROCEDURE [dbo].[Property_Search]
@CityId int,
@PriceFrom money,
@PriceTo money,
@bedrooms nvarchar(20)
AS
BEGIN
SELECT
,[State]
,[City]
,Locality
FROM cj_ListProperty
WHERE CityId=@CityId and
(TotalPrice>=@PriceFrom and TotalPrice<=@PriceTo) and
Bedrooms=@Bedrooms
and here @values are coming from dropdown selected items with c# as below(in short here):
protected void srchList_Bind()
{
bedrooms = "", mainStateVal="0";
cityId = 0;
decimal pricFrom = 0, pricTo = 0;
try
{
if (Request.QueryString["city"] != null)
cityId = Convert.ToInt32(Request.QueryString["city"]);
if (Request.QueryString["from"] != null)
pricFrom = Convert.ToDecimal(Request.QueryString["from"]);
if (Request.QueryString["to"] != null)
pricTo = Convert.ToDecimal(Request.QueryString["to"]);
if (Request.Params["bdroom"] != null)
bedrooms = Request.QueryString["bdroom"];
//using linq here
myDataContext hp = new myDataContext();
var myQuery = hp.Property_Search_result(cityId, pricFrom, pricTo, bedrooms);
listViewSearch.DataSource = hpQuery;
listView.DataBind();
}
}
it is perfect till now.
but now I have a checkbox list of localities as per searched city and I have to filter this result as per selected localities from checkboxlist of localities.
there is not single or double case but on can select 4 or 10 localities.
For single or double I can extend the query in where clause as:
Where ....... and cityId=@cityId And localityId=@localityId .....for single checked locality. Or
Where ....... and cityId=@cityId And (localityId=@localityId1 And localityId=@localityId2) ......for double checked localities.
but what if user selects 4 or ten localities..
Here is the image:
Thanks & Regards,
Hem Singh -
I refer you back to my last response.
Use IN instead of = on the stored procedure and concatenate the values together. You can expand my single parameter query to use multiple parameters and to do the same concatenate for all possible values (City, Bedroom(s), Price Range(s)).
Do you understand what I mean by concatenate, right?
-
Brandie Tarvin (1/13/2012)
I refer you back to my last response.Use IN instead of = on the stored procedure and concatenate the values together......
Hi Brandie Tarvin,
I am trying yr suggestion.. but is working perfectly for 1 value not when concatenating multiple coma separated values. here is the code:
localityIds = "1,4";
myDataContext hp = new myDataContext();
var myQuery = hp.cj_ListProperty_SrhBsic2(cityId, localityIds, pricFrom, pricTo, bedrooms);
I am trying sending static values first from c# to check the result:
and here is Sql's SP:
............
@LocalityId nvarchar(50),
..........
SELECT
City
,Locality
FROM ListProperty
WHERE CityId=@CityId and
Bedrooms=@Bedrooms and
LocalityId IN(@LocalityId)
Have I to use query in var as u mentioned and then to execute...
Thanks & Regards,
Hem Singh -
HemSingh (1/13/2012)
Brandie Tarvin (1/13/2012)
I refer you back to my last response.Use IN instead of = on the stored procedure and concatenate the values together......
Hi Brandie Tarvin,
I am trying yr suggestion.. but is working perfectly for 1 value not when concatenating multiple coma separated values. here is the code:
localityIds = "1,4";
myDataContext hp = new myDataContext();
var myQuery = hp.cj_ListProperty_SrhBsic2(cityId, localityIds, pricFrom, pricTo, bedrooms);
I am trying sending static values first from c# to check the result:
and here is Sql's SP:
............
@LocalityId nvarchar(50),
..........
SELECT
City
,Locality
FROM ListProperty
WHERE CityId=@CityId and
Bedrooms=@Bedrooms and
LocalityId IN(@LocalityId)
Have I to use query in var as u mentioned and then to execute...
Try making your T-SQL code dynamic, like I did in my example. Assign the sql to a variable and then execute it.
............
@LocalityId nvarchar(50),
..........
Declare @MySQLString Varchar(800),
@Localities Varchar(25) = @LocalityID, @Cities Varchar(500)=@CityID,
@Rooms int= @bedrooms;
SET @MySQLString = 'SELECT City,Locality
FROM ListProperty
WHERE CityId= ' + @Cities + ' and Bedrooms= ' + @Rooms
+ ' and LocalityId IN (' + @Localities + ')';
sp_executesql @MySQLString
I don't know C#, so I can only address the T-SQL portion of your question.
EDIT: Now that I think about it, you may have to reassign your variables to new variables within the Proc to get this to work correctly due to SQL Injection worries... I've added a few new things to the code above.
-
giving error for @vals which are int type:
unable to cast nvarchar to int.... very confusing....
i have converted from int to nvarchar then other error. :
.....
@CityId int,
@LocalityId nvarchar(50),
@PriceFrom money,
@PriceTo money,
@bedrooms nvarchar(20)
FROM Property
DECLARE @hpQyery nvarchar(max);
SET @hpQyery= 'SELECT p.Id
,[PropertyAddress]
,(SELECT MainCityState FROM cj_MainCity_States WHERE id=p.MainCityStateId) MainCityState
,(SELECT CityValue FROM cj_Cities WHERE id=p.CityId) City
From Property p
WHERE
Convert(nvarchar(100),CityId) CityId='+ Convert(nvarchar(100),@CityId) +' and
(Convert(nvarchar(100),TotalPrice) TotalPrice>='+ Convert(nvarchar(100),@PriceFrom)+' and Convert(nvarchar(100),TotalPrice) TotalPrice<='+ Convert(nvarchar(100),@PriceTo)+') and
Bedrooms='+ @bedrooms +'and Convert(nvarchar(100),LocalityId) LocalityId IN('+@LocalityId+')
ORDER BY DateCreated DESC';
EXEC @hpQyery;
confused...
Thanks & Regards,
Hem Singh -
@bedrooms is an integer...you have to explicitly convert it when building the string:
'...Bedrooms='+ CONVERT(NVARCHAR,@Bedrooms) + '...'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Well in c# you have to do and explicit conversion to string for the concatenation.
Assuming I understood this correctly, you should have a list of city.
Depending on the object you used you should have access to a collection called selecteditems.
Just loop through all those to collect the ids.
Then the concatenation might look like this (sorry but it's been a long time and I can't test this).
Ids = Ids + ',' + MyCollectionItem.Value.toString()
Part 2 coming up.
-
Lowell (1/13/2012)
@Bedrooms is an integer...you have to explicitly convert it when building the string:'...Bedrooms='+ CONVERT(NVARCHAR,@Bedrooms) + '...'
That would mean that he can onlypass 1 value at a time, so still not doing the correct thing anyways.
Still working on part 2.
-
I work on massive ERP reports. Some of my reports have 20 parameters so you need to get inventive to both get the correct results and with fast speed.
The way to do this is create a local temp table like this :
CREATE TABLE #Localities(
LocalityId INT PRIMARY KEY CLUSTERED
)
Then split to localities from your concatenated value into that table.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Then use that table for the in in your query. The reason I use the temp table with PK is that it usually gives the best possible estimates for that filtering. Which then gives a steady performance for the SP.
Dynamic sql will give you the same speed & compile time (roughly), but with the added securities & permission issues that come with it.
-
I think your dynamic query should look like this
SET @hpQyery= 'SELECT p.Id,
p.[PropertyAddress],
s.MainCityState,
c.CityValue
FROM Property p
JOIN cj_MainCity_States s ON s.[id]=p.MainCityStateId
JOIN FROM cj_Cities c WHERE c.[id]=p.CityId
WHERE p.CityId='+Convert(nvarchar(100),@CityId)+
' AND p.TotalPrice>='+Convert(nvarchar(100),@PriceFrom)+
' AND p.TotalPrice<='+Convert(nvarchar(100),@PriceTo)+
' AND p.Bedrooms='''+@Bedrooms+
''' AND p.LocalityId IN ('+@LocalityId+
') ORDER BY DateCreated DESC';
Far away is close at hand in the images of elsewhere.
Anon. -
I'd have to agree with Remi (Ninja's_RGR'us) on this one. I'd convert any parameters that could have multiple values to varchar(max) (or a large number) and then split them into temp tables that I'd join on. The other option is to have them be XML parameters and use the native XML functions to convert to a table and join on them.
If you are using the version of .NET that allows for table valued parameters you might want to investigate using them as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack Corbett (1/13/2012)
I'd have to agree with Remi (Ninja's_RGR'us) on this one. I'd convert any parameters that could have multiple values to varchar(max) (or a large number) and then split them into temp tables that I'd join on. The other option is to have them be XML parameters and use the native XML functions to convert to a table and join on them.If you are using the version of .NET that allows for table valued parameters you might want to investigate using them as well.
FYI, VARCHAR(MAX) won't work in Reporting Services 2005. The reason being that the string sent in that parameter will cut at 8000 characters. No idea if this is fixed in later versions.
Sorry if this is out of your current context, but really worth knowing this info. Saves a lot of headache ;-). This won't affect you in .Net.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply