Solved

Populating GridView from Calendar Date Range Reduxe

Posted on 2008-10-07
8
1,542 Views
Last Modified: 2013-11-07
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

0
Comment
Question by:RalphHxyz
  • 4
8 Comments
 

Author Comment

by:RalphHxyz
ID: 22664064
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22680461
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() & "#";




0
 

Author Comment

by:RalphHxyz
ID: 22680617
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
0
 

Author Comment

by:RalphHxyz
ID: 22686549
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
0
 

Accepted Solution

by:
RalphHxyz earned 0 total points
ID: 22695468

"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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now