How do I perform a search?

[RachelKC]

I have a website with Sql database. I want to be able to let users search my products table for particular titles, categories, etc. Does ListView have a search capability? Or else how do I do this?
Any help would be appreciated.

Rachel

[Hack]

Why not use ordinary SQL?

[RachelKC]

Why not use ordinary SQL?

I don't care if its with a listview.

I just want to get something to work. I've been trying now with the code below with no luck.

I put in a gridview and then added new data source and tried this statement:
SelectCommand="SELECT * FROM [authors] WHERE ([au_lname] LIKE '%' + @au_lname + '%') OR ([au_fname] LIKE '%' + @au_fname + '%') ">

But I get following error:
Must declare the scalar variable "@au_fname".

It will only let me search ONE field. Rest of my code is below - it works with only that one field but there must be a way to let you search ALL the fields in your table using the textbox.text search ? I've had no luck.

<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="au_id" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="au_id" HeaderText="au_id" ReadOnly="True"
SortExpression="au_id" />
<asp:BoundField DataField="au_lname" HeaderText="au_lname"
SortExpression="au_lname" />
<asp:BoundField DataField="au_fname" HeaderText="au_fname"
SortExpression="au_fname" />
<asp:BoundField DataField="phone" HeaderText="phone" SortExpression="phone" />
<asp:BoundField DataField="address" HeaderText="address"
SortExpression="address" />
<asp:BoundField DataField="city" HeaderText="city" SortExpression="city" />
<asp:BoundField DataField="state" HeaderText="state" SortExpression="state" />
<asp:BoundField DataField="zip" HeaderText="zip" SortExpression="zip" />
<asp:CheckBoxField DataField="contract" HeaderText="contract"
SortExpression="contract" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:PUBSConnectionString4 %>"

SelectCommand="SELECT * FROM [authors] WHERE ([au_lname] LIKE '%' + @au_lname + '%')">
<SelectParameters>
<asp:ControlParameter ControlID="txtList" DefaultValue="txtList.Text"
Name="au_lname" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>

[RachelKC]

I finally resolved this issue.

Rachel

[Hack]

Post your solution.

It could help others with the same or similiar problem.

Thanks.

[RachelKC]

Sure thing. I figure my problems are so elementary that others already know but happy to do that. I had tried this several times and it did not work and then the last time I did it, it did work. So some step I took must have been different.

I am using one text box, though you can use one for each field if you want to set it up that way.

I found the solution playing with the gridview control. I never read anywhere that you could set up your sql statement with controls through that wizard. Discovered it by accident.

edit: I almost forgot to mention, after the gridview built it using AND, I edited it in the page and changed AND to OR. I don't think it lets you use OR in the wizard.

<ContentTemplate>
Searching: <asp:Label ID="Label1" runat="server" Text="Label" ></asp:Label>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333"
GridLines="None">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="ISBN" HeaderText="ISBN" SortExpression="ISBN" />
<asp:BoundField DataField="Author" HeaderText="Author"
SortExpression="Author" />
<asp:BoundField DataField="Category" HeaderText="Category"
SortExpression="Category" />
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<&#37;$ ConnectionStrings:ASPNETDBConnectionString6 %>"
SelectCommand="SELECT [Title], [ISBN], [Author], [Category] FROM [au_Products] WHERE (([Title] LIKE '%' + @Title + '%') OR ([ISBN] LIKE '%' + @ISBN + '%') OR ([Author] LIKE '%' + @Author + '%') OR ([Category] LIKE '%' + @Category + '%'))">
<SelectParameters>
<asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
Name="Title" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
Name="Author" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
Name="ISBN" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
Name="Category" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" />
</ContentTemplate>

This was built with the GridView control wizard. So its all in the main aspx page not the code behind.

[Andrew0999]

Sure thing. I figure my problems are so elementary that others already know but happy to do that. I had tried this several times and it did not work and then the last time I did it, it did work. So some step I took must have been different.

I am using one text box, though you can use one for each field if you want to set it up that way.

I found the solution playing with the gridview control. I never read anywhere that you could set up your sql statement with controls through that wizard. Discovered it by accident.

edit: I almost forgot to mention, after the gridview built it using AND, I edited it in the page and changed AND to OR. I don't think it lets you use OR in the wizard.

<ContentTemplate>
Searching: <asp:Label ID="Label1" runat="server" Text="Label" ></asp:Label>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333"
GridLines="None">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="ISBN" HeaderText="ISBN" SortExpression="ISBN" />
<asp:BoundField DataField="Author" HeaderText="Author"
SortExpression="Author" />
<asp:BoundField DataField="Category" HeaderText="Category"
SortExpression="Category" />
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString6 %>"
SelectCommand="SELECT [Title], [ISBN], [Author], [Category] FROM [au_Products] WHERE (([Title] LIKE '%' + @Title + '%') OR ([ISBN] LIKE '%' + @ISBN + '%') OR ([Author] LIKE '%' + @Author + '%') OR ([Category] LIKE '%' + @Category + '%'))">
<SelectParameters>
<asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
Name="Title" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
Name="Author" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
Name="ISBN" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
Name="Category" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" />
</ContentTemplate>

This was built with the GridView control wizard. So its all in the main aspx page not the code behind.

Thanks for your post and its solution it is helpful for me as i was having similar problem and i think it would be helpful for others who has same or similar problem.

[hemslyjones]

Hi RachelKC, you have got many good reply. I hope that would be help you.


All the best!