searching on multiple fileds using begins with

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

searching on multiple fileds using begins with

Author

Topic

mkusza1
Starting Member

12 Posts

Posted - 2008-04-29 : 14:53:45

What i am trying to achieve is when a user enters their search criteria, it will only display records that begin with what they entered. This is what i have in place now. Thanks for the help in advanced!!!

"SELECT [Application], [Section], [JBL Part Number], [Description], [Part Code], [Revision], [Hyperlink] FROM Catalog WHERE [Application] LIKE ? OR [Section] LIKE ? OR [JBL Part Number] LIKE ? OR [Part Code] LIKE ? ORDER BY [Application], [Section] ASC"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 14:55:32

LIKE @YourVariable + '%'

You need the wild card character % in your query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mkusza1
Starting Member

12 Posts

Posted - 2008-04-29 : 14:57:15

? represents '%varSearch%'

<%
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] FROM Catalog WHERE [Application] LIKE ? OR [Section] LIKE ? OR [JBL Part Number] LIKE ? OR [Part Code] LIKE ? ORDER BY [Application], [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

mkusza1
Starting Member

12 Posts

Posted - 2008-05-02 : 12:03:43

I dont understand what you are saying TKIZER. Perhaps an example could clarify it up for me i am a novice here. Thank you

mkusza1
Starting Member

12 Posts

Posted - 2008-05-06 : 08:26:55

So this is what i came up with after reviewing your post TKIZER, however it is telling me that it is expected to have 5 parameters. When i add another parameter i get

"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.

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"

mkusza1
Starting Member

12 Posts

Posted - 2008-05-08 : 12:12:14

I solved my problem. i had to take out ("%" +) from the beginning of my params.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources