locked
Set Datetime.Now into Defaultvalue of ControlParameter to display in gridview RRS feed

  • Question

  • User-906866042 posted

    Hi, 

    I am trying to filter my gridview display by date.  For now, it able to use filter expression to display between two dates.

    Then I want to filter the date by only today not specific date   

    Is that possible to set the default value like Defaultvalue = <% DateTime.Now%> to show the row?

    Here is my code:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:VisitorConnectionString2 %>"

    DeleteCommand="DELETE FROM [Visitor] WHERE [VisitorID] = @VisitorID"

    InsertCommand="INSERT INTO [Visitor] ([VisitorFirstName], [VisitorLastName], [VisitorCompany], [CompanyTtile], [VisitorHost], [VisitorType], [CheckInTime], [ChekcOutTime]) VALUES (@VisitorFirstName, @VisitorLastName, @VisitorCompany, @CompanyTitle, @VisitorHost, @VisitorType, @CheckInTime, @ChekcOutTime)" ProviderName="<%$ ConnectionStrings:VisitorConnectionString2.ProviderName %>"

    SelectCommand="SELECT [VisitorID], [VisitorFirstName], [VisitorLastName], [VisitorCompany], [CompanyTitle], [VisitorHost], [VisitorType], [CheckInTime], [ChekcOutTime] FROM [Visitor] "

    FilterExpression=" [CheckInTime] >= #{0}# AND [CheckInTime] <= #{1}#" 

                                <FilterParameters>                       

                                    <asp:ControlParameter ControlID="tbStartDate" DefaultValue="1/1/1900" Name="startDate" PropertyName="Text" Type="DateTime"  />

                                    <asp:ControlParameter ControlID="tbEndDate" DefaultValue="12/31/2059" Name="endDate" PropertyName="Text" Type="DateTime" />

                                </FilterParameters>

     

    Thanks

     

    Tuesday, July 11, 2017 12:26 AM

Answers

  • User-335504541 posted

    Hi cn0341,

    I'm using the following code, and it works.

    In aspx:

    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"></asp:GridView>
    <asp:TextBox ID="tbStartDate" runat="server"></asp:TextBox>
    <asp:TextBox ID="tbEndDate" runat="server"></asp:TextBox>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="YOUR CONNCETIONSTRING" SelectCommand="SELECT * from Visitor" FilterExpression=" [CheckInTime] >= #{0}# AND [CheckInTime] <= #{1}#" > <FilterParameters> <asp:ControlParameter ControlID="tbStartDate" Name="startDate" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="tbEndDate" DefaultValue="12/31/2059" Name="endDate" PropertyName="Text" Type="DateTime" /> </FilterParameters> </asp:SqlDataSource>

    In behind code:

      protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource1.FilterParameters[0].DefaultValue = DateTime.Now.ToShortDateString();
            }

    Best Regards

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 12, 2017 2:05 AM

All replies

  • User-1716253493 posted

    Maybe you can use selectparameter instead, modify the query something like this

    without defaultvalue set

    WHERE datecol between isnull(@stardate, cast(getdate() as date)) and isnull(@enddate, cast(getdate() as date))

    Tuesday, July 11, 2017 12:57 AM
  • User-335504541 posted

    Hi cn0341,

    I think you could try to set the Defaultvalue in behind code.

    For example:

     SqlDataSource1.FilterParameters[0].DefaultValue = DateTime.Now.ToShortDateString();

    Best Regards

    Billy


     

    Tuesday, July 11, 2017 7:53 AM
  • User-1716253493 posted

    Maybe you can use sqldatasource filtering event like this

            If e.ParameterValues("stardate") = "1/1/1900" Then
                e.ParameterValues("stardate") = Now.Date
            End If

    Tuesday, July 11, 2017 8:58 AM
  • User-906866042 posted

    Thank your reply, I found most answers prefer using SQL query to filter. 

    I just wondering is that possible filter by filter parameter?

    Tuesday, July 11, 2017 4:07 PM
  • User-906866042 posted

    Hi cn0341,

    I think you could try to set the Defaultvalue in behind code.

    For example:

     SqlDataSource1.FilterParameters[0].DefaultValue = DateTime.Now.ToShortDateString();

    Best Regards

    Billy


     

    Hi, thank your reply. 

    I have tried this way but got error (Object reference not set to an instance of object)

    Tuesday, July 11, 2017 4:09 PM
  • User-335504541 posted

    Hi cn0341,

    I'm using the following code, and it works.

    In aspx:

    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"></asp:GridView>
    <asp:TextBox ID="tbStartDate" runat="server"></asp:TextBox>
    <asp:TextBox ID="tbEndDate" runat="server"></asp:TextBox>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="YOUR CONNCETIONSTRING" SelectCommand="SELECT * from Visitor" FilterExpression=" [CheckInTime] >= #{0}# AND [CheckInTime] <= #{1}#" > <FilterParameters> <asp:ControlParameter ControlID="tbStartDate" Name="startDate" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="tbEndDate" DefaultValue="12/31/2059" Name="endDate" PropertyName="Text" Type="DateTime" /> </FilterParameters> </asp:SqlDataSource>

    In behind code:

      protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource1.FilterParameters[0].DefaultValue = DateTime.Now.ToShortDateString();
            }

    Best Regards

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 12, 2017 2:05 AM
  • User-1716253493 posted

    Hi cn0341,

    I'm using the following code, and it works.

     Page_Load(object sender, EventArgs e)
            {
                SqlDataSource1.FilterParameters[0].DefaultValue = DateTime.Now.ToShortDateString();
            }

    AFAIK, setting DefaulValue directly in parameter i.e DefaultValue="1/1/1900"

    The behavior, when the startdate is blank then @stardate become 1/1/1900, then when startdate text has spesific date then @stardate become the date, when remove stardate text then @startdate back to 1/1/1900.

    Are you sure setting defaultvalue from codebehind have same behaviour?

    Wednesday, July 12, 2017 3:32 AM
  • User-335504541 posted

    Hi

    Here is the result of my code:

    Best Regards

    BIlly

    Wednesday, July 12, 2017 5:31 AM
  • User-906866042 posted

    Hi cn0341,

    I'm using the following code, and it works.

    In aspx:

    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"></asp:GridView>
    <asp:TextBox ID="tbStartDate" runat="server"></asp:TextBox>
    <asp:TextBox ID="tbEndDate" runat="server"></asp:TextBox>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                    ConnectionString="YOUR CONNCETIONSTRING"
                    SelectCommand="SELECT * from Visitor"
                    FilterExpression=" [CheckInTime] >= #{0}# AND [CheckInTime] <= #{1}#" >               
                    <FilterParameters>
                        <asp:ControlParameter ControlID="tbStartDate" Name="startDate" PropertyName="Text" Type="DateTime" />
                        <asp:ControlParameter ControlID="tbEndDate" DefaultValue="12/31/2059" Name="endDate" PropertyName="Text" Type="DateTime" />
                    </FilterParameters>
                </asp:SqlDataSource>

    In behind code:

      protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource1.FilterParameters[0].DefaultValue = DateTime.Now.ToShortDateString();
            }

    Best Regards

    Billy

    Thanks!!  It's work!! 

    Wednesday, July 12, 2017 10:03 PM