asked on

Populating GridView from Calendar Date Range Reduxe

So let me rephrase my question:
This doesn't work why? What do i need to do?
If I do this with a unbound GridView in a codebehind page it works great.
But I need Paging and Sorting which are apparently a real bear to implement programatically.
I have Paging working but Sorting is another story so I figured I would just do it with a Bound
DataGrid so i could use the built in Paging and Sorting, simple right.
The database is Access not SQL.
Here is the Error:
Data type mismatch in criteria expression.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

<%@ 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="True" 
            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 dbo_Orders WHERE (OrderDate BETWEEN ? AND ?)">
            <SelectParameters>
                <asp:ControlParameter ControlID="Label1" Name="?" PropertyName="Text" />
                <asp:ControlParameter ControlID="Label2" Name="?" PropertyName="Text" />
            </SelectParameters>
        </asp:AccessDataSource>
    </div>
    </form>
</body>
</html>

Open in new window

RalphHxyz

ASKER

If I make the OrderDate column Text instead of DateTime then I do not get any errors but the GridView is not populated.
I need to be working with OrderDate as a Text string not DateTime!!
Thanks for the help.
Ralph

mbizup

I'm not real familiar with ASP.net, but I beleive you are getting this error because Access needs # delimiters when working with Dates.

For example, if you displayed a messagebox showing your resulting SelectCommand after inserting the dates it should look like this:

SELECT OrderID, OrderDate, ShipCity FROM dbo_Orders WHERE (OrderDate BETWEEN #10/1/08# AND #10/9/08#)

Give this a try for lines 12 and 13 (double checking the concatenation operators that I am using. &'s are used in VBA):

Label1.Text = "#" & startdate.ToShortDateString() & "#";
Label2.Text = "#" & enddate.ToShortDateString() & "#";

RalphHxyz

ASKER

mbizup, thank you for your reply. You would be correct if I was doing this in a code behind page programatically. On a .aspx page one can not use #.
I wonder if I am posting to the wrong Group I thought I posted to ASP.NET and the
only reply I get is from someone who does not work in ASP.NET.
Are there no experts in ASP.NET?
Ralph

RalphHxyz

ASKER

WhackAMod, please resend. Especially to the ASP.NET "experts".
It would be great, and have saved me a lot of time if someone would just say
"You can't do that" so no responding has ever tired to populate a GridView form
a date range in a .aspx page. Doing this from a code behind unbound DataView is easy.
Tahanks,
Ralph

ASKER CERTIFIED SOLUTION

RalphHxyz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial