Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Unanswered: Select MAX ID if no Request passed

    Working with .NET and an Access db.

    I hope this makes sense. I want to pull the largest record from an Access dB if no parameter is passed in a QueryString.

    Under normal situations, my passed querystring might look like this; http://www.XYZ.com/videos.aspx?id=1

    What I would like to do is have the page "http://www.XYZ.com/videos.aspx" load the MAX ID record by default. I tried this, but it won't even load the page portion that is suppose to contain the data and returned no errors;

    Here is the Query;
    SELECT *, (SELECT MAX(ID) FROM myMovies) as Max_IDFROM [myMovies] WHERE ([ID] = ?) '"Select all the record data of either the passed querystring value OR get and use the MAX ID of the records if no querystring passed"

    My ASP.NET code;

    <asp:AccessDataSource id="AccessDataSource2" runat="server" DataFile="_database/92592_Web.mdb" SelectCommand="SELECT *, (SELECT MAX(ID) FROM myMovies) as Max_IDFROM [myMovies] WHERE ([ID] = ?)" CancelSelectOnNullParameter="False">
    <SelectParameters>
    <asp:QueryStringParameter Name="ID" QueryStringField="ID" Type="Int32" />
    </SelectParameters>
    </asp:AccessDataSource>

  2. #2
    Join Date
    Aug 2010
    Posts
    3
    OK, this works, (well sort of, it not only returns the MAX ID but the selected record as well), but could someone clarify or even post a better solution?

    "SELECT * FROM MyMovies WHERE ID = ? OR (ID = (SELECT MAX(ID) FROM MyMovies))"

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT * 
    FROM MyMovies WHERE ID = ? OR ID = (SELECT MAX(ID) FROM MyMovies WHERE ? IS NULL)
    I don't know if that is allowed using your provider.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, this might be better:
    Code:
    SELECT * 
    FROM MyMovies WHERE ID = NZ(?, (SELECT MAX(ID) FROM MyMovies))
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2010
    Posts
    3
    I ended up using this SQL;

    SELECT * FROM MyMovies WHERE ID = @ID OR (@ID IS NULL AND ID = (SELECT MAX(ID) FROM MyMovies))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •