Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Fulltext Search DB Guru's please help!

    My site uses a text box to allow visitors to search products.
    I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
    e.g
    megger
    meg
    mft megger (proper order in the database is megger mft)
    mft1710 (using 1710 should find the product)

    This is my select statement (classic ASP)

    Code:
    <%
    Dim RSResults__param5
    RSResults__param5 = "xxx"
    If (Request("searchme")  <> "") Then 
      RSResults__param5 = Request("searchme") 
    End If
    %>
    <%
    Dim RSResults__param6
    RSResults__param6 = "0"
    If (Application("ClientID")  <> "") Then 
      RSResults__param6 = Application("ClientID") 
    End If
    %>
    Set RSResults_cmd = Server.CreateObject ("ADODB.Command")
    RSResults_cmd.ActiveConnection = MyConn
    RSResults_cmd.CommandText = "SELECT FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, KEY_TBL.RANK, dbo.ClientProducts.ClientID FROM dbo.Products AS FT_TBL INNER JOIN FREETEXTTABLE(dbo.Products, Product, ?) AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY] INNER JOIN  dbo.ClientProducts ON FT_TBL.ProductID = dbo.ClientProducts.ProductID WHERE (dbo.ClientProducts.ClientID = ?) ORDER BY " + orderby
    
    RSResults_cmd.Prepared = true
    RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param1", 200, 1, 255, "%" + RSResults__param5 + "%") ' adVarChar
    RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param2", 5, 1, -1, RSResults__param6) ' adDouble
    
    Set RSResults = RSResults_cmd.Execute
    RSResults_numRows = 0
    Any advice would be great.

    Andy

  2. #2
    Join Date
    Feb 2008
    Posts
    120
    ok i've reaslised i can use a wildcard * in my Statement.
    What i'm now not sure on, is how to write the wildcard into my code.

    How do i the wildcard* to my variable ? (FREETEXTTABLE(dbo.Products, Product, ?)

    I've tried "? *" but this doesn't work.

    Help!
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •