问题描述
我有搜索页6 dropdownlists,2文本框,并在我的asp.net web窗体应用程序的按钮。我需要点击按钮后,选择筛选数据中继器组件。我能做到这一点,当用户在SQL SELECT命令所有dropdownlists选择一些值。
SELECT * FROM型材WHERE(运动= @Sport,地区= @region,名称使用@name,...)
但是,当用户留下了一些DropDownList的或文本框为空SQL命令不显示任何内容。有谁请帮助我,如何解决呢?
编辑:
我修改了查询,但它仍然无法正常工作。
SELECT Profiles.ProfileId,Profiles.ProPicUrl,Profiles.Name,Profiles.Specialization,UsersSports.Rating+ _
从配置文件+ _
JOIN UsersSports ON Profiles.ProfileId = UsersSports.ProfileId+ _
WHERE(UsersSports.SportId = CASE WHEN @SportId!=''THEN @Sport ELSE UsersSports.SportId END)AND(地区= CASE WHEN @region!=''THEN ELSE @region END区)AND(专业= CASE WHEN @专精!=''THEN @Specialization ELSE专业化END)+ _
AND(性别= CASE WHEN @Sex!=''THEN @Sex ELSE性别END)AND((@AgeFrom不是null,@AgeTo IS NOT NULL和BETWEEN @AgeFrom和@AgeTo年龄)OR(@AgeFrom IS NULL和@AgeTo IS NULL和年龄))+ _
AND((@PractiseFrom不是null,@PractiseTo IS NOT NULL与实践之间@PractiseFrom和@PractiseTo)OR(@PractiseFrom IS NULL和@PractiseTo IS NULL和实践))+ _
AND((@Name不是null,名称LIKE'%@名称%')或(@Name IS NULL和名称))AND((@city IS NOT NULL,市LIKE'%@%市')或(@市IS NULL,市))+ _
ORDER BY UsersSports.Rating ASC
这应该是如下 - 只是确保当未选择下拉,参数设置为 NULL
:
SELECT * FROM简介
WHERE(@Sport为空或运动= @Sport)
AND(@region为空或地区= @region)
AND(@Name为空或名称,比如@Name)
I've got search page with 6 dropdownlists, 2 textboxes and a button in my asp.net web forms application. I need to select filtered data to a repeater component after clicking the button. I can do this when user select some values in all dropdownlists with SQL Select Command.
SELECT * FROM Profiles WHERE (Sport = @Sport, Region = @Region, Name LIKE @Name,...)
But when user left some dropdownlist or textbox empty SQL Command doesn't display anything. Could anybody please help me, how to solve this?
EDIT:I modified the query, but still it doesn't work.
SELECT Profiles.ProfileId,Profiles.ProPicUrl, Profiles.Name, Profiles.Specialization, UsersSports.Rating " + _
"FROM Profiles " + _
"JOIN UsersSports ON Profiles.ProfileId = UsersSports.ProfileId " + _
"WHERE (UsersSports.SportId = CASE WHEN @SportId != '' THEN @Sport ELSE UsersSports.SportId END) AND (Region = CASE WHEN @Region != '' THEN @Region ELSE Region END) AND (Specialization = CASE WHEN @Specialization != '' THEN @Specialization ELSE Specialization END)" + _
"AND (Sex = CASE WHEN @Sex != '' THEN @Sex ELSE Sex END) AND ((@AgeFrom IS NOT NULL AND @AgeTo IS NOT NULL AND Age BETWEEN @AgeFrom AND @AgeTo) OR (@AgeFrom IS NULL AND @AgeTo IS NULL AND Age)) " + _
"AND ((@PractiseFrom IS NOT NULL AND @PractiseTo IS NOT NULL AND Practise BETWEEN @PractiseFrom AND @PractiseTo) OR (@PractiseFrom IS NULL AND @PractiseTo IS NULL AND Practise))" + _
"AND ((@Name IS NOT NULL AND Name LIKE '%@Name%') OR (@Name IS NULL AND Name)) AND ((@City IS NOT NULL AND City LIKE '%@City%') OR (@City IS NULL AND City))" + _
"ORDER BY UsersSports.Rating ASC
It should be as follows - just ensure when a dropdown is not selected, the parameter is set to NULL
:
SELECT * FROM Profiles
WHERE (@Sport IS NULL OR Sport = @Sport)
AND (@Region IS NULL OR Region = @Region)
AND (@Name IS NULL OR Name LIKE @Name)
这篇关于过滤器使用数据库dropdownlists选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!