locked
Using a Subquery value as Default in QueryStringParameter RRS feed

  • Question

  • User793909578 posted

    Using this call;


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


    How can I use "Max_ID" as my DefaultValue?  So that basically if the page gets passed without a parameter, the SQL will use the Max(ID) as a default.  Can I use Max_ID as a DefaultValue?


    I'm an old .asp developer still on the learning curve of .NET so some of this stuff is a little daunting.  In .asp doing something like this was pretty easy...

    Tuesday, June 29, 2010 6:30 PM

All replies

  • User-1199946673 posted

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

    Don't forget to set the CancelSelectOnNullParameter Property of the DataSource to false. And don't specify a DefaultValue of the QueryStringParameter, otherwise this value is used when the querystring cannot be found.

    More on optional parameters:

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria 

    In .asp doing something like this was pretty easy...

    really? In classic ASP I would use the same SQL statement!

    Tuesday, June 29, 2010 8:09 PM
  • User793909578 posted

    OK, this works to get the MaxID if no value is passed;

    <asp:AccessDataSource id="AccessDataSource2" runat="server" DataFile="_database/92592_Web.mdb" SelectCommand="SELECT * FROM MyMovies WHERE ID = @ID OR (@ID IS NULL AND ID = (SELECT MAX(ID) FROM MyMovies))" CancelSelectOnNullParameter="False">
                <SelectParameters>
                    <asp:QueryStringParameter Name="ID" QueryStringField="ID" Type="Int32" />
                </SelectParameters>
            </asp:AccessDataSource>


    Now how can I use ALL the records with the same DataSource?  Can I nest another parameterized query inside this to return ALL records as well as the matching record?

    Like this maybe?  This doesn't work obviously;

    <asp:AccessDataSource id="AccessDataSource2" runat="server" DataFile="_database/92592_Web.mdb" SelectCommand="SELECT ID AS theID, fldShortTitle AS sTitle, (SELECT * FROM MyMovies WHERE ID = @ID OR (@ID IS NULL AND ID = (SELECT MAX(ID) FROM MyMovies))) FROM MyMovies" CancelSelectOnNullParameter="False">

    What I want to do is use theID and sTitle in a Repeater object;

    <ItemTemplate>
                 <a href="videos.aspx?id=<%# DataBinder.Eval(Container.DataItem, "theID") %>" style="margin-left:0px"><%# DataBinder.Eval(Container.DataItem, "sTitle") %></a><br />
                                </ItemTemplate>

    Tuesday, June 29, 2010 8:44 PM
  • User-1199946673 posted

    Now how can I use ALL the records with the same DataSource?  Can I nest another parameterized query inside this to return ALL records as well as the matching record?
     

     

    I really don't understand what you're saying? When you specify the ID, the record is returned. When you don't specify and ID, the record with the highest ID is returned. When do you want to return all records?

    How about simply adding another OR in the WHERE clause:

    WHERE ID = @ID OR (@ID IS NULL AND ID = (SELECT MAX(ID) FROM MyMovies)) OR ( <logic to return all records>  )

    Wednesday, June 30, 2010 3:01 AM
  • User793909578 posted

    Here is what I would like the SQL to do;


    "SELECT * FROM myVideos table WHERE ID = the passed QueryString value OR Max(ID) of myVideos, AND SELECT everything from myVideos"


    So actually, there are two queries,  The first SELECT is used to pull a single record, while the second SELECT is used to create a Repeater Template, basically a list of all the records.  Here is a screen shot of what I have working, although it's bad code since it makes two calls to the same database on the same page.  The left hand list is all the records in the db, and the right hand Header, the text and the actual video link and image is data from the same table. I would prefer to make a simgle SQL to retrieve all the relevent data.  Does that help?


    Wednesday, June 30, 2010 3:32 AM