SQL query with the 'LIKE' operator

kent1975 · February 20th, 2011, 07:07 PM

Hello.

Iâm doing an exercise where I want a filtering option for reviews to show every reviews in the management section. Iâve changed the sql for the select query like this:

Code:

SelectCommand="SELECT Review.Id, Review.Title, Review.Authorized, Review.CreateDateTime FROM Review INNER JOIN Genre ON Review.GenreId = Genre.Id WHERE (Genre.Name = @GenreName)"

And the Value for the DropDownList to âNameâ. Which works just fine, but when Iâm changing the â=â operator in the ....WHERE (Genre.Name = @GenreName)... filter with âLIKEâ Iâm getting in trouble. Iâve tried to write it like this â¦. WHERE (Genre.Name LIKE Nâ%@GenreName%â)

Can you help?

Imar · February 21st, 2011, 03:47 AM

Hi there,

You can try concatenating the value and the % symbols, e.g.:

LIKE '%' + @GenreName + '%'

Hope this helps,

Imar

kent1975 · February 21st, 2011, 05:13 AM

Thanks, this solved some of the problems. Now I'm getting every genre with "rock" and so on, but when the default value in the dropdownlist "" (blank) is sent inn it doesn't return anything. Doesn't LIKE work when the value is nothing?

Do you have a suggestion?

BTW, finished the book and loved it!
Looking for a new book to learn even more about .net 4 and sql database. Do you have something to recommend?

Mvh
Kent

Imar · February 21st, 2011, 05:41 AM

You can also check for the parameter being null:

LIKE '%' + @GenreName + '%' OR @GenreName IS NULL

This returns all records when @GenreName is null or an empty string.

Quote:

BTW, finished the book and loved it!

Great to hear. Thanks!

Quote:

Looking for a new book to learn even more about .net 4 and sql database.

You could take a look at Professional ASP.NET 4 in C# and VB, or one of the Problem Design Solution books on ASP.NET.

Cheers,

Imar

kent1975 · February 22nd, 2011, 07:27 AM

Hi. Thanks again, but I'm still having some problems getting this to work on the page. The SQL-query appears ok (works when Iâm testing it), but on the Page I donât get any Reviews when the default value = ââ.
Bye the way I have changed back to Id as Value, when I saw that the NULL test should work fine here too.
Everything else are working fine, Iâm getting reviews when I chose a Genre and so on.
This is the code for the reviews-page:

<asp:Content ID="Content2" ContentPlaceHolderID="cpMainContent" Runat="Server">
<asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="True"
AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="Name"
DataValueField="Id">
<asp:ListItem Value=""></asp:ListItem>
</asp:DropDownList>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Id"
DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="Id"
HeaderText="Id" SortExpression="Id" InsertVisible="False"
ReadOnly="True" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:CheckBoxField DataField="Authorized" HeaderText="Authorized"
SortExpression="Authorized" />
<asp:BoundField DataField="CreateDateTime" HeaderText="CreateDateTime"
SortExpression="CreateDateTime" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:PlanetWroxConnectionString1 %>"
DeleteCommand="DELETE FROM [Review] WHERE [Id] = @Id"
InsertCommand="INSERT INTO [Review] ([Title], [Authorized], [CreateDateTime]) VALUES (@Title, @Authorized, @CreateDateTime)"
SelectCommand="SELECT Id, Title, Authorized, CreateDateTime FROM Review WHERE (GenreId = @GenreId) OR (@GenreId IS NULL)"

UpdateCommand="UPDATE [Review] SET [Title] = @Title, [Authorized] = @Authorized, [CreateDateTime] = @CreateDateTime WHERE [Id] = @Id">
<DeleteParameters>
<asp:Parameter Name="Id" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Title" Type="String" />
<asp:Parameter Name="Authorized" Type="Boolean" />
<asp:Parameter Name="CreateDateTime" Type="DateTime" />
</InsertParameters>
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" DefaultValue="" Name="GenreId"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Title" Type="String" />
<asp:Parameter Name="Authorized" Type="Boolean" />
<asp:Parameter Name="CreateDateTime" Type="DateTime" />
<asp:Parameter Name="Id" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:PlanetWroxConnectionString1 %>"
SelectCommand="SELECT [Id], [Name] FROM [Genre] ORDER BY [SortOrder]">
</asp:SqlDataSource>
<a href="AddEditReviews.aspx">Insert New Review</a>
</asp:Content>

Mvh Kent

Imar · February 22nd, 2011, 01:37 PM

Ah, I see now. You need to set the CancelSelectOnNullParameter property on the DataSource to False. As its name suggests, with this value set to True (which it is by default), the control doesn't execute the SELECT command when a parameter is null or an empty string which is the case for the Genre Id parameter. E.g.:

Code:

 
<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
    ConnectionString="<%$ ConnectionStrings:PlanetWroxConnectionString1 %>"  
    ... Other code here  
    CancelSelectOnNullParameter="False">

Alternatively, but less charming, you could set the DefaultValue to -1 or another value that has no matching ID (any negative number would qualify) and then compare the parameter to that value:

Code:

 
SelectCommand="SELECT Id, Title, Authorized, CreateDateTime FROM Review WHERE (GenreId = @GenreId) OR (@GenreId = -1)"

Hope this helps,

Imar

kent1975 · March 4th, 2011, 05:59 PM

Thanks for the tips, this worked fine.

Btw, have bought the book âprofessional asp.net 4: in C# and VBâ, and are looking forward to learn even more.
Do you have a good book for web services also? Need to handle booth http and https issues, mostly consuming data..

Imar · March 5th, 2011, 05:39 AM

No book comes to mind at the moment but you could search the booklist: http://www.wrox.com/go/browsebooks and see if there's anything that fits your needs.

Cheers,

Imar