两个问题：1.DropDownList控件的‘未选择’和bit类型数据冲突。2.如何把Lable控件的指定部分变成彩色

Question

----问题：1.DropDownList控件的‘未选择’和bit类型数据冲突。

--

----在SqlDataSource控件中我输入：SELECT N'未选择' AS propertystate UNION ALL SELECT DISTINCT propertystate FROM PropertyTable。一般情况下这样做是没错的，可是这次字段PropertyState比较特殊，是bit型的。于是就出现了错误提示：Varchar类型的‘未选择’不能转换成bit类型。

--

----请问，如何在不更改Propertystate的情况下，解决这个问题？

----

----问题2：如何把Lable控件的指定部分变成彩色？

--

--下面这段代码，我怎么样把 Count 变成彩色的呢？

protected void SqlDataSource_Search_Selected(object sender, SqlDataSourceStatusEventArgs e)
    {
        string count = e.Command.Parameters["@Count"].Value.ToString();//取得行數
        string BuildingArea = e.Command.Parameters["@BuildingArea"].Value.ToString();//取得Sum值
        string MonthRent = e.Command.Parameters["@MonthRent"].Value.ToString();
        Label_Count.Text = "共有：" + count + "项符合条件,面积合计： " + BuildingArea + " ㎡,理论月租金为："+MonthRent +"元。查询正在履行的合同请点击“合同状态”中的“正在履行”";
        
    }

向各位大大请教，谢谢解答！

 

---

C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！

Answer 1

问题：1.DropDownList控件的‘未选择’和bit类型数据冲突。

試試

SELECT N'未选择' AS propertystate
UNION ALL
SELECT DISTINCT Convert(nvarchar,propertystate) as propertystate FROM PropertyTable

 

问题2：

Answer↓

  Label_Count.Text = "共有：<span style='color:red;'>" + count + "</span>项符合条件,面积合计： " + BuildingArea + " ㎡,理论月租金为："+MonthRent +"元。查询正在履行的合同请点击“合同状态”中的“正在履行”";

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/

Answer 2

----关于第一个问题，深入问一下：

--

----因为我的字段PropertyState是Bit类型的，一般只取值‘1’或‘0’，如果直接用您提供的代码，下拉键将会显示出3个选项，分别是‘未选择’、‘1’、‘0’。

--

----如果我想把下拉键显示出来的3个选项表现出来分别是'未选择'、‘正在使用’、‘未使用’，请问该怎么改呢？最好还是直接能在sqldataSource中写代码。

---

C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！

Answer 3

----如果我想把下拉键显示出来的3个选项表现出来分别是'未选择'、‘正在使用’、‘未使用’，请问该怎么改呢？最好还是直接能在sqldataSource中写代码。

Try it↓

SELECT N'未选择' AS propertystate 
UNION ALL 
SELECT DISTINCT 
Case When propertystate=1 Then N'正在使用'
     When propertystate=0 Then N'未使用'
     End As propertystate FROM PropertyTable

 

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/

Answer 4

A1：除了從T-SQL著手，你也可以利用DropDownList的屬性來達到相同效果。如此一來你就不用自己用Union產生未選擇的選項。重點在於把AppendDataBoundItems設為True。

<asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="True" 
            DataSourceID="SqlDataSource1" DataTextField="c1" DataValueField="c1">
   <asp:ListItem>未選擇</asp:ListItem>
</asp:DropDownList>

A2：也可以試試看這樣的作法。

Label1.Text = "總筆數：<font color='red'>" + count + "</font>";

 

---

以上說明若有錯誤請指教，謝謝。
http://www.dotblogs.com.tw/terrychuang/

Answer 5

因为我的字段PropertyState是Bit类型的，一般只取值‘1’或‘0’，如果直接用您提供的代码，下拉键将会显示出3个选项，分别是‘未选择’、‘1’、‘0’。

bit只能表达0和1，如果想要表达更多的，请使用int而不是bit。

int的话，假设就这三个选项，你拖拽一个Dropdownlist到页面上，然后点击右上角的小箭头，在弹出的方框中手动输入Text是“未编辑”，“1”，“0”；分别对应“-1”，“1”，“0”（作为Value）。更新是取Value的内容的。

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 6

----如果我想把下拉键显示出来的3个选项表现出来分别是'未选择'、‘正在使用’、‘未使用’，请问该怎么改呢？最好还是直接能在sqldataSource中写代码。

Try it↓

