locked
Query Date RRS feed

  • Question

  • User-7389755 posted

    I have two date variables one is stored in a table and the other is a sesison date

    I want to filter records in a table by these two dates for example:

    Show all records which have a status = null and Date < SessionDate

    But the query gives an error "No value given for one of more paremeters.  It not the Status part as I can delete records based on this query along.

     

    Here is the code I am using:

            <asp:AccessDataSource ID="AccessDataSource1" runat="server"
                DataFile="~/App_Data/shop.mdb"
                DeleteCommand="DELETE FROM Orders WHERE (Status IS NULL) AND ([Date] < SessionDate)"
                SelectCommand="SELECT * FROM [Orders] WHERE ([SessionID] = ?)">
                <SelectParameters>
                    <asp:SessionParameter Name="SessionID" SessionField="SessionID" Type="String" />
                </SelectParameters>
                <DeleteParameters>
                    <asp:SessionParameter Name="SessionDate" SessionField="SessionDate" />
                </DeleteParameters>
            </asp:AccessDataSource>

     

    Any clues why this is not working?

     

    Tuesday, March 24, 2009 7:14 AM

Answers

  • User-7389755 posted

     This is what worked for me:

     

    "DELETE FROM Orders WHERE (Status IS NULL) AND ([Date] < ?)" 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 24, 2009 7:12 PM

All replies

  • User-1832689190 posted


    Hi,
      You can set the query like follows:

     <asp:AccessDataSource ID="AccessDataSource1" runat="server"
                DataFile="~/App_Data/shop.mdb"
                DeleteCommand="DELETE FROM Orders WHERE (Status IS NULL) AND ([Date] < Convert(varchar, SessionDate,102))"
                SelectCommand="SELECT * FROM [Orders] WHERE ([SessionID] = ?)">
                <SelectParameters>
                    <asp:SessionParameter Name="SessionID" SessionField="SessionID" Type="String" />
                </SelectParameters>
                <DeleteParameters>
                    <asp:SessionParameter Name="SessionDate" SessionField="SessionDate" />
                </DeleteParameters>
            </asp:AccessDataSource>

    This may help you.

    Tuesday, March 24, 2009 7:38 AM
  • User-7389755 posted

    Thanks for the quick response.

     

    Done what you suggest by I get an error when I execute it:

    Error:

    Undifined Function 'Convert'  in expression

    Any other clues why this is not working?

    Tuesday, March 24, 2009 7:51 AM
  • User-1199946673 posted

    Your commands are fine, the problem are you parameters:

    <asp:SessionParameter Name="SessionDate" SessionField="SessionDate" Type="DateTime" />

    Tuesday, March 24, 2009 2:58 PM
  • User-1199946673 posted

                DeleteCommand="DELETE FROM Orders WHERE (Status IS NULL) AND ([Date] < Convert(varchar, SessionDate,102))"

    This is an Access Database, not SQL SERVER so this won't work!

    Tuesday, March 24, 2009 2:59 PM
  • User-7389755 posted

     This is what worked for me:

     

    "DELETE FROM Orders WHERE (Status IS NULL) AND ([Date] < ?)" 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 24, 2009 7:12 PM