none
Passing parameter to Query string using the Freetext function in ADO.net RRS feed

  • Question

  • Hello,

    From a VB.net application I need to execute a query for full text searching using the FREETEXT keyword.

    Actually the freetext syntax is as below:

       select * from students where freetext (*, 'fluent in english')

    When I am trying to use the following code:

                 query="Select Statement From StatementTable Where Freetext(Statement, @Word)"
                 conn.open()
                 Using cmd as new Sqlcommand(query,conn)
                   Using dr as Sqldatareader=cmd.ExecuteReader

    The query is not returning any rows. When I write the query as:

                query="Select Statement From StatementTable Where Freetext(Statement, '@Word')"

    Here @word is not considered as a parameter but rather considered as the input value.
    The result is the statements which contain the term 'word'. I need the result which match the input parameter @word. @word may be a word or a sentence based on the users input.

    When the same query using local variable @word is execeuted in SQL Server Management Studio, it is working fine.
    Please suggest me how to resolve this in ADO.net.

    Thanks in advance.

    Regards,

    Aswani


    Thursday, October 11, 2012 8:25 AM

Answers

  • Hi Aswani,

    I think you can try to write a store procedure and then execute the store procedure in ado.net.

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Aswani Kowuru Thursday, November 8, 2012 8:42 AM
    Thursday, October 18, 2012 10:28 AM
    Moderator

All replies

  • What is the difference between a parameter and an input value? Try doing this:

    query="Select Statement From StatementTable Where Freetext(Statement, @Word)"
    conn.open()
    Using cmd as new Sqlcommand(query,conn)
        cmd.Parameters.AddWithValue("@Word", "@Word")
        Using dr as Sqldatareader=cmd.ExecuteReader

    Thursday, October 11, 2012 4:16 PM
  • Hi,

    The difference i was trying to say here is that it's not taking @word as a variable that can replace any value but rather it is considering word as the input and returning statements that contain the term 'word'. I mean full text seraching is done based on the term 'word'. But this is not the desired result.

    Same is the result when we try the above solution suggested.

    Actually in my post i missed out a statement i.e.

            cmd.Parameters.AddWithValue("@Word", searchword)

    and the query string as:

           query="Select Statement From StatementTable Where Freetext(Statement, @Word)"

    where searchword is the string variable and @Word parameter of the query string should get replaced with the value of searchword.

    But it's not working. Suggest me how the parameter @Word should take the value of searchword and replace it in the query string so that the result is the statements which contain the string 'searchword'.

    I hope I am more clear this time.

    Thanks,

    Aswani

    Friday, October 12, 2012 2:26 AM
  • Hi Aswani,

    I think you can try to write a store procedure and then execute the store procedure in ado.net.

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Aswani Kowuru Thursday, November 8, 2012 8:42 AM
    Thursday, October 18, 2012 10:28 AM
    Moderator
  • Hi,

    Thanks. Both the solutions are working fine.

    Regards,

    Aswani

    Thursday, November 8, 2012 8:44 AM