By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,842 Members | 2,186 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,842 IT Pros & Developers. It's quick & easy.

strSQL - Searching 2 Columns in Same Table?

P: n/a
Hi, Thanks for any help in advance...
The Problem - I have a search routine below, which works and
will return any result where the seach words is found in my DB
products.prodName, all good...

However i'd like it to also like to look at my
products.productDesc (at the same time) to see if the
description also has the word - anyone know how i can achieve
this?
strSQL = "SELECT * FROM products WHERE "
strSQL = strSQL & BuildSQL ("products.prodName",
sarySearchWord)

Function BuildSQL (strTable, sarySearchWord)
intSQLLoopCounter = 0

BuildSQL = BuildSQL & "(" & strTable & " LIKE '%" &
sarySearchWord(0) & "%'"

For intSQLLoopCounter = 1 To UBound(sarySearchWord)

If Request.QueryString("searchMode") = "allwords" Then
BuildSQL = BuildSQL & " AND "
else
BuildSQL = BuildSQL & " OR "
End if

BuildSQL = BuildSQL & strTable & " LIKE '%" &
sarySearchWord(intSQLLoopCounter) & "%'"
Next

BuildSQL = BuildSQL & ")"
End Function

----------------------------------------------
Posted with NewsLeecher v2.0 Beta 5
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
----------------------------------------------

Jul 22 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
InvisibleMan wrote:
Hi, Thanks for any help in advance...
The Problem - I have a search routine below, which works and
will return any result where the seach words is found in my DB
products.prodName, all good...

However i'd like it to also like to look at my
products.productDesc (at the same time) to see if the
description also has the word - anyone know how i can achieve
this?
strSQL = "SELECT * FROM products WHERE "
strSQL = strSQL & BuildSQL ("products.prodName",
sarySearchWord)

Function BuildSQL (strTable, sarySearchWord)
intSQLLoopCounter = 0

BuildSQL = BuildSQL & "(" & strTable & " LIKE '%" &
sarySearchWord(0) & "%'"

For intSQLLoopCounter = 1 To UBound(sarySearchWord)

If Request.QueryString("searchMode") = "allwords" Then
BuildSQL = BuildSQL & " AND "
else
BuildSQL = BuildSQL & " OR "
End if

BuildSQL = BuildSQL & strTable & " LIKE '%" &
sarySearchWord(intSQLLoopCounter) & "%'"
Next

BuildSQL = BuildSQL & ")"
End Function


I haven't a clue as to what this query is. All i see is a bunch of vbscript
statements that I imagine are supposed to result in a string containing a
sql statement. Why not show us that instead?

Response.Write strSQL

Better yet:
1. Tell us what database type and version you are using
2. Show us some sample data (relevant fields only, please)
3. Show us the results you wish to obtain from the sample data
The operative word is "show". As in

field1 field1 field3
data data data
data data data
data data data
data data data

results:
field1 field1 field3
data data data
data data data

I strongly suggest you stop using dynamic sql for this.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.