full text t-sql that accepts input from a control at runtime

Discussion in 'Databases' started by Dipset, Jun 3, 2008.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Please can anybody help with a query statement that accepts input from a user through a control (textbox control) at runtime.

    for example, I ran this query - select *
    from records
    where contains(designation,'"bull*"')

    and it returned values successfully.

    what if I want the value (bull*) to be inserted from a control (textbox)from my user interface and what ever values I insert should be used to run the query, how do I write the query to substitute the query above, thankz.
     
  2. Thx 4 replying. From the above example, I am trying to retrieve data from a table named records, I have created a fulltext index on column "designation" of a table named "records" and also a fulltext catalog for this table (records). Ijust need an sql statement that will retrieve all information from the table where the designation column contains a word or phrase "bull".


    I hope u understan this a bit more, thank you.
     
  3. Ahhh, even easier. [​IMG]

    You'll want to use the "like" operative, like this:


     
  4. Thanks but you still don't get me. I want to create my own search engine and as earlier stated, I have created a fulltext catalog on column "designation" of "Records" table.


    Here is the logic I want you to understand:


    On my form design, I have a textbox that will receive input from user (for instance- "bull") then pass the value ("bull") to the query which will now execute the query and retrieve all values that contain "bull" in the designation column of records table.


    How do I bind the input value to the text box? Here below is the code of the web form I created, please do take a look and make the right corrections for me, this is the last phase of work for me.


    Search Page:





    <%@ Page Language="VB" MasterPageFile="~/Dicon.master" Title="Untitled Page" %>


    <script runat="server">


    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)


    SqlDataSource1.SelectParameters("DESIGNATION").DefaultValue = _


    "%" + TextBox1.Text + "%"


    End Sub


    </script>


    <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">


    <style type="text/css">


    .style4


    {


    color: #FFFFFF;


    font-weight: bold;


    }


    </style>


    </asp:Content>


    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">


    <table width="900px" align="center">


    <tr>


    <td>


    <span class="style4">Enter Designation :</span>


    <asp:TextBox ID="TextBox1" runat="server" Width="200px"></asp:TextBox>


    &amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;


    <asp:Button ID="Button1" runat="server" Text="Search" Width="60px" />


    </td>


    </tr>


    <tr>


    <td>





    <asp:GridView ID="GridView1" runat="server" AllowPaging="True"


    AllowSorting="True" AutoGenerateColumns="False"


    DataSourceID="SqlDataSource1" Width="900px">





    <Columns>


    <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />


    <asp:BoundField DataField="SNUM" HeaderText="S-NUM" SortExpression="SNUM" />


    <asp:BoundField DataField="DATE" HeaderText="DATE" SortExpression="DATE" DataFormatString='{0:d}' />


    <asp:BoundField DataField="CODE" HeaderText="CODE" SortExpression="CODE" />


    <asp:BoundField DataField="DESIGNATION" HeaderText="DESIGNATION"SortExpression="DESIGNATION" />


    <asp:BoundField DataField="UNIT" HeaderText="UNIT" SortExpression="UNIT" />


    <asp:BoundField DataField="SVC" HeaderText="SVC" SortExpression="SVC" />


    <asp:BoundField DataField="UNSVC" HeaderText="UNSVC" SortExpression="UNSVC" />


    <asp:BoundField DataField="STOREID" HeaderText="STOREID" SortExpression="STORENAME" />


    <asp:BoundField DataField="LOCATION" HeaderText="LOCATION" SortExpression="LOCATION" />


    <asp:BoundField DataField="ADMINNAME" HeaderText="ADMIN NAME" SortExpression="ADMINNAME" />


    <asp:BoundField DataField="STOREMAN" HeaderText="STOREMAN" SortExpression="STOREMAN" />


    </Columns>


    </asp:GridView>


    <asp:SqlDataSource ID="SqlDataSource1" runat="server"


    ConnectionString="<%$ ConnectionStrings:DiconConnectionString %>" SelectCommand="select id, snum, date, code, designation, unit, svc, unsvc, storeid, location, adminname, storeman


    from records


    where contains(designation, @designation)">


    <SelectParameters>


    <asp:ControlParameter ControlID="TextBox1" Name="designation"


    PropertyName="Text" />


    </SelectParameters>


    </asp:SqlDataSource>











    </td>


    </tr>


    </table>


    </asp:Content>


    It compiled successfully but would not return any results. I think there is a data binding error in my code, I'm only novice. This page is just for you to understand what logic I am trying to execute, thank you as you always seem to solve my problems.


    Thank you.
     
  5. The dotLuceneSearch is an alternative to the MS full text search, so it probably isn't necessary if you have already built your full text catalog.

    I'm not familiar enough with .net to troubleshoot your code, but I would suggest that you output the sql to a field or alert so that you can see what it looks like.

    In your code you have '%' + TextBox1.Text + '%'

    You need to make sure that the enclosing single and double quotes are there, and I don't see where these are added.

    marc
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page