none
在SQLTOLINQ中遇到的时间空值写法的疑问! RRS feed

  • 问题

  • 两个Textbox分别对应时间搜索范围的开始时间和终止时间! 再加1个GridView和1个SQldataSource,我要达到一开始界面显示所有数据。当我在2个Textbox中填好时间,GridView才按照条件显示相应数据! 下面是我写的代码!求改进!谢谢! =======================代码============================== <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default7.aspx.cs" Inherits="Default7" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { width: 100%; } </style> </head> <body> <form id="form1" runat="server">
    起始日:<asp:TextBox ID="TextBox_LeaseDateFrom" runat="server" AutoPostBack="True"></asp:TextBox> 终止日:<asp:TextBox ID="TextBox_LeaseDateTo" runat="server" AutoPostBack="True"></asp:TextBox>
    <asp:GridView ID="GridView_Search" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ContractNumber" DataSourceID="SqlDataSource_Search"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> <asp:BoundField DataField="ContractNumber" HeaderText="ContractNumber" ReadOnly="True" SortExpression="ContractNumber" /> <asp:BoundField DataField="LeaseDateFrom" HeaderText="LeaseDateFrom" SortExpression="LeaseDateFrom" /> <asp:BoundField DataField="LeaseDateTo" HeaderText="LeaseDateTo" SortExpression="LeaseDateTo" /> </Columns> </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource_Search" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:长铁物业公司资产数据库ConnectionString2 %>" DeleteCommand="DELETE FROM [ContractTable] WHERE [ContractNumber] = @original_ContractNumber AND (([LeaseDateFrom] = @original_LeaseDateFrom) OR ([LeaseDateFrom] IS NULL AND @original_LeaseDateFrom IS NULL)) AND (([LeaseDateTo] = @original_LeaseDateTo) OR ([LeaseDateTo] IS NULL AND @original_LeaseDateTo IS NULL))" InsertCommand="INSERT INTO [ContractTable] ([ContractNumber], [LeaseDateFrom], [LeaseDateTo]) VALUES (@ContractNumber, @LeaseDateFrom, @LeaseDateTo)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [ContractNumber], [LeaseDateFrom], [LeaseDateTo] FROM [ContractTable] WHERE (([LeaseDateFrom] >= @LeaseDateFrom) AND ([LeaseDateTo] <= @LeaseDateTo))" UpdateCommand="UPDATE [ContractTable] SET [LeaseDateFrom] = @LeaseDateFrom, [LeaseDateTo] = @LeaseDateTo WHERE [ContractNumber] = @original_ContractNumber AND (([LeaseDateFrom] = @original_LeaseDateFrom) OR ([LeaseDateFrom] IS NULL AND @original_LeaseDateFrom IS NULL)) AND (([LeaseDateTo] = @original_LeaseDateTo) OR ([LeaseDateTo] IS NULL AND @original_LeaseDateTo IS NULL))"> <SelectParameters> <asp:ControlParameter ControlID="TextBox_LeaseDateFrom" DbType="Date" Name="LeaseDateFrom" PropertyName="Text" /> <asp:ControlParameter ControlID="TextBox_LeaseDateTo" DbType="Date" Name="LeaseDateTo" PropertyName="Text" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_ContractNumber" Type="String" /> <asp:Parameter DbType="Date" Name="original_LeaseDateFrom" /> <asp:Parameter DbType="Date" Name="original_LeaseDateTo" /> </DeleteParameters> <UpdateParameters> <asp:Parameter DbType="Date" Name="LeaseDateFrom" /> <asp:Parameter DbType="Date" Name="LeaseDateTo" /> <asp:Parameter Name="original_ContractNumber" Type="String" /> <asp:Parameter DbType="Date" Name="original_LeaseDateFrom" /> <asp:Parameter DbType="Date" Name="original_LeaseDateTo" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ContractNumber" Type="String" /> <asp:Parameter DbType="Date" Name="LeaseDateFrom" /> <asp:Parameter DbType="Date" Name="LeaseDateTo" /> </InsertParameters> </asp:SqlDataSource>
     
     
     
     
     
     
     
    </form> </body> </html>
    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!
    • 已移动 Song Tian 2011年8月1日 9:13 (发件人:ASP.NET 与 AJAX)
    2011年7月29日 10:38

答案

  • 首先你可以把你的SelectCommand改成下列的TSQL敘述。

     

    SELECT [ContractNumber], [LeaseDateFrom], [LeaseDateTo] 
    FROM [ContractTable] 
    WHERE (([LeaseDateFrom] >= @LeaseDateFrom OR @LeaseDateFrom = '') 
    AND ([LeaseDateTo] <= @LeaseDateTo OR @LeaseDateTo = ''))
    

     


    接著把SelectParameter都加上ConvertEmptyStringToNull="false"。

    <SelectParameters> 
    	<asp:ControlParameter ControlID="TextBox_LeaseDateFrom" DbType="Date" Name="LeaseDateFrom" PropertyName="Text" ConvertEmptyStringToNull="false" /> 
    	<asp:ControlParameter ControlID="TextBox_LeaseDateTo" DbType="Date" Name="LeaseDateTo" ConvertEmptyStringToNull="false" PropertyName="Text" /> 
    </SelectParameters>
    

    試試看這樣能否符合你的需求。



     


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年7月29日 12:52