Posted: 6/28/2011
I have a deop down list and a text box. I want the end user to be able to select a store name from the drop down and then query the DB with the text box. I have this working. However when the drop down is at the selected value of 0. which I left the text blank, when you put the string in the text box it returns no results to my details view. I am assuming that the problem is with the drop down, but maybe its my where clause. Here is my code:
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"> <table align="center" cellpadding="0" cellspacing="1" class="style1"> <tr> <td align="center"> <asp:DropDownList ID="DropDownList1" AutoPostBack="true" runat="server"> <asp:ListItem></asp:ListItem> <asp:ListItem>Jewel</asp:ListItem> <asp:ListItem>Walmart</asp:ListItem> <asp:ListItem>Berkot's</asp:ListItem> </asp:DropDownList> <br /> <br /> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Button" /> <br /> <br /> <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AllowPaging="True" AutoGenerateRows="False" DataKeyNames="ItemId" DataSourceID="SqlDataSource1"> <Fields> <asp:BoundField DataField="ItemId" HeaderText="ItemId" InsertVisible="False" ReadOnly="True" SortExpression="ItemId" /> <asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" /> <asp:BoundField DataField="StoreName" HeaderText="StoreName" SortExpression="StoreName" /> <asp:BoundField DataField="Coupons" HeaderText="Coupons" SortExpression="Coupons" /> <asp:BoundField DataField="RetailPrice" HeaderText="RetailPrice" SortExpression="RetailPrice" /> <asp:BoundField DataField="FinalValuePrice" HeaderText="FinalValuePrice" SortExpression="FinalValuePrice" /> <asp:BoundField DataField="ClerkName" HeaderText="ClerkName" SortExpression="ClerkName" /> </Fields> </asp:DetailsView> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringtestData %>" SelectCommand="SELECT * FROM [CouponBot] WHERE (([ItemName] LIKE '%' + @ItemName + '%') AND ([StoreName] = @StoreName))"> <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="ItemName" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="DropDownList1" Name="StoreName" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> </td> </tr> </table> </asp:Content>
Hi Mike,
Your SQL Query is:
SELECT * FROM [CouponBot] WHERE (([ItemName] LIKE '%' + @ItemName + '%') AND ([StoreName] = @StoreName))
The WHERE clause says that both parts need to be TRUE to get the values, because you use AND. In your case, if you put for instance ItemName = "Something" and StoreName null, you will get no result except if you have records where StoreName is "" and the ItemName is like 'Something'.
When you have selected the first value (empty string) in your dropdownlist and added 'Something' in your textbox, your SQL query will be:
SELECT * FROM [CouponBot] WHERE (([ItemName] LIKE '%Something%') AND ([StoreName] = ''))
Tell me how do you store your data in your database? (post sample data) And how do you further want to filter if you have 'nothing selected in your dropdownlist'?
Hope this helps,Hajan
This is the table I am using:
ItemId varchar(50) //Primary key, Is Identity yes seed 1 increment 1
ItamName varchar(50
StoreName varchar(50)
Coupons varchar(100) //Allows nulls
WebOffers nvarchar(max) //Allows nulls
WebOffers2 nvarchar(max) //Allows nulls
RetailPrice smallmoney
FinalValuePrice smallmoney
IsNationalOffer nchar(10)
Region varchar(50)
Administrator nchar(50)
ValidThru date
I want to only filter by store name with the drop down. Then the text box will bring all the results from that store. Example; if you select "Jewel" from the drop down then type in soup it will bring all rows with soup in the ItemName that have "Jewel" as the StoreName. But if the drop down is left at the "select one" or "blank" I want it to pull all "soup" in Itemname to my details views regardless of what the StoreName is.
Thanks!
Posted: 6/29/2011
Mike,
Try with this query:
SELECT * FROM [CouponBot] WHERE ((@ItemName = '') OR ([ItemName] LIKE '%' + @ItemName + '%')) AND ((@StoreName = '') OR ([StoreName] = @StoreName))
This will work perfectly if you send '' (empty string) for StoreName when the first item is selected.
To ensure and make this work even if you send null for StoreName or ItemName, you can do it in the following way:
SELECT * FROM [CouponBot] WHERE ((coalesce(@ItemName,'') = '') OR ([ItemName] LIKE '%' + @ItemName + '%')) AND ((coalesce(@StoreName,'') = '') OR ([StoreName] = @StoreName))
Test it in the following way directly in SQL Server Management Studio:
declare @ItemName as nvarchar(100) declare @StoreName as nvarchar(100) set @ItemName = 'Item' --add your search string here set @StoreName = '' -- add '' or null or StoreName SELECT * FROM [CouponBot] WHERE ((coalesce(@ItemName,'') = '') OR ([ItemName] LIKE '%' + @ItemName + '%')) AND ((coalesce(@StoreName,'') = '') OR ([StoreName] = @StoreName))
Thanks for the help. When I configure my datasource and test the query it works. But when I try it on the actual page it doesn't. When it posts back the whole page flashes but it doesn't do anything.
Here's the code for my Drop down, Text box, Button, gridview, and datasource:
<asp:DropDownList ID="StoreDD" runat="server" Height="26px" Width="110px"> <asp:ListItem Value="0">Store Name</asp:ListItem> <asp:ListItem Value="Berkot's">Berkot's</asp:ListItem> <asp:ListItem Value="Butera">Butera</asp:ListItem> <asp:ListItem Value="CVS">CVS</asp:ListItem> <asp:ListItem Value="Dominick's">Dominick's</asp:ListItem> <asp:ListItem Value="Food for Less">Food for Less</asp:ListItem> <asp:ListItem Value="HyVee">HyVee</asp:ListItem> <asp:ListItem Value="Jewel">Jewel</asp:ListItem> <asp:ListItem Value="Kroger">Kroger</asp:ListItem> <asp:ListItem Value="Save-A-Lot">Save-A-Lot</asp:ListItem> <asp:ListItem Value="Target">Target</asp:ListItem> <asp:ListItem Value="Ultra">Ultra</asp:ListItem> <asp:ListItem Value="Walgreens">Walgreens</asp:ListItem> <asp:ListItem Value="Walmart">Walmart</asp:ListItem> <asp:ListItem Value="Whole Foods">Whole Foods</asp:ListItem> </asp:DropDownList> <asp:TextBox ID="SearchTxtBox" runat="server" Height="20px" style="position: relative; top: 0px; left: -4px; width: 200px"></asp:TextBox> <asp:Button ID="SearchBtn" runat="server" Height="28px" Text="Search" style="left: -11px; position: relative" /> </td> </tr> <tr> <td align="center" style="font-family: Arial, Helvetica, sans-serif; font-style: italic"> Select a store from the drop down if you wish to search by store.</td> </tr> </table> </td> </tr> <tr> <td align="center" valign="middle"> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False" BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="ItemId" DataSourceID="SqlDataSource1" Height="50px" Width="400px" style="color: #FF6600"> <EditRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" /> <Fields> <asp:BoundField DataField="ItemId" HeaderText="ItemId" SortExpression="ItemId" InsertVisible="False" ReadOnly="True" > </asp:BoundField> <asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" > </asp:BoundField> <asp:BoundField DataField="StoreName" HeaderText="StoreName" SortExpression="StoreName" > </asp:BoundField> <asp:BoundField DataField="Coupons" HeaderText="Coupons" SortExpression="Coupons" > </asp:BoundField> <asp:BoundField DataField="WebOffers" HeaderText="WebOffers" SortExpression="WebOffers" > </asp:BoundField> <asp:BoundField DataField="WebOffers2" HeaderText="WebOffers2" SortExpression="WebOffers2" > </asp:BoundField> <asp:BoundField DataField="RetailPrice" HeaderText="RetailPrice" SortExpression="RetailPrice" > </asp:BoundField> <asp:BoundField DataField="FinalValuePrice" HeaderText="FinalValuePrice" SortExpression="FinalValuePrice" /> <asp:BoundField DataField="IsNationalOffer" HeaderText="IsNationalOffer" SortExpression="IsNationalOffer" /> <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" /> <asp:BoundField DataField="Administrator" HeaderText="Administrator" SortExpression="Administrator" /> <asp:BoundField DataField="ValidThru" HeaderText="ValidThru" SortExpression="ValidThru" /> </Fields> <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" /> <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" /> <RowStyle BackColor="White" ForeColor="#330099" /> </asp:DetailsView> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CouponBotConnectionString1 %>" SelectCommand="SELECT * FROM [Items] WHERE ((coalesce(@ItemName,'') = '') OR ([ItemName] LIKE '%' + @ItemName + '%')) AND ((coalesce(@StoreName,'') = '') OR ([StoreName] = @StoreName))" OldValuesParameterFormatString="original_{0}"> <SelectParameters> <asp:ControlParameter ControlID="SearchTxtBox" Name="ItemName" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="StoreDD" Name="StoreName" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource>
