?
Solved

Populate a DataGrid from a Calendar Control Date Range

Posted on 2008-10-05
6
Medium Priority
?
809 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 11

Expert Comment

by:saleek
ID: 22648277
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
ID: 22653980
Yes I need to use a Calendar Control in DayWeekMonth mode.
Ralph
0
 

Author Comment

by:RalphHxyz
ID: 22654402
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
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 

Author Comment

by:RalphHxyz
ID: 22654604
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
ID: 22654894
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
ID: 22695470

"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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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