C# ASP.Net - DataList Changing Images On The Fly

Question

I have a simple DataList that is getting text data from one field in a database and displaying it horizontally in the lblProductName label. This works great. In my database I have 4 records and it displays 1 row with 4 columns of data in a table in the DataList. Perfect.

The harder part. Based on another query/logic I want to display an image under the lblProductName label in the DataList; either a switchon.jpg or a switchoff.jpg. This I have no idea how to do since I'm binding the DataList with the original query to the database.

Here is my Datalist.

    <asp:DataList ID="dlstProductDataList" runat="server" RepeatColumns = "5" CellPadding = "10" HorizontalAlign ="Center" RepeatDirection="Horizontal" Width="100%">
        <ItemTemplate>
            <div class="ProductLinks">
                <asp:Label ID="lblProductName" runat="server" Text='<%# Eval("ProductName") %>' /><br />
                <asp:Image ID="imgProductStatus" runat="server"></asp:Image>
            </div>
        </ItemTemplate>
    </asp:DataList>

Here is my code to load the data into the DataList.

    var cnnString = ConfigurationManager.ConnectionStrings["TaktBoardsConnectionString"].ConnectionString;
    SqlConnection conn = new SqlConnection(cnnString);
    SqlCommand cmd = new SqlCommand();

    string sql = "SELECT [ProductID], [ProductName] FROM [Product] WHERE [ProductID] <> 1 ORDER BY [ProductID]";
    cmd.CommandText = sql;
    cmd.CommandType = CommandType.Text;
    cmd.Connection = conn;

    cmd.Connection.Open();
    dlstProductDataList.DataSource = cmd.ExecuteReader();
    dlstProductDataList.DataBind();

    cmd.Connection.Close();
    cmd.Connection.Dispose();

The code/logic to get the image would look something like this...

Somehow for each lblProductName that gets bound in the DataList I would have to execute the following query. If the query below returns 0 in ProductCount then I'd set the imgProductStatus to be switchoff.jpg. If it's > 0 then it would be set to switchon.jpg. For each lblProductName I know the ProductID (as per the first query above).

    sql = "SELECT COUNT([ProductID]) AS ProductCount FROM TaktBoard WHERE [ProductID] = ??????? AND CONVERT (date, [BoardDate]) = CONVERT (date, GETDATE())";

If anyone can steer me in the right direction it would be greatly appreciated!!

Andy

Answer 1

You can adjust your query to include the count.

You'll need to include this in your SQL too, there are a few ways to do this such as with a join, but without seeing your full schema, you may have more luck with a subquery

string sql = "SELECT [ProductID], [ProductName], (select COUNT([ProductID]) FROM TaktBoard tb WHERE tb.[ProductID] = [Product].[ProductID] AND CONVERT (date, [BoardDate]) = CONVERT (date, GETDATE())) as [ProductCount] FROM [Product] WHERE [ProductID] <> 1 ORDER BY [ProductID]";

Formatted a bit more to see

SELECT [ProductID], [ProductName],
(
    select COUNT([ProductID])
    FROM TaktBoard tb
    WHERE tb.[ProductID] = [Product].[ProductID]
    AND CONVERT (date, [BoardDate]) = CONVERT (date, GETDATE())
) as [ProductCount]

FROM [Product]
WHERE [ProductID] <> 1 
ORDER BY [ProductID]

Then use this within your image.

<asp:Image ID="imgProductStatus" runat="server" ImageUrl='<%# (Convert.ToInt32(Eval("ProductCount")) == 0) ? "~/Images/switchoff.jpg" : "~/Images/switchon.jpg" %>'></asp:Image>