[RESOLVED] Search multiple words from textbox in database

[vascobrito]

Hello everyone,
I'm in need of some help searching a database using multiple words from a textbox showing in gridview.

<asp:TextBox ID="TextBox1" runat="server" Width="382px" AutoPostBack="True"></asp:TextBox>
    <asp:Button ID="Button1" runat="server" style="height: 26px" Text="Button" />
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" Width="346px" 
        DataSourceID="AccessDataSource1">
    </asp:GridView>
    <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
        DataFile="~/cod_postais.mdb" 
        SelectCommand="SELECT [Local], [Morada], [Lugar] FROM [cod_c] WHERE ([Morada] LIKE '%' + ? + '%')">
        <SelectParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="Morada" PropertyName="Text" 
                Type="String" />
        </SelectParameters>
    </asp:AccessDataSource>

It works fine, i click button and the gridview shows all the right records.
The problem is when i use more then 1 word in textbox. it shows nothing.
The record in the database field may have several words, so i need to search using all words in textbox.

any help understanding the problem is much apreciated
thanks

Vasco Brito

[kareninstructor]

If you wrote a select statement within MS-Access the code might look like this (replace the single characters with words and alter the like operator that fits your needs)

SELECT Products.[ProductID], Products.[ProductName]
FROM Products
WHERE (((Products.[ProductName]) Like "C*")) 
   OR (((Products.[ProductName]) Like "U*"));

You would then transform to

SELECT ProductName
FROM Products
WHERE ProductName Like "C*" OR ProductName Like "U*"

So with this pattern you need to determine how many words there are in the TextBox and alter the SQL as needed. Another option is to have several TextBox controls, check each one to see if they have content and if so build your select statement around them. Adding params in ASP is something I do not have code for as I do not code ASP. I would think there are alternate methods to do your params.

[vascobrito]

Thank you for your hep.
I was trying to understand why the search wasn't working with more then one word in the textbox.
Reviewing everything i found the problem. actualy it was a database problem because a lot of the records in the field i was searching, had more then one blank space between the words.
So, the record has for example, "Red Orange". I was searching "Red Orange".
Corrected the database and everything works now.
Thanks for your help. Found interesting things in your signature anyway.