SELECT N'未选择' AS propertystate 
UNION ALL 
SELECT DISTINCT 
Case When propertystate=1 Then N'正在使用'
     When propertystate=0 Then N'未使用'
     End As propertystate FROM PropertyTable

 

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/

接下来出现的新问题，举例：下面是SqlDataSource的代码

select *
from ContractTable 
where ISNULL (@propertyState,N'')='未选择' or propertyState=@propertyState

则会出现下图所示的错误：

 

---

C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！

Answer 7

接下来出现的新问题，举例：下面是SqlDataSource的代码

 

select *
from ContractTable 
where ISNULL (@propertyState,N'')='未选择' or propertyState=@propertyState

 

 

建议该SqlDataSource整段贴上来看看

主要想看<asp:Parameter />里  @propertyState您声明了什么类型？

目前这样看了您的SQL语句

就只能建议改成这样

 

select *
from ContractTable 
where ISNULL (Convert(nvarchar,@propertyState),N'')='未选择' or Convert(nvarchar,propertyState)=Convert(nvarchar,@propertyState)

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/

Answer 8

----如果我想把下拉键显示出来的3个选项表现出来分别是'未选择'、‘正在使用’、‘未使用’，请问该怎么改呢？最好还是直接能在sqldataSource中写代码。

Try it↓

SELECT N'未选择' AS propertystate 
UNION ALL 
SELECT DISTINCT 
Case When propertystate=1 Then N'正在使用'
     When propertystate=0 Then N'未使用'
     End As propertystate FROM PropertyTable

 

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/

接下来出现的新问题，举例：下面是SqlDataSource的代码

 

select *
from ContractTable 
where ISNULL (@propertyState,N'')='未选择' or propertyState=@propertyState

 

则会出现下图所示的错误：

 

---

C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！

“未选择”字符串，无法转化成int或者bit类型。

除了把该字段改成int以外，我建议你使用这样的做法：

1）对于Dropdownlist：

 <asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%#Bind("更新的字段名称") %>'>
        <asp:ListItem Value="-1">未选择</asp:ListItem>
        <asp:ListItem Value="0">0</asp:ListItem>
        <asp:ListItem Value="1">1</asp:ListItem>
    </asp:DropDownList>

2）在对应的SqlDataSource更新中，请确保类型是int即可。这样应该可以实现同步更新了。

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 9

PropertyState 在SQL数据库中是Bit类型

