?
Solved

Populate a DataGrid from a Calendar Control Date Range

Posted on 2008-10-05
6
Medium Priority
?
815 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:Ramesh Srinivas
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
Industry Leaders: 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!

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month15 days, 1 hour left to enroll

840 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