过滤器使用数据库dropdownlists选择 [英] Filter select from database using dropdownlists

问题描述

我有搜索页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+ _
                                WHE​​RE（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（@S​​port为空或运动= @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选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！