<asp:SqlDataSource ID="SqlDataSource_Search" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:长铁物业公司资产数据库ConnectionString %>" 
                    
                    
                    
                    
                    
                    
                    SelectCommand="SELECT a.SumbitNumber AS 送审编号, a.ContractNumber AS 合同编号, b.PropertyTypeNumber AS 资产编号, a.ManageProject AS 经营项目, b.City + b.Road + b.Address AS 地址, a.OppositeSide AS 承租单位, b.RoomQuantity AS 间或处, b.BuildingArea AS 面积, a.LeaseDateFrom AS 租赁起始日, a.LeaseDateTo AS 租赁终止日, a.TypeOfPayment AS 交租方式, ROUND(a.MonthRent / b.BuildingArea, 1) AS [租金单价(元/㎡)], a.MonthRent AS 月租金, a.MonthRent * 12 AS 年租金, a.YearRent AS 原年租金, a.RateOfRise AS 增长率, a.SumRent AS 合同总金额, a.LeaseAssureMoney AS 租赁保证金, a.PenalSum AS 违约金, a.ReferencePrice AS 地段指导价, a.ReferencePriceRemarks AS 指导价备注, (SELECT ISNULL(b.PropertyRemarks, '') + ' ' + ISNULL(a.ContractRemarks, '') AS Expr1) AS 资产及合同备注, (CASE WHEN b.neartheroad = '0' THEN '否' ELSE '是' END) AS 是否临街, (CASE WHEN a.SafetyContract = '1' THEN '有' ELSE '无' END) AS 安全协议, (CASE WHEN a.FamilyPlanningContract = '1' THEN '有' ELSE '无' END) AS 计生协议, (CASE WHEN a.FoodContract = '1' THEN '有' ELSE '无' END) AS 食品卫生承诺, b.ConstructionType AS 建筑性质, b.SupplyWater AS 供水, b.SupplyElectricity AS 供电 FROM ContractTable AS a INNER JOIN PropertyTable AS b ON a.City + a.Road + a.Address = b.City + b.Road + b.Address WHERE (ISNULL(@ContractNumber, N'') = '-1' OR ISNULL(a.ContractNumber, N'') LIKE '%' + @ContractNumber + '%') AND (ISNULL(@contracttype, N'') = '未选择' OR a.ContractType = @contracttype) AND (ISNULL(@PropertyType, N'') = '未选择' OR b.PropertyType = @PropertyType) AND (ISNULL(@city, N'') = '未选择' OR b.City = @city) AND (ISNULL(@Road, N'') = '未选择' OR b.Road = @Road) AND (ISNULL(@Address, N'') = '未选择' OR b.Address = @Address) AND (ISNULL(@oppositeside, N'') = '-1' OR ISNULL(a.OppositeSide, N'') LIKE '%' + @oppositeside + '%') AND ((ISNULL(a.LeaseDateTo, N'') = '1999-01-01' OR a.LeaseDateTo &gt;= @LeaseDateTo1) AND (ISNULL(a.LeaseDateTo, N'') = '9999-12-31' OR a.LeaseDateTo &lt;= @LeaseDateTo) OR a.LeaseDateTo IS NULL) AND (ISNULL(@ContractCompany, N'') = '未选择' OR a.ContractCompany = @ContractCompany) AND (ISNULL(@PerformState, N'') = '未选择' OR a.PerformState = @PerformState) AND (ISNULL(@PropertyState, N'') = '未选择' OR b.PropertyState = @PropertyState) ORDER BY b.City, b.Road, b.Address ; SELECT @Count=count(*) FROM ContractTable AS a INNER JOIN PropertyTable AS b ON a.City + a.Road + a.Address = b.City + b.Road + b.Address WHERE (ISNULL(@ContractNumber, N'') = '-1' OR ISNULL(a.ContractNumber, N'') LIKE '%' + @ContractNumber + '%') AND (ISNULL(@contracttype, N'') = '未选择' OR a.ContractType = @contracttype) AND (ISNULL(@PropertyType, N'') = '未选择' OR b.PropertyType = @PropertyType) AND (ISNULL(@city, N'') = '未选择' OR b.City = @city) AND (ISNULL(@Road, N'') = '未选择' OR b.Road = @Road) AND (ISNULL(@Address, N'') = '未选择' OR b.Address = @Address) AND (ISNULL(@oppositeside, N'') = '-1' OR ISNULL(a.OppositeSide, N'') LIKE '%' + @oppositeside + '%') AND ((ISNULL(a.LeaseDateTo, N'') = '1999-01-01' OR a.LeaseDateTo &gt;= @LeaseDateTo1) AND (ISNULL(a.LeaseDateTo, N'') = '9999-12-31' OR a.LeaseDateTo &lt;= @LeaseDateTo) OR a.LeaseDateTo IS NULL) AND (ISNULL(@ContractCompany, N'') = '未选择' OR ContractCompany = @ContractCompany) AND (ISNULL(@PerformState, N'') = '未选择' OR PerformState = @PerformState) AND (ISNULL(@PropertyState, N'') = '未选择' OR b.PropertyState = @PropertyState) ;SELECT @SumAddRent=Sum(monthrent) FROM ContractTable AS a INNER JOIN PropertyTable AS b ON a.City + a.Road + a.Address = b.City + b.Road + b.Address WHERE (ISNULL(@ContractNumber, N'') = '-1' OR ISNULL(a.ContractNumber, N'') LIKE '%' + @ContractNumber + '%') AND (ISNULL(@contracttype, N'') = '未选择' OR a.ContractType = @contracttype) AND (ISNULL(@PropertyType, N'') = '未选择' OR b.PropertyType = @PropertyType) AND (ISNULL(@city, N'') = '未选择' OR b.City = @city) AND (ISNULL(@Road, N'') = '未选择' OR b.Road = @Road) AND (ISNULL(@Address, N'') = '未选择' OR b.Address = @Address) AND (ISNULL(@oppositeside, N'') = '-1' OR ISNULL(a.OppositeSide, N'') LIKE '%' + @oppositeside + '%') AND ((ISNULL(a.LeaseDateTo, N'') = '1999-01-01' OR a.LeaseDateTo &gt;= @LeaseDateTo1) AND (ISNULL(a.LeaseDateTo, N'') = '9999-12-31' OR a.LeaseDateTo &lt;= @LeaseDateTo) OR a.LeaseDateTo IS NULL) AND (ISNULL(@ContractCompany, N'') = '未选择' OR ContractCompany = @ContractCompany) AND (ISNULL(@PerformState, N'') = '未选择' OR PerformState = @PerformState) AND (ISNULL(@PropertyState, N'') = '未选择' OR b.PropertyState = @PropertyState) ;SELECT @avg=avg(b.buildingarea) FROM ContractTable AS a INNER JOIN PropertyTable AS b ON a.City + a.Road + a.Address = b.City + b.Road + b.Address WHERE (ISNULL(@ContractNumber, N'') = '-1' OR ISNULL(a.ContractNumber, N'') LIKE '%' + @ContractNumber + '%') AND (ISNULL(@contracttype, N'') = '未选择' OR a.ContractType = @contracttype) AND (ISNULL(@PropertyType, N'') = '未选择' OR b.PropertyType = @PropertyType) AND (ISNULL(@city, N'') = '未选择' OR b.City = @city) AND (ISNULL(@Road, N'') = '未选择' OR b.Road = @Road) AND (ISNULL(@Address, N'') = '未选择' OR b.Address = @Address) AND (ISNULL(@oppositeside, N'') = '-1' OR ISNULL(a.OppositeSide, N'') LIKE '%' + @oppositeside + '%') AND ((ISNULL(a.LeaseDateTo, N'') = '1999-01-01' OR a.LeaseDateTo &gt;= @LeaseDateTo1) AND (ISNULL(a.LeaseDateTo, N'') = '9999-12-31' OR a.LeaseDateTo &lt;= @LeaseDateTo) OR a.LeaseDateTo IS NULL) AND (ISNULL(@ContractCompany, N'') = '未选择' OR ContractCompany = @ContractCompany) AND (ISNULL(@PerformState, N'') = '未选择' OR PerformState = @PerformState) AND (ISNULL(@PropertyState, N'') = '未选择' OR b.PropertyState = @PropertyState)" onselected="SqlDataSource_Search_Selected"  
                    >
                    <SelectParameters>
                        <asp:ControlParameter ControlID="TextBox_ContractNumber" DefaultValue="-1" 
                            Name="ContractNumber" PropertyName="Text" />
                        <asp:ControlParameter ControlID="DropDownList_ContractType" Name="contracttype" 
                            PropertyName="SelectedValue" DefaultValue="" />
                        <asp:ControlParameter ControlID="DropDownList_PropertyType" DefaultValue="" 
                            Name="propertytype" PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_City" DefaultValue="" Name="city" 
                            PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_Road" Name="Road" 
                            PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_Address" Name="address" 
                            PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="TextBox_Oppositeside" DefaultValue="-1" 
                            Name="oppositeside" PropertyName="Text" />
                        <asp:ControlParameter ControlID="TextBox_LeaseDateTo1" 
                            DefaultValue="1999-01-01" Name="LeaseDateTo1" PropertyName="Text" />
                        <asp:ControlParameter ControlID="TextBox_LeaseDateTo" DefaultValue="9999-12-31" 
                            Name="LeaseDateTo" PropertyName="Text" />
                        <asp:ControlParameter ControlID="DropDownList_Company" DefaultValue="" 
                            Name="ContractCompany" PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_PerformState" DefaultValue="" 
                            Name="PerformState" PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_PropertyState" DefaultValue="" 
                            Name="PropertyState" PropertyName="SelectedValue" />
                        <asp:Parameter Name="Count" Direction="Output" Type="Int32" />    
                        <asp:Parameter Name="SumAddRent" Direction="Output" Type="Int32" />    
                    
                        <asp:Parameter Name="avg" Type="Int32" Direction="Output" />
                    
                    </SelectParameters>
                </asp:SqlDataSource>

