Set CheckBox ‘checked’ from SQL DB if an entry exists without any code-behind

Found an interesting way to set the checked value of a checkbox as a databound control. Used in a datalist for example.

The checkbox in the datalist bound to a value in the usual way:

<asp:CheckBox ID=”MainCatCbx” runat=”server” Checked='<%# Eval(“Active”)%>’ CssClass=”smalltext12″ />

Then the query, this was fairly complicated as I wanted to join 2 tables and set the checkbox to ticked if a value was found and not-ticked if it wasn’t, but still display all the records.

The important part (bold) adds a column (active) which is detmind by the result of the case statement. If the DBID column is null the the active column is set to 0(zero) or 1 (one) and cast as a bit so that when the binding takes place it is evaluated as true or false for the checkbox checked value.

SelectCommand=”SELECT tb1.DBID, tb1.Title, tb2.DBID,
CASE WHEN tb2.DBID IS NULL THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END AS Active,
tb1.ParentDBID FROM tb1 LEFT OUTER JOIN tb2 ON Specs_NavTbl.DBID = tb2.CatDBID
WHERE (tb2.DBID = ?) OR (tb2.DBID IS NULL)
AND (tb1.ParentDBID = 0)”

I found this to become usefull rather than writing codebehind to populate datasets from the DB and manually loop through and set the checkbox checked values etc.