Query Date

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?

 

Answer 1

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.

Answer 2

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?

Answer 3

User-1199946673 posted

Your commands are fine, the problem are you parameters:

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

Answer 4

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!

Answer 5

User-7389755 posted

 This is what worked for me:

 

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