---

C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！

Answer 10

你好linjiangxian11：）

既然你存在“未选择”、“0”、“1”为什么用bit？bit只有两种类型啊，呵呵！

请使用我的方法，改用int+Dropdownlist绑定方式看看呢。谢谢！

PS：你数据库中如果bit允许为空，那么也可以（Dropdownlist还是沿用我一开始的），并且select语句这样做法：

select isnull(绑定的字段名称,-1) as 字段名称，* from xxx

 <asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%#Bind("更新的字段名称") %>'>
        <asp:ListItem Value="-1">未选择</asp:ListItem>
        <asp:ListItem Value="0">0</asp:ListItem>
        <asp:ListItem Value="1">1</asp:ListItem>
</asp:DropDownList>

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 11

    我还是沿用PropertyState是Bit类型，只有null、1、0三个值，使用Wei_Dong大大提供的方法完全解决了问题，也感谢T大的重点提示。

--

----不过我还是很好奇！使用Shadow大的主要使用SQL语句的方法如何解决这个问题！感觉很接近了，应该就是Convert转换，可我具体不知道该怎么转！

---

C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！

Answer 12

很高兴你解决了问题！祝贺啊。

等Shadow And Happy来和你回复吧，没准有更好的方法，解题方法往往多种多样的。

希望能够常来论坛交流或者帮助别人！

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 13

