Displaying Search criteria that begins with

Author Message
mkusza1

  • Total Posts : 2
  • Scores: 0
  • Reward points : 0
  • Joined: 5/6/2008
  • Status: offline
Displaying Search criteria that begins with Tuesday, May 06, 2008 2:50 AM (permalink)
0
The problem i am having is that my search function is set to span across 4 columns and it is pulling anything that contains what is entered into the search. Ex..if i search for 90.4 it will pull anything containing 90.4  I need it to only get the records that start with what the user enters, while still searching all the columns. Here is what i have and have tried.
 
 
This code pulls anything that has what is searched for contained in either of the 4 columns.
<%
 Dim rsCatalog__varSearch
 rsCatalog__varSearch = "%"
 If ((Request.Form("searchText")) <> "") Then 
   rsCatalog__varSearch = (Request.Form("searchText"))
 End If
 %>
 <%
 Dim rsCatalog
 Dim rsCatalog_cmd
 Dim rsCatalog_numRows
 Set rsCatalog_cmd = Server.CreateObject ("ADODB.Command")
 rsCatalog_cmd.ActiveConnection = MM_catalog_STRING
 rsCatalog_cmd.CommandText = "SELECT [Application], [Section], [JBL Part Number], [Description], [Part Code], [Revision], [Hyperlink], [Area] FROM Catalog WHERE [Application] LIKE ? OR [Section] LIKE ? OR [JBL Part Number] LIKE ? OR [Part Code] LIKE ? ORDER BY [Area], [Section] ASC"
 rsCatalog_cmd.Prepared = true
 rsCatalog_cmd.Parameters.Append rsCatalog_cmd.CreateParameter("param1", 200, 1, 255, "%" + rsCatalog__varSearch + "%") ' adVarChar
 rsCatalog_cmd.Parameters.Append rsCatalog_cmd.CreateParameter("param2", 200, 1, 255, "%" + rsCatalog__varSearch + "%") ' adVarChar
 rsCatalog_cmd.Parameters.Append rsCatalog_cmd.CreateParameter("param3", 200, 1, 255, "%" + rsCatalog__varSearch + "%") ' adVarChar
 rsCatalog_cmd.Parameters.Append rsCatalog_cmd.CreateParameter("param4", 200, 1, 255, "%" + rsCatalog__varSearch + "%") ' adVarChar
 Set rsCatalog = rsCatalog_cmd.Execute
 rsCatalog_numRows = 0
 %>
 
  
 

 
This is what i tried to do, but it was unsuccessful. And gave me the proceeding error.
 
[color=#191970][size=2]SELECT [Application], [Section], [JBL Part Number], [Description], [Part Code], [Revision], [Hyperlink], [Area] FROM Catalog WHERE [Application] LIKE @varSearch + ? OR [Section] LIKE @varSearch + ? OR [JBL Part Number] LIKE @varSearch + ? OR [Part Code] LIKE @varSearch + ? ORDER BY [Area], [Section] ASC"[/size][/color]
  
 

 
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done"

Not sure if it's becuase of my lack of understanding or if there is something else i am missing.

 
Thanks for the help in advance!!!


 
#1
    mkusza1

    • Total Posts : 2
    • Scores: 0
    • Reward points : 0
    • Joined: 5/6/2008
    • Status: offline
    RE: Displaying Search criteria that begins with Thursday, May 08, 2008 4:14 AM (permalink)
    0
    I solved my issue. In my params i had to take out the "%" + from the beginning. Now looks like this and runs great.
     
    [CODE]rsCatalog_cmd.Parameters.Append rsCatalog_cmd.CreateParameter("param1", 200, 1, 255, rsCatalog__varSearch + "%") ' adVarChar
    rsCatalog_cmd.Parameters.Append rsCatalog_cmd.CreateParameter("param2", 200, 1, 255, rsCatalog__varSearch + "%") ' adVarChar
    rsCatalog_cmd.Parameters.Append rsCatalog_cmd.CreateParameter("param3", 200, 1, 255, rsCatalog__varSearch + "%") ' adVarChar
    rsCatalog_cmd.Parameters.Append rsCatalog_cmd.CreateParameter("param4", 200, 1, 255, rsCatalog__varSearch + "%") ' adVarChar[/CODE]


     
    #2

      Online Bookmarks Sharing: Share/Bookmark

      Jump to:

      Current active users

      There are 0 members and 1 guests.

      Icon Legend and Permission

      • New Messages
      • No New Messages
      • Hot Topic w/ New Messages
      • Hot Topic w/o New Messages
      • Locked w/ New Messages
      • Locked w/o New Messages
      • Read Message
      • Post New Thread
      • Reply to message
      • Post New Poll
      • Submit Vote
      • Post reward post
      • Delete my own posts
      • Delete my own threads
      • Rate post

      2000-2012 ASPPlayground.NET Forum Version 3.9