I've been trying everything, but I just can't seem to get one query to function properly. My goal is to retrieve information from the SQL Server database in two separate queries - one for album names (based on URL parameter) and another for song details.
Below is the code snippet where I'm utilizing Album in the WHERE clause of both DataLists and their datasources. However, the second WHERE clause is causing an error.
<h2 class="w3-container" style="color:blue;">Album</h2>
<strong><p class="w3-container" id="albumParam" ></p></strong>
<script>
type = "text/javascript"
document.getElementById("albumParam").innerHTML = location.search.substring(1).substring(6, 150).replaceAll("+", " ").replaceAll("%2f", "/").replace("%26", "&").replaceAll("%2b", "+").replace("%2c", ",").replaceAll("%27", "'");
</script>
<asp:SqlDataSource ID="SqlDataSourceAlbum" runat="server"
ConnectionString="<%$ ConnectionStrings:FMConnectionString %>" ProviderName="System.Data.SqlClient"
SelectCommand="SELECT Album FROM dbo.c_mymusic_songs WHERE (Album = @Album) ORDER BY Track"></asp:SqlDataSource>
<asp:DataList class="w3-container" ID="DataListAlbum" CellSpacing="3" runat="server" DataSourceID="SqlDataSourceAlbum" ItemStyle-Wrap="false">
<ItemTemplate>
<asp:HyperLink ID="HyperLinkAlbum" runat="server" Text='<%# Eval("Album") %>' />
</ItemTemplate>
</asp:DataList>
<strong><p class="w3-container" id="AlbumParam" ></p></strong>
<h2 class="w3-container" style="color:blue;">Songs</h2>
<script>
type = "text/javascript"
document.getElementById("Param").innerHTML = location.search.substring(1).substring(6, 150).replaceAll("+", " ").replaceAll("%2f", "/").replace("%26", "&").replaceAll("%2b", "+").replace("%2c", ",").replaceAll("%27", "'");
</script>
<asp:SqlDataSource ID="SqlDataSourceSongs" runat="server"
ConnectionString="<%$ ConnectionStrings:FMConnectionString %>" ProviderName="System.Data.SqlClient"
SelectCommand="SELECT Artist, Album, Track, Title FROM dbo.c_mymusic_songs WHERE (Album = @Album) ORDER BY Track">
<SelectParameters>
<asp:Parameter Name="Album" Type="string" DefaultValue=" " />
</SelectParameters>
</asp:SqlDataSource>
<asp:DataList ID="DataListSongs" runat="server" DataSourceID="SqlDataSourceSongs" RepeatColumns="3"
CellSpacing="3" RepeatLayout="Table">
<ItemTemplate>
<table class="table">
<tr>
<td>
<%# Eval("Track") %><br />
</td>
<td>
<%# Eval("Title")%>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
The first query functions correctly, however, the 2nd select where clause is triggering an error:
Server Error in '/' Application.
Must declare the scalar variable "@Album".
If I remove the WHERE clause in the second select query, all songs are displayed as expected. But, I require the WHERE clause for specific filtering.
<asp:SqlDataSource ID="SqlDataSourceSongs" runat="server"
ConnectionString="<%$ ConnectionStrings:FMConnectionString %>" ProviderName="System.Data.SqlClient"
SelectCommand="SELECT Artist, Album, Track, Title FROM dbo.c_mymusic_songs ORDER BY Track">
</asp:SqlDataSource>
How can I define the scalar variable such that the second WHERE works without any errors, enabling me to display only the songs based on the url album parameter?