把       <asp:ControlParameter ControlID="DropDownList_PropertyState" DefaultValue=""
                            Name="PropertyState" PropertyName="SelectedValue" />

DefaultValue改成DefaultValue="未选择"

再追加Type="String"

然后所有的SQL语句

不论是

PropertyState字段还是@PropertyState都改写为Convert(nvarchar,PropertyState)和Convert(nvarchar,@PropertyState)

但既然您已经解决问题了，就不必用我这么麻烦的方法了

(另外请记得标记适当的回覆为答案 当作结帖)

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/

Answer 14

把       <asp:ControlParameter ControlID="DropDownList_PropertyState" DefaultValue=""
                            Name="PropertyState" PropertyName="SelectedValue" />

DefaultValue改成DefaultValue="未选择"

再追加Type="String"

然后所有的SQL语句

不论是

PropertyState字段还是@PropertyState都改写为Convert(nvarchar,PropertyState)和Convert(nvarchar,@PropertyState)

    我按照您的提示做了以上修改，现在的情况是：在‘未选择’的情况下，全部正常显示；在‘正在使用’或‘停用’的情况下不显示任何数据。

    这个情况太奇怪了！下面是我的SQL部分代码：

<asp:SqlDataSource ID="SqlDataSource_Search" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:长铁物业公司资产数据库ConnectionString %>" 
                    
                    
                    
                    
                    
                    
                    
                    SelectCommand="SELECT a.SumbitNumber AS 送审编号, a.ContractNumber AS 合同编号, b.PropertyTypeNumber AS 资产编号, a.ManageProject AS 经营项目, b.City + b.Road + b.Address AS 地址, a.OppositeSide AS 承租单位, b.RoomQuantity AS 间或处, b.BuildingArea AS 面积, a.LeaseDateFrom AS 租赁起始日, a.LeaseDateTo AS 租赁终止日, a.TypeOfPayment AS 交租方式, ROUND(a.MonthRent / b.BuildingArea, 1) AS [租金单价(元/㎡)], a.MonthRent AS 月租金, a.MonthRent * 12 AS 年租金, a.YearRent AS 原年租金, a.RateOfRise AS 增长率, a.SumRent AS 合同总金额, a.LeaseAssureMoney AS 租赁保证金, a.PenalSum AS 违约金, a.ReferencePrice AS 地段指导价, a.ReferencePriceRemarks AS 指导价备注, (SELECT ISNULL(b.PropertyRemarks, '') + ' ' + ISNULL(a.ContractRemarks, '') AS Expr1) AS 资产及合同备注, (CASE WHEN b.neartheroad = '0' THEN '否' ELSE '是' END) AS 是否临街, (CASE WHEN a.SafetyContract = '1' THEN '有' ELSE '无' END) AS 安全协议, (CASE WHEN a.FamilyPlanningContract = '1' THEN '有' ELSE '无' END) AS 计生协议, (CASE WHEN a.FoodContract = '1' THEN '有' ELSE '无' END) AS 食品卫生承诺, b.ConstructionType AS 建筑性质, b.SupplyWater AS 供水, b.SupplyElectricity AS 供电 FROM ContractTable AS a INNER JOIN PropertyTable AS b ON a.City + a.Road + a.Address = b.City + b.Road + b.Address WHERE (ISNULL(@ContractNumber, N'') = '-1' OR ISNULL(a.ContractNumber, N'') LIKE '%' + @ContractNumber + '%') AND (ISNULL(@contracttype, N'') = '未选择' OR a.ContractType = @contracttype) AND (ISNULL(@PropertyType, N'') = '未选择' OR b.PropertyType = @PropertyType) AND (ISNULL(@city, N'') = '未选择' OR b.City = @city) AND (ISNULL(@Road, N'') = '未选择' OR b.Road = @Road) AND (ISNULL(@Address, N'') = '未选择' OR b.Address = @Address) AND (ISNULL(@oppositeside, N'') = '-1' OR ISNULL(a.OppositeSide, N'') LIKE '%' + @oppositeside + '%') AND ((ISNULL(a.LeaseDateTo, N'') = '1999-01-01' OR a.LeaseDateTo &gt;= @LeaseDateTo1) AND (ISNULL(a.LeaseDateTo, N'') = '9999-12-31' OR a.LeaseDateTo &lt;= @LeaseDateTo) OR a.LeaseDateTo IS NULL) AND (ISNULL(@ContractCompany, N'') = '未选择' OR a.ContractCompany = @ContractCompany) AND (ISNULL(@PerformState, N'') = '未选择' OR a.PerformState = @PerformState) AND (ISNULL(CONVERT (nvarchar, @PropertyState), N'') = '未选择' OR CONVERT (nvarchar, b.PropertyState) = CONVERT (nvarchar, @PropertyState)) ORDER BY b.City, b.Road, b.Address" onselected="SqlDataSource_Search_Selected"  
                    >
                    <SelectParameters>
                        <asp:ControlParameter ControlID="TextBox_ContractNumber" DefaultValue="-1" 
                            Name="ContractNumber" PropertyName="Text" />
                        <asp:ControlParameter ControlID="DropDownList_ContractType" Name="contracttype" 
                            PropertyName="SelectedValue" DefaultValue="" />
                        <asp:ControlParameter ControlID="DropDownList_PropertyType" DefaultValue="" 
                            Name="propertytype" PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_City" DefaultValue="" Name="city" 
                            PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_Road" Name="Road" 
                            PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_Address" Name="address" 
                            PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="TextBox_Oppositeside" DefaultValue="-1" 
                            Name="oppositeside" PropertyName="Text" />
                        <asp:ControlParameter ControlID="TextBox_LeaseDateTo1" 
                            DefaultValue="1999-01-01" Name="LeaseDateTo1" PropertyName="Text" />
                        <asp:ControlParameter ControlID="TextBox_LeaseDateTo" DefaultValue="9999-12-31" 
                            Name="LeaseDateTo" PropertyName="Text" />
                        <asp:ControlParameter ControlID="DropDownList_Company" DefaultValue="" 
                            Name="ContractCompany" PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_PerformState" DefaultValue="" 
                            Name="PerformState" PropertyName="SelectedValue" />
                        <asp:ControlParameter ControlID="DropDownList_PropertyState" DefaultValue="未选择" 
                            Name="PropertyState" PropertyName="SelectedValue" Type="String" />
                    
                    </SelectParameters>
                </asp:SqlDataSource>

