Search for more than one keyword - SQL statement - Access database

advertisements

I am testing an asp.net website with Visual Studio 2008. The website uses an ACCESS database. The following search query works fine if the user inputs one keyword only in the search field. For more than 1 keyword the select statement returns null result. Is there any way to restrusture the SQL statement so that more than 1 keyword can be searchable?

SelectCommand="SELECT [title] FROM [recipe] WHERE ([title] LIKE '%' + ? + '%')">
<SelectParameters>
<asp:ControlParameter ControlID="searchField" Name="title"
PropertyName="Text" Type="String" />
</SelectParameters>

Thanks in advance.


Since MS ACCESS doesn't support full text search I think I solved the problem so that it is possible to search a table(s) using multiple keywords from a single text-box form field. Here is the script. It works well so far. Is there any way to make this script even better????

Thanks

<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If IsPostBack Then

        Dim dbconn, sql, dbcomm, dbread, searchTxt, arrText, intCount
        searchTxt = Request.Form("TextBox1")
        arrText = Split(searchTxt)
        'Response.Write(search)

        dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/MyRecipes.mdb"))
        dbconn.Open()
        For intCount = 0 To UBound(arrText)
            sql = "SELECT * FROM recipe WHERE title LIKE '%" + arrText(intCount) + "%' "
        Next
        dbcomm = New OleDbCommand(sql, dbconn)
        dbread = dbcomm.ExecuteReader()
        customers.DataSource = dbread
        customers.DataBind()
        dbread.Close()
        dbconn.Close()
    End If

End Sub
</script>

<body>
<form id="Form1" runat="server">
<asp:Repeater id="customers" runat="server">

<HeaderTemplate></HeaderTemplate>

<ItemTemplate>
<div style="display:block;">
<%#Container.DataItem("title")%>
</div>
</ItemTemplate>

<FooterTemplate></FooterTemplate>

</asp:Repeater>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox>
</form>

</body>