CODEWITHSUNDEEP
asp.netsqlsqldatasource
jimmy
jimmy

Reputation: 756

SqlDataSource SelectCommand using 2 Parameters and LIKE does not work

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.

Upvotes: 2

Views: 2540

Answers (1)

Code52
Code52

Reputation: 56

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

<asp:SqlDataSource CancelSelectOnNullParameter="false">

Upvotes: 4

Related Questions