Solved

Populate a DataGrid from a Calendar Control Date Range

Posted on 2008-10-05
6
807 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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