How to use "<%# bind(" to compare a varchar to char?

Question

I have the following code:

<asp:DropDownList ID="ddlStudyYear" runat="server" SelectedValue='<%# Bind("StudyYearID") %>' DataSourceID="sdsStudyYears" DataTextField="StudyYearID" DataValueField="StudyYearID"></asp:DropDownList>
<asp:SqlDataSource ID="sdsStudyYears" runat="server" ConnectionString="<%$ ConnectionStrings:ATCNTV1ConnectionString %>" SelectCommand="SELECT StudyYearID FROM tblStudyYears ORDER BY StudyYearID"></asp:SqlDataSource>

But it does not work because the Bind("StudyYearID") comes from another SqlDataSource further down the code, which returns a VARCHAR(3). However, the datasource above (sdsStudyYears) returns a value as a CHAR(3).

So when I run this, I get the following error:

'ddlStudyYear' has a SelectedValue which is invalid because it does
not exist in the list of items.

I was not able to find it on the net either, but there must be a way in the Bind() function to tell it to CAST the varchar to char. Or perhaps a function surrounding the SelectedValue property to do the casting?

Any help appreciated.

Thank you

Answer 1

Solution found!

I solved it from the query point of view.

I changed the query to ...

SELECT RTRIM(CAST(CASE WHEN StudyYearID = '0' THEN ' ' ELSE StudyYearID END AS VARCHAR(3))) AS StudyYearID FROM tblStudyYears

I know the CAST() is superflous, but the RTRIM() is what did it, but forcing the return value to be just as a VARCHAR() in the gridview datasource and both now compare perfectly.

It would be good to know how I could have done this purely from the bind() function.