How can I do a cast in a SqlDataSource FilterExpression?

Question

I'm using a single textbox to search a report and filter out records. One of my fields is an int32 and the rest are varchar's. So when the filter tries to compare the string from the textbox with the int32 field, I get an error. Here's the SQLDataSouce and the search box:

<asp:TextBox ID="SearchBox" AutoPostBack="true" OnTextChanged="SearchBox_TextChanged" runat="server"></asp:TextBox>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:oneworldunitedConnectionString %>" 
        SelectCommand="SELECT usrs.UserID, usrs.UserName, usrs.FirstName, usrs.LastName, jndt.PropertyValue AS JoinDate, rgn.PropertyValue AS Region, cty.PropertyValue AS City, spnsrnm.PropertyValue AS SponsorName FROM            dbo.Users AS usrs INNER JOIN
                     dbo.UserProfile AS jndt ON jndt.PropertyDefinitionID = 412 AND usrs.UserID = jndt.UserID INNER JOIN
                     dbo.UserProfile AS cty ON cty.PropertyDefinitionID = 27 AND usrs.UserID = cty.UserID INNER JOIN
                     dbo.UserProfile AS rgn ON rgn.PropertyDefinitionID = 28 AND usrs.UserID = rgn.UserID INNER JOIN
                     dbo.UserProfile AS spnsrnm ON spnsrnm.PropertyDefinitionID = 421 AND usrs.UserID = spnsrnm.UserID" 
        FilterExpression="UserID LIKE '%{0}%' OR UserName LIKE '%{0}%' OR FirstName LIKE '%{0}%' OR LastName LIKE '%{0}%' OR JoinDate LIKE '%{0}%' OR Region LIKE '%{0}%' OR City LIKE '%{0}%' OR SponsorName LIKE '%{0}%'">
    <FilterParameters>
        <asp:ControlParameter Name="Search" ControlID="SearchBox" PropertyName="Text" />    
    </FilterParameters>
</asp:SqlDataSource>

Please don't bash the sql statement, I know it's extremely inefficient. Does anyone know how to change the FilterExpression so that I don't get an error when it tries to compare the textbox value with the UserID field? I tried doing this:

FilterExpression="CAST(UserID as varchar(4)) LIKE '%{0}%' OR UserName LIKE '%{0}%' OR FirstName LIKE '%{0}%' OR LastName LIKE '%{0}%' OR JoinDate LIKE '%{0}%' OR Region LIKE '%{0}%' OR City LIKE '%{0}%' OR SponsorName LIKE '%{0}%'"

but that didn't work. I figured doing UserID LIKE CAST('%{0}%' as varchar(4)) wouldn't work because if it is actually a string that they entered then you would get an error.

Edit: Here is the error message I got "Cannot perform 'Like' operation on System.Int32 and System.String." That was before I did the cast. Then after I did the cast I got "The expression contains undefined function call Cast()."

Anyone have any ideas?

Thanks,
Matt

Answer 1

The problem probably isn't UserID, your change should have fixed that. More likely the problem is coming from one of the other comparison fields, with JoinDate being the leading contender. (I can't fix that for you because I don't know what your string date format is).

---

OK, if date format is mm/dd/yyy then you want to change the filter comparison of JoinDate to:

OR CONVERT(Varchar(10), JoinDate, 101) LIKE '%{0}%'

---

OK, based on the error messages, the error is not coming from SQL Server, but must be coming from something on your (client) side. What you will need to do is to figure out what that is, find it in your code, and then either

1) try to set a "PassThrough" (or "Passthru") mode, so that it doesn't try to validate it first,

or 2) lookup that facility's version of SQL and change the CAST() to the corresponding function. For instance ODBC will do exactly what you are seeing here and as ODBC SQL does not support the CAST function you have to use the ODBC SQL CONVERT() function instead.

Note that ODBC also has a pass-thru mode option as well.