Show 2 pics from each album using SELECT Rank Over Partition by : The Official Microsoft ASP.NET Forums

Reply

xandeq

Member

384 Points

315 Posts

Show 2 pics from each album using SELECT Rank Over Partition by

May 04, 2012 02:22 AM|LINK

Hi,

Im showing 1 picture from each album with the code above:

<asp:ListView ID="lvCategorias" runat="server" DataSourceID="CategoriasSQLDS"
        DataKeyNames="codigo">
        <emptydatatemplate>
            <table runat="server" style="">
                <tr>
                    <td>
                        </td>
                </tr>
            </table>
        </emptydatatemplate>
        <itemtemplate>
            <li runat="server" style="">
                <%--<img src="img/albums.png" style="height:37px" />--%>
                <asp:Image ID="Image1" runat="server" ImageUrl='<%# "~/Public/fotos/" + Eval("foto") %>' Height="100" />
                <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "VerFotos.aspx?codigo_album=" + Eval("codigo") %>' CssClass="23uioy4">
                    &nbsp;&nbsp;<asp:Label ID="tituloLabel" runat="server" Text='<%# Eval("titulo") %>' CssClass="albums-titulo" />
                </asp:HyperLink>
            </li>
        </itemtemplate>
        <layouttemplate>
                        <div ID="itemPlaceholderContainer" runat="server" border="0" style="">
                            <ul ID="itemPlaceholder" runat="server">
                            </ul>
                        </div>
        </layouttemplate>
    </asp:ListView>
    <asp:SqlDataSource ID="CategoriasSQLDS" runat="server" ConnectionString="<%$ ConnectionStrings:nutrisinalConnectionString %>"
        SelectCommand="SELECT *
		FROM
		(
		SELECT
		Albums.titulo,
		Albums.codigo as album,
		Fotos.codigo_album,
		Fotos.codigo,
		Fotos.foto,
		Rnk = RANK() Over(Partition by Albums.codigo Order by Fotos.codigo DESC)
		FROM Fotos
		INNER JOIN Albums ON Fotos.codigo_album = Albums.codigo
		) AS x
		WHERE Rnk = 1">
    </asp:SqlDataSource>

What if i need to show 2 pics from each album. How would i do ?

Thanks.

Reply

limno

All-Star

117314 Points

7997 Posts

Moderator

MVP

Re: Show 2 pics from each album using SELECT Rank Over Partition by

May 04, 2012 02:33 AM|LINK

WHERE Rnk <=2

Limno

Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Reply

xandeq

Member

384 Points

315 Posts

Re: Show 2 pics from each album using SELECT Rank Over Partition by

May 04, 2012 10:01 AM|LINK

Ok. And how do i show the album title and the 2 pics in the Listview ?

Reply

tusharrs

Contributor

3230 Points

668 Posts

Re: Show 2 pics from each album using SELECT Rank Over Partition by

May 04, 2012 10:26 AM|LINK

go to design view

at the top right corner of listview there is a right arrow

click on it

options displyed , in that select configure listview

select any layout you want and click on ok

Regards

( Mark as Answer if it helps you out )

View my Blog

Reply

xandeq

Member

384 Points

315 Posts

Re: Show 2 pics from each album using SELECT Rank Over Partition by

May 04, 2012 10:29 AM|LINK

So im showing ONE pic in this part of the code:

<itemtemplate>
            <li runat="server" style="">
                <%--<img src="img/albums.png" style="height:37px" />--%>
                <asp:Image ID="Image1" runat="server" ImageUrl='<%# "~/Public/fotos/" + Eval("foto") %>' Height="100" />
                <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "VerFotos.aspx?codigo_album=" + Eval("codigo") %>' CssClass="23uioy4">
                    &nbsp;&nbsp;<asp:Label ID="tituloLabel" runat="server" Text='<%# Eval("titulo") %>' CssClass="albums-titulo" />
                </asp:HyperLink>
            </li>
        </itemtemplate>

Do you know how to show a second image in the itemtemplate ?

Thanks