Solved

Populate a DataGrid from a Calendar Control Date Range

Posted on 2008-10-05
6
802 Views
Last Modified: 2013-11-26
This should be so simple.
How does one populate a GridView from a Date Range?
I'd just as soon do it on the .aspx page not using code behind because of Paging and Sorting issues.
so this shold be easy.
0
Comment
Question by:RalphHxyz
  • 5
6 Comments
 
LVL 11

Expert Comment

by:saleek
Comment Utility
Hi,

A Datasource is simply given to the gridview and it will go ahead and display it - is there any specific difficulty that you are encountering?

If you query your data, providing a start date and end date and then the resulting dataset or datatable (or whatever) can be given to the grid as a datasource.

Or do you mean that you would like to select a range on a single calendar control?

thanks,

saleek
0
 

Author Comment

by:RalphHxyz
Comment Utility
Yes I need to use a Calendar Control in DayWeekMonth mode.
Ralph
0
 

Author Comment

by:RalphHxyz
Comment Utility
This is all because of how hard it appears to be to setup sorting on a DataGrid
with a unbound datasource.
So anyway here is a working .aspx that does exactly what I want to do.
The only difference is that for this working page I am using a bound SQL DataSource
for the GridView. This is using a SQL NORTWND data base.
I need to use a Access database, I will try subsituting the AccessData Source for the SQL.
This works:
    <form id="form1" runat="server">
    <div>    
        <asp:Calendar ID="Calendar1" runat="server"
            onselectionchanged="Calendar1_SelectionChanged" SelectionMode="DayWeekMonth">
        </asp:Calendar>
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server"
            ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
            SelectCommand="SELECT OrderID, OrderDate, ShipCity FROM Orders WHERE (OrderDate BETWEEN @Param1 AND @Param2)">
            <SelectParameters>
                <asp:ControlParameter ControlID="Label1" Name="Param1" PropertyName="Text" />
                <asp:ControlParameter ControlID="Label2" Name="Param2" PropertyName="Text" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
        <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2"
            AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
            DataKeyNames="OrderID">
            <Columns>
                <asp:BoundField DataField="OrderID" HeaderText="OrderID" InsertVisible="False"
                    ReadOnly="True" SortExpression="OrderID" />
                <asp:BoundField DataField="OrderDate" HeaderText="OrderDate"
                    SortExpression="OrderDate" />
                <asp:BoundField DataField="ShipCity" HeaderText="ShipCity"
                    SortExpression="ShipCity" />
            </Columns>
        </asp:GridView>
    </div>
    </form>
 Now what do I need to do to get it to work with a Access dB, so far the DataGrid is empty.
Ralph
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:RalphHxyz
Comment Utility
Darn I turned my back and even what was just working now fails either as a empty GridView or with a error about DateTime to Text string error.
This really should not be so hard, I think I am going to learn PHP or Python instead of these stupid Microsoft methods.
Ralph
0
 

Accepted Solution

by:
RalphHxyz earned 0 total points
Comment Utility
What would be the correct syntax for the WHERE?

WHERE qEmployees_Remote_sub_Enum.DateSubmitted BETWEEN ? AND ?
this fails.
This works but of course there is no date range:

SelectCommand="SELECT qEmployees_Remote_sub_Enum.DateSubmitted, qEmployees_Remote_sub_Enum.SubmissionId, qEmployees_Remote_sub_Enum.Customer, qEmployees_Remote_sub_Enum.Invoice_Number, qEmployees_Remote_sub_Enum.Employees, qEmployees_Remote_sub_Enum.Enum, qEmployees_Remote_sub_Enum.Invoice_Amount, Employees.Rate, ([qEmployees_Remote_sub_Enum].[Invoice_Amount]*[Employees].[Rate]/[qEmployees_Remote_sub_Enum].[Enum]) AS Employees_Pay
FROM Employees, qEmployees_Remote_sub_Enum
ORDER BY qEmployees_Remote_sub_Enum.DateSubmitted, qEmployees_Remote_sub_Enum.SubmissionId;
So when I try to add a date range it fails.
Does the date range need to be surrounded with # (this is a Access (Jet) database)?
0
 

Author Comment

by:RalphHxyz
Comment Utility

"How to Populate a GridView from a Calendar Control Date Range?" Summary
Related questions and progressive answers.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23804580.html (Access Query Syntax)
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23804183.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_23801795.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_23795432.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_23788766.html
http://forums.asp.net/t/1329661.aspx

Doing this in code behind with a unbound DataGrid is very easy and straight forward when the
Date field Type is DateTime.
Doing this with a Bound DataGrid from SQL or Access when the Date field is of DateTime
is very easy and straight forward.
Doing this with a Bound or unbound GridView when the Date field is of TYPE TEXT is a bear.
The first lesson being from GRayL:
"but remember 07/08/09 as a string date is greater than 06/07/10 even though the intended format
was mm/dd/yy - remember you cannot expect a string date to behave as though it were a date.  
Strings sort according to well defined rules and looking like a date ain't one of them!"

The problem was the Date Field being of Type Text!
That solution is to use the SQL Format function, this would apply to both Acccess and SQL!!
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
            DataFile="~/App_Data/WebDB.mdb"
            SelectCommand="SELECT
                    Copy Of dbo_Orders].OrderID,
                    [Copy Of dbo_Orders].OrderDate,
                    [Copy Of dbo_Orders].ShipCity
                    FROM [Copy Of dbo_Orders]
                    WHERE (((Format([OrderDate],'yyyymmdd'))>=
                    Format([Enter StartDate],'yyyymmdd')
                   And
                    (Format([OrderDate],'yyyymmdd'))<=
                    Format([Enter EndDate],'yyyymmdd')))
                   ORDER BY [Copy Of dbo_Orders].OrderID">
            <SelectParameters>
                <asp:ControlParameter ControlID="Label1" Name="OrderDate" PropertyName="Text" />
                <asp:ControlParameter ControlID="Label2" Name="OrderDate2" PropertyName="Text" />
            </SelectParameters>
</asp:AccessDataSource>
The [Copy Of dbo_0rders] table is copied from the SQL NORTHWND database with the
Orders column Type changed to Text from DateTime. If you follow the thread you will also see a [Oders] table
in this table the Orders column was left as DateTime. All of the queries worked with the Orders being DateTime,
so this made it very frustrating.
I was checking the query in Access and seeing it return data BUT I was not paying attention
until I was prompted by Qin Dian Tang to verify my query in the database, then I saw that the query
while returning data was not returning the correct date range (because of the string date mentined above)
.
My thanks to all that helped me solve this, it took a number of days and considerable help.
I sure got some learning from this one.
Ralph
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now