---

C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！

Answer 15

 

    我按照您的提示做了以上修改，现在的情况是：在‘未选择’的情况下，全部正常显示；在‘正在使用’或‘停用’的情况下不显示任何数据。

    这个情况太奇怪了！下面是我的SQL部分代码：

 

 

您不是照着Wei_Dong 大大的方法实现成功了吗？

 

我是认为之前的SQL语句造成的

SELECT N'未选择' AS propertystate 
UNION ALL 
SELECT DISTINCT 
Case When propertystate=1 Then N'正在使用'
     When propertystate=0 Then N'未使用'
     End As propertystate FROM PropertyTable

这样传给DB的值就是正在使用和未使用两种

这样的话，得再改SqlDataSource的SQL语句

由于再改下去，您的SQL会过于复杂，小弟建议您还是维持Wei_Dong 大大提供的方法比较好

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/

Answer 16

谢谢您的解答！

    确实采取Wei_Dong的方法容易实现！下面我把在解决这个问题中最关键的地方写出来，供后来人参考

--

----如果字段PropertyState是Bit类型：

--  1.设置DropDownList，

<asp:DropDownList ID="DropDownList_PropertyState" runat="server" 
                    AutoPostBack="True">
                    <asp:ListItem>未选择</asp:ListItem>
                    <asp:ListItem Value="1">正在使用</asp:ListItem>
                    <asp:ListItem Value="0">停用</asp:ListItem>
                </asp:DropDownList>

--

----2.因为是Bit类型，Sql语句必须如下，（如果‘未选择’的Value值为‘-1’，就不行，因为Bit类型没有-1）

(ISNULL(CONVERT (nvarchar, @PropertyState), N'') = '未选择' OR CONVERT (nvarchar, b.PropertyState) = CONVERT (nvarchar, @PropertyState)) 

--

----其实如果PropertyState是别的类型就简单多了！

---

C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！