Hi,

Am just putting a search into my site and below is the code i am using

<% Dim arrSearch, strWhere, strSQL
arrSearch = Split(Request.QueryString("search", " "
strWhere = "WHERE product_name OR product_catagory OR product_briefdesc OR product_fulldesc OR product_code OR product_picture OR product_price LIKE '%" & arrSearch(i) & "%'"
For i = 0 To UBound(arrSearch)
strWhere = strWhere & " LIKE '%" & arrSearch(i) & "%'"
Next
strSQL = "SELECT * FROM products " & strWhere & ""
%>

i keep getting the error 'Subscript out of range: 'i''

What does it mean and how do i solve it. Any help please?

Regards

Simon

This error is telling you that the array doesn't contain the element (index number) you're trying to retrieve.

You could try putting your loop above your strWhere line, also you'll need to include an AND or OR operator in your strWhere assignment in your loop otherwise your SQL will fail. Don't forget you'll have one too many AND/OR operators in your strWhere variable built by your loop so you'll have to handle that.

Try doing Response.Write(strSQL) to view the SQL string, making sure statement is valid SQL, before testing on your DB.

Digga

Sharing Knowledge Saves Valuable Time!!!

many thanks for your reply. Where do i need to include additional AND or OR's, as i am not clear on that.

Cheers

Hi Simon

I didn't rally read your code in detail before, just understood the concept. Your code, looks like you want to create multiple LIKE statements.

Can you explain what you want to do?



Digga

Sharing Knowledge Saves Valuable Time!!!

HI Lee,

All that i am trying to do is have a single box search on my site that will look in all the columns in my database, but where the search doesnt have to be exact. Like if in the database it was 'Light' but the user put in 'lights' it would not return a result. thats all.

Many thanks for your help.

Hi Simon

I would do this with multiple search fields not just the one. As the single field approach means that all fields will be searched for a particular string that may only be contained in one field, a real waste of processing power. There's no way of differentiating between the elements in your array to pinpoint the search.

Your SQL WHERE clause will also fail as you need to state LIKE for each field:

WHERE product_name LIKE '%pname%' OR product_catagory LIKE '%pcat%' OR product_briefdesc LIKE '%pbrief%' OR product_fulldesc LIKE '%pfull%' OR product_code LIKE '%pcode%' OR product_picture LIKE '%ppict%' OR product_price LIKE '%pprice%'

You need to decide which fields you want to search on, do you really need product_picture?

Have a rethink Simon and post back.

Digga

Sharing Knowledge Saves Valuable Time!!!

Thanks for replying

All the fields in there arnt being used just quickly grabbed all the data and posted. Even doing it that way, and only searching on field in the way '%proddesc%' would only match exact words or part of. What i want to do say is if in the dayabase the word was light, but somebody searched with the word lights, adding the s, it would still return results with light in it.

Does this make sense?

Cheers

Hi Simon

Which database are you using?

Digga

Sharing Knowledge Saves Valuable Time!!!

Access with VB Script.

Hi Simon

I think you're going to be a bit limited with your search capabilities, not sure how you would achieve what you want even if possible, without some type of indexing, which Access cannot do (I think).

Enclose the wildcard(s) and the character string in single quotation marks, for example:

LIKE 'Mc%' searches for all strings that begin with the letters Mc (McBadden).

LIKE '%inger' searches for all strings that end with the letters inger (Ringer, Stringer).

LIKE '%en%' searches for all strings that contain the letters en anywhere in the string (Bennet, Green, McBadden).

LIKE '_heryl' searches for all six-letter names ending with the letters heryl (Cheryl, Sheryl).

LIKE '[CK]ars[eo]n' searches for Carsen, Karsen, Carson, and Karson (Carson).

LIKE '[M-Z]inger' searches for all names ending with the letters inger that begin with any single letter from M through Z (Ringer).

LIKE 'M[^c]%' searches for all names beginning with the letter M that do not have the letter c as the second letter (MacFeather).

If these above SQL techniques do not work, there should be an Access alternative, substituting * for % for example.

Also do a search in Access Help for LIKE operator, Specifying search conditions.


Digga

Sharing Knowledge Saves Valuable Time!!!