Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Populating ASP,NET GridView form Calendar Control Dat Range re-reduxe

Posted on 2008-10-09
3
Medium Priority
?
1,316 Views
Last Modified: 2013-11-26
I am using a Orders table copied from SQL NORTHWND database to Access.
This "allmost" works except every record form the Oders table is returned
or every record for a month no matter the yerar is returned.
But at least I am finally populating the DataGrid with something.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        System.Web.UI.WebControls.Calendar cal;
        DateTime startdate, enddate;
        cal = (System.Web.UI.WebControls.Calendar)sender;
        startdate = cal.SelectedDates[0];
        enddate = cal.SelectedDates[cal.SelectedDates.Count - 1];
        Label1.Text = startdate.ToShortDateString();
        Label2.Text = enddate.ToShortDateString();
    }    
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Calendar date Range</title>
</head>
<body>
    <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:Label ID="Label2" runat="server" Text="Label"></asp:Label>
        <br />            
        <br />
        <asp:GridView ID="GridView1"
            runat="server"
            AllowPaging="False"
            AllowSorting="True"
            AutoGenerateColumns="False"
            DataSourceID="AccessDataSource1">
            <Columns>
                <asp:BoundField DataField="OrderID" HeaderText="OrderID" InsertVisible="False"
                    SortExpression="OrderID" />
                <asp:BoundField DataField="OrderDate" HeaderText="OrderDate"
                    SortExpression="OrderDate" />
                <asp:BoundField DataField="ShipCity" HeaderText="ShipCity"
                    SortExpression="ShipCity" />
            </Columns>
        </asp:GridView>        
 <asp:AccessDataSource ID="AccessDataSource1" runat="server"
            DataFile="~/App_Data/WebDB.mdb"
            SelectCommand="SELECT OrderID, OrderDate, ShipCity FROM [Copy Of dbo_Orders] WHERE ([OrderDate] BETWEEN ? AND ?)">
            <SelectParameters>
                <asp:ControlParameter ControlID="Label1" Name="OrderDate" PropertyName="Text" />
                <asp:ControlParameter ControlID="Label2" Name="OrderDate2" PropertyName="Text" />
            </SelectParameters>
</asp:AccessDataSource>
    </div>
    </form>
</body>
</html>
------------------------------------------------------------------------------------------------------------------------
Would it help to run against a query instead of the Orders table?
How would I pass the date range to the query?

Thanks for the help,
Ralph
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
  • 2
3 Comments
 
LVL 7

Expert Comment

by:technofile
ID: 22689953
try replacing your AccessDataSource with the one below
---------------------------------------------------------------------
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
            DataFile="~/App_Data/WebDB.mdb"
            SelectCommand="SELECT OrderID, OrderDate, ShipCity FROM [Copy Of dbo_Orders] WHERE ([OrderDate] BETWEEN datestart AND dateend)">
            <SelectParameters>
                <asp:ControlParameter ControlID="Label1" Name="datestart" PropertyName="Text" />
                <asp:ControlParameter ControlID="Label2" Name="dateend" PropertyName="Text" />
            </SelectParameters>
</asp:AccessDataSource>
0
 
LVL 7

Expert Comment

by:technofile
ID: 22689986
you can not have orderdate used as a column name and parameter named so I named them datestart and dateend also you have to tell it what to compare it to, ie that is why you have the variables there not question marks. I didn't actually have time to test it so let me know if that solves your problem or not, if not copy the code into the attach code snippet so I can copy and paste, and include all the code.
0
 

Accepted Solution

by:
RalphHxyz earned 0 total points
ID: 22695459

"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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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