CODEWITHSUNDEEP
asp.netsqlms-accessvisual-studio-2008
Gloria
Gloria

Reputation: 1325

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

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.

Upvotes: 1

Views: 1212

Answers (2)

Gloria
Gloria

Reputation: 1325

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>

Upvotes: 1

Mayank Pathak
Mayank Pathak

Reputation: 3681

Gloria, You could change after where like below example

  [title] Like 1st criteria OR [title] Like 2nd criteria OR  [title] Like 3rd criteria;

Upvotes: 0

Related Questions