locked
Access Database SQL Query between two dates Datagrid RRS feed

  • Question

  • User828474466 posted

    Hi

    Could someone help me , by explaining where I'm going wrong with this SQL Query of an Access Database which populates a Datagrid,

    From a calendar selection the user can select a From and To Date and I want the SQL Query to ONLY pick up the dates between these two values, the selection is fine it seems to be the sql query which is the problem  ... (I have added dates rather than the text value to make it easier to get working ) the following query wil pick up dates outside of the selected values 01/08/2010 , 02/08/2010, etc

     

    SELECT  COUNT(*) AS Date_Installed, Date_Installed AS Expr1, Installed_By FROM Buildprocess WHERE (Date_Installed BETWEEN '20/08/2010' AND ' 25/08/2010') GROUP BY Date_Installed, Installed_By

     

    Any Help would be geat !

    Cheers

    Wednesday, August 25, 2010 1:11 PM

Answers

  • User828474466 posted

    That did it... thanks very much for your help Smile 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 28, 2010 11:38 AM

All replies

  • User-1148431695 posted

    Hi,

    May I know what calendar control you are using? Are you directly passing the date value to SQL query?

    I think the issue seems to be with the date format. SQL Server may encounter issue while converting the date you have mentioned.

    If you try '2010-08-20' and '2010-08-25', it should work fine. You may need to convert the date from your calendar control and then pass it to SQL.

    Thanks,


    Wednesday, August 25, 2010 1:41 PM
  • User-1199946673 posted

    Use parameterized queries:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    and read this for more info about Access and Date(times)

    http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET 

    Wednesday, August 25, 2010 2:42 PM
  • User828474466 posted

    Hi

    Thanks for your reply , I am usinging the Calendar Function within Visual Webdeveloper, but my biggest issue is the SQL Query, as its not returning the correct search values , as I said I sak for between "10/08/2010" and "19/08/2010" and I receive results including July "07" , the results i am after are a list of Dates "Dates_Installed" displaying a Count of how many Installed by Each Engineer "Installed_By" - Between the Date Criteria...

    So Far this gives me a very rough result

    Installed_By Expr1001 Expr1
    5 21/07/2010
    5 22/07/2010
    3 23/07/2010
    4 MooreD 20/8/2010
    2 MooreD 23/8/2010
    1 MooreD 24/8/2010
    1 yorkj 20/8/2010

    Query Is

     

    SELECT COUNT(*) AS Installed_By, Installed_By, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN '20-08-2010' AND '25-08-2010') GROUP BY Installed_By, Date_Installed

     

    From Table "Build_Process

     

    Thanks

     

    Andy

    Thursday, August 26, 2010 5:42 AM
  • User-1199946673 posted

    Did you read the articles I referred to?

     "It is not uncommon to find that people are trying to work with dates and times stored in TEXT fields. The problem with this approach is that any sorting on dates in TEXT fields will only be done alphabetically because they will be treated as strings."

    My guess is that this is exactly what you're doing! So once again, read the articles, use datetime fields for storing dates and use parameterized queries.... 

    Thursday, August 26, 2010 6:22 AM
  • User828474466 posted

    Yes I will read the article and apreciate I need to work this out myself but  what i dont understand is why if I run the query with actual values in it  as per my earlier message, it still returns incorrect values  

    SELECT COUNT(*) AS Installed_By, Installed_By, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN '20/8/2010' AND '25/08/2010') GROUP BY Installed_By, Date_Installed

     

    cheers

     

    Andy

    Thursday, August 26, 2010 6:29 AM
  • User-1199946673 posted

    Do you store the dates into Text fields? 

    In that case "21/07/2010" is larger as "20/08/2010" and smaller as "25/08/2010" 

     

    Thursday, August 26, 2010 6:35 AM
  • User828474466 posted

    Yes they are stored as Text Fields  

    Thursday, August 26, 2010 7:14 AM
  • User-1199946673 posted

    Yes they are stored as Text Fields  

     

    Exactly what I expected. The solution of your problem can be found in the articles....

    Thursday, August 26, 2010 7:23 AM
  • User828474466 posted

    ok I now have the query working as per the links you kindly sent me , It works fine if I add manual dates into the  but when I try adding the calendar date selection for the between 2 dates  into the sql query I get a error :

    Syntax error (missing operator) in query expression '(Date_Installed BETWEEN ([Date1]=?) AND ([Date2]=?)  GROUP BY Installed_By, Date_Installed'

    I am using the calender feature from within the Viusual  Web Developer , the back end code I have is :

        Sub calDate_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim dt1 As DateTime
            dt1 = Convert.ToDateTime(CalDate.SelectedDate.ToString("d"))
    
            Date1.Text = dt1 
        End Sub
     
        Sub calDate2_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim dt2 As DateTime
            dt2 = Convert.ToDateTime(CalDate2.SelectedDate.ToString("d"))
    
            Date2.Text = dt2 
        End Sub


    Can you help me , Is it because the selected date returns a value of say 17/08/2010 instead of 17/08/2010 00:00:00 ?

     

    Cheers

    Andy

    Thursday, August 26, 2010 3:11 PM
  • User-1199946673 posted

    Date_Installed BETWEEN ([Date1]=?) AND ([Date2]=?)  
     

     

    This should be:

    Date_Installed BETWEEN ? AND ?

    OR

    Date_Installed BETWEEN @Date1 AND @Date2

    Thursday, August 26, 2010 3:27 PM
  • User828474466 posted

    Thank you for your reply , I have tried both of your examples, but I'm still getting errors

    Option1

    Syntax error (missing operator) in query expression '(Date_Installed BETWEEN @Date1 and @Date2
    GROUP BY Installed_By, Date_Installed'.

    Option2

    Syntax error (missing operator) in query expression '(Date_Installed BETWEEN  ? and ?
    GROUP BY Installed_By, Date_Installed'.

     

    Any help would be great .....

     

    Thursday, August 26, 2010 4:11 PM
  • User-1199946673 posted

    Can you please show the complete SQL command? 

    Thursday, August 26, 2010 4:14 PM
  • User828474466 posted

    The ASPX PAge is as follows :

    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="runrate.aspx.vb" Inherits="UserDetails.runrate" %>
    
    <%@ Register assembly="System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" namespace="System.Web.UI.DataVisualization.Charting" tagprefix="asp" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title></title>
        <script type="text/javascript">
            function popupCalendar() {
                var dateField = document.getElementById('dateField');
    
                // toggle the div
                if (dateField.style.display == 'none')
                    dateField.style.display = 'block';
                else
                    dateField.style.display = 'none';
            }
    </script>
    <script type="text/javascript">
        function popupCalendar2() {
            var dateField2 = document.getElementById('dateField2');
    
            // toggle the div
            if (dateField2.style.display == 'none')
                dateField2.style.display = 'block';
            else
                dateField2.style.display = 'none';
        }
    </script>
        <style type="text/css">
            .style1
            {
                text-align: left;
            }
            .style3
            {
                width: 100%;
            }
            .style4
            {
                font-family: Arial, Helvetica, sans-serif;
            }
        </style>
    </head>
    <body>
        <form id="form1" runat="server">
        <div class="style1">
        <div class="style1">
        
    
            <div class="style1">
        
    
            <br />
            </div>
        
        </div>
         
            <div class="style1">
             From:   <asp:TextBox ID="Date1" runat="server"></asp:TextBox>
              
    <img src="\images\cal.jpg" onclick="popupCalendar()" />
         
       
            To:    <asp:TextBox ID="Date2" runat="server"></asp:TextBox>     
    <img src="\images\cal.jpg" onclick="popupCalendar2()" />
         
         
         <div id="dateField" style="display:none;">
      <asp:Calendar id="CalDate"  OnSelectionChanged="calDate_SelectionChanged"   Runat="server" />
    </div>
              <div id="datefield2" style="display:none;">
      <asp:Calendar id="CalDate2"   OnSelectionChanged="calDate2_SelectionChanged"   Runat="server" />
    </div>
              
         
            
         
                <br class="style4" />
                <br />
         
                <br />
                <table class="style3">
                    <tr>
                        <td>
        
            <asp:ListView ID="ListView1" runat="server" DataSourceID="SqlDataSource1">
                <ItemTemplate>
                    <tr style="background-color: #FFFBD6;color: #333333;">
                        <td>
                            <asp:Label ID="Installed_ByLabel" runat="server" 
                                Text='<%# Eval("Installed_By") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Expr2Label" runat="server" Text='<%# Eval("Expr2") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Expr1Label" runat="server" Text='<%# Eval("Expr1") %>' />
                        </td>
                    </tr>
                </ItemTemplate>
                <AlternatingItemTemplate>
                    <tr style="background-color: #FAFAD2;color: #284775;">
                        <td>
                            <asp:Label ID="Installed_ByLabel" runat="server" 
                                Text='<%# Eval("Installed_By") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Expr2Label" runat="server" Text='<%# Eval("Expr2") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Expr1Label" runat="server" 
                                Text='<%# Eval("Expr1") %>' />
                        </td>
                    </tr>
                </AlternatingItemTemplate>
                <EmptyDataTemplate>
                    <table runat="server" 
                        
                        style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;">
                        <tr>
                            <td>
                                No data was returned.</td>
                        </tr>
                    </table>
                </EmptyDataTemplate>
                <InsertItemTemplate>
                    <tr style="">
                        <td>
                            <asp:Button ID="InsertButton" runat="server" CommandName="Insert" 
                                Text="Insert" />
                            <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" 
                                Text="Clear" />
                        </td>
                        <td>
                            <asp:TextBox ID="Installed_ByTextBox" runat="server" 
                                Text='<%# Bind("Installed_By") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="Expr2TextBox" runat="server" 
                                Text='<%# Bind("Expr2") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="Expr1TextBox" runat="server" 
                                Text='<%# Bind("Expr1") %>' />
                        </td>
                    </tr>
                </InsertItemTemplate>
                <LayoutTemplate>
                    <table runat="server">
                        <tr runat="server">
                            <td runat="server">
                                <table ID="itemPlaceholderContainer" runat="server" border="1" 
                                    
                                    style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;font-family: Verdana, Arial, Helvetica, sans-serif;">
                                    <tr runat="server" style="background-color: #FFFBD6;color: #333333;">
                                        <th runat="server">
                                            Count</th>
                                        <th runat="server">
                                            Engineer</th>
                                        <th runat="server">
                                            Date</th>
                                    </tr>
                                    <tr ID="itemPlaceholder" runat="server">
                                    </tr>
                                </table>
                            </td>
                        </tr>
                        <tr runat="server">
                            <td runat="server" 
                                
                                
                                style="text-align: center;background-color: #FFCC66;font-family: Verdana, Arial, Helvetica, sans-serif;color: #333333;">
                            </td>
                        </tr>
                    </table>
                </LayoutTemplate>
                <EditItemTemplate>
                    <tr style="background-color: #FFCC66;color: #000080;">
                        <td>
                            <asp:Button ID="UpdateButton" runat="server" CommandName="Update" 
                                Text="Update" />
                            <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" 
                                Text="Cancel" />
                        </td>
                        <td>
                            <asp:TextBox ID="Installed_ByTextBox" runat="server" 
                                Text='<%# Bind("Installed_By") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="Expr2TextBox" runat="server" 
                                Text='<%# Bind("Expr2") %>' />
                        </td>
                        <td>
                            <asp:TextBox ID="Expr1TextBox" runat="server" 
                                Text='<%# Bind("Expr1") %>' />
                        </td>
                    </tr>
                </EditItemTemplate>
                <SelectedItemTemplate>
                    <tr style="background-color: #FFCC66;font-weight: bold;color: #000080;">
                        <td>
                            <asp:Label ID="Installed_ByLabel" runat="server" 
                                Text='<%# Eval("Installed_By") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Expr2Label" runat="server" Text='<%# Eval("Expr2") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Expr1Label" runat="server" 
                                Text='<%# Eval("Expr1") %>' />
                        </td>
                    </tr>
                </SelectedItemTemplate>
            </asp:ListView>
        
                        </td>
                        <td>
        
        <asp:Chart ID="Chart1" runat="server" DataSourceID="SqlDataSource1" Height="388px" 
                Width="473px">
            <Legends>
                <asp:Legend Name="Legend1" Title="Pc Run Rate">
                </asp:Legend>
            </Legends>
            <Series>
                <asp:Series Name="Series1" XValueMember="Expr2" YValueMembers="Installed_By" 
                    ChartType="StackedColumn" Legend="Legend1" LegendText="Pc Installs" 
                    IsValueShownAsLabel="True">
                </asp:Series>
            </Series>
            <ChartAreas>
                <asp:ChartArea Name="ChartArea1">
                    <Area3DStyle Enable3D="True" />
                </asp:ChartArea>
                
           
            </ChartAreas>
        </asp:Chart>
        
                        </td>
                    </tr>
                </table>
         
        <br />
        
        
            </div>
        
        
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:refreshConnectionString2 %>" 
            ProviderName="<%$ ConnectionStrings:refreshConnectionString2.ProviderName %>" 
         
                
                
                
                
                
                SelectCommand="SELECT COUNT(*) AS Installed_By, Installed_By AS Expr2, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN @Date1 and @Date2
     GROUP BY Installed_By, Date_Installed">
       
        </asp:SqlDataSource>
        
            <div class="style1">
        
            <br />
        
        <br />
         
            </div>
         
        </div>
         
        </form>
    </body>
    </html>
    

    The Code PAge is :

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
           
        End Sub
        Sub calDate_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim dt1 As DateTime
            dt1 = Convert.ToDateTime(CalDate.SelectedDate.ToString("d"))
    
            Date1.Text = dt1
        End Sub
     
        Sub calDate2_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim dt2 As DateTime
            dt2 = Convert.ToDateTime(CalDate2.SelectedDate.ToString("d"))
    
            Date2.Text = dt2
        End Sub


    Cheers Andy


     

    Thursday, August 26, 2010 5:27 PM
  • User-1199946673 posted

    line 255 was sufficient Wink

    Remove the ( AFTER WHERE!!!

    SelectCommand="SELECT COUNT(*) AS Installed_By, Installed_By AS Expr2, Date_Installed AS Expr1 FROM Buildprocess WHERE Date_Installed BETWEEN @Date1 and @Date2  GROUP BY Installed_By, Date_Installed" 

    Thursday, August 26, 2010 5:49 PM
  • User828474466 posted

    Sorry I thought I would give you everything .... Ok I have corrected my typo and I no receive

     

    No value given for one or more required parameters.

     

    Is this because it is looking for a Date1 Date2 value as the page loads ?

     

    Thanks

     

    Andy 

    Friday, August 27, 2010 4:53 AM
  • User-1199946673 posted

    Is this because it is looking for a Date1 Date2 value as the page loads ?
     

    Yes, because you didn't specify any value for the parameter. If I understand correctly, you want to enter the dates in the calendar controls CalDate and CalDate2? In that case, use control parameters:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"    
      ConnectionString="<%$ ConnectionStrings:refreshConnectionString2 %>"    
      ProviderName="<%$ ConnectionStrings:refreshConnectionString2.ProviderName %>"    
      SelectCommand="SELECT COUNT(*) AS Installed_By, Installed_By AS Expr2, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN @Date1 and @Date2 GROUP BY Installed_By, Date_Installed">   
      <SelectParameters>
        <asp:ControlParameter ControlID="CalDate" PropertyName="SelectedDate" Name="Date1" Type="DateTime" />
        <asp:ControlParameter ControlID="CalDate2" PropertyName="SelectedDate" Name="Date2" Type="DateTime" />
      </SelectParameters> 
    </asp:SqlDataSource> 
    


     

    Friday, August 27, 2010 1:52 PM
  • User828474466 posted

    That did it... thanks very much for your help Smile 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 28, 2010 11:38 AM