SqlDataSource SelectCommand using 2 Parameters and LIKE does not work

Question

I have the following selectcommand in a SqlDataSource, my problem is that it only returns data when I have values for both Parameters. I need it to also return data when only 1 has a value.

SelectCommand="SELECT user.UserId, UserProfiles.Company, UserProfiles.Address, UserProfiles.JV2, UserProfiles.DB1, aspnet_Membership.Email, user.UserName
FROM user INNER JOIN UserProfiles ON user.UserId = UserProfiles.UserId  WHERE        (@UserName  IS NULL OR @UserName = ''  OR user.UserName LIKE '%'+@UserName+'%' ) AND
                     (@Email IS NULL  OR @Email = '' OR user.Email LIKE '%'+@Email+'%')

with the following Parameters

   <SelectParameters>  
    <asp:ControlParameter Name="Email" ControlID="eMail"  PropertyName="Text" Type="String"  />
<asp:ControlParameter Name="UserName" ControlID="userName"  PropertyName="Text" Type="String"  />

When I run the query in SQL Server Management Studio and it returns data when I enter only 1 value or both. When I run the form I only get data returned when I enter values in to both fields.

I found this post: SqlDataSource SelectCommand using LIKE does not work which is similar but the solution hasn't helped.

Obviously I've done something wrong, I just can't see what.

Code52 · Accepted Answer · 2012-06-19 16:15:06Z

4

I have had a similar issue and adding this to the sqldatasource fixed it:

<asp:SqlDataSource CancelSelectOnNullParameter="false">

answered Jun 19, 2012 at 16:15

Code52

562 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlDataSource SelectCommand using 2 Parameters and LIKE does not work

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related