本文介绍了过滤器使用数据库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选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-20 12:42