Prevent part match in database results

[Nags]

I have an asp page which users can find the correct ink cartridge for their
printer.
Each cartridge record has a field containing all the known model numbers,
normally separaed by a space, but sometimes beginning or ending <BR> .
The problem comes if the user enters a short model number eg 340, since
there are longer model numbers such as 3405, and the user gets two or more
results - only one being correct.
I could edit the HTML code but I can't figure out how to phrase it.
I really need to search for something like ([space]OR <BR>)&[ModelInfo]&
([space]OR <BR>)

The following seems to be the bit of htm to be modified

fp_sQry="SELECT * FROM MyNewInkModelData WHERE (ModelInfo LIKE
'%::ModelInfo::%' AND ProductCode LIKE 'hp.%') ORDER BY ItemTitle ASC"
fp_sDefault="ModelInfo="

Could someone help?

 

[Stefan B Rusynko]

For the leading/trailing space Try

fp_sQry="SELECT * FROM MyNewInkModelData WHERE (ModelInfo LIKE %" & " " & ModelInfo & " " & "% AND ProductCode LIKE 'hp.%') ORDER
BY ItemTitle ASC"

What the mean you are looking for is: " 340 " or " 3405 "


For the "<br>" recommend you always wrap each model # in a leading/trailing space before you insert the <br>
- then you won't need to search for it

|I have an asp page which users can find the correct ink cartridge for their
| printer.
| Each cartridge record has a field containing all the known model numbers,
| normally separaed by a space, but sometimes beginning or ending <BR> .
| The problem comes if the user enters a short model number eg 340, since
| there are longer model numbers such as 3405, and the user gets two or more
| results - only one being correct.
| I could edit the HTML code but I can't figure out how to phrase it.
| I really need to search for something like ([space]OR <BR>)&[ModelInfo]&
| ([space]OR <BR>)
|
| The following seems to be the bit of htm to be modified
|
| fp_sQry="SELECT * FROM MyNewInkModelData WHERE (ModelInfo LIKE
| '%::ModelInfo::%' AND ProductCode LIKE 'hp.%') ORDER BY ItemTitle ASC"
| fp_sDefault="ModelInfo="
|
| Could someone help?

 

[Nags]

Assuming I trawl through all my records and ensure a space around each model
number, it still has the problem:
One record will include model number 340
Another will include 3408 (say)
If the user is looking for 340, he gets the correct result containing 340
plus the incorrect record containing 3408

 

[Ian Haynes]

Assuming I trawl through all my records and ensure a space around each

model
number, it still has the problem:
One record will include model number 340
Another will include 3408 (say)
If the user is looking for 340, he gets the correct result containing 340
plus the incorrect record containing 3408

Then surely you don't want to use 'LIKE' but rather '=' ?? (The '%' are
wildcard characters.)

So

WHERE (ModelInfo = '" & ModelInfo & "'"

or however the code is constructed.

 

[Nags]

Hmmm... the "wizard" form offers "contains" or "like"
I'll try "=" in the code

 

[Nags]

If I try atering the Java code, it gets overwritten on saving.
Trying again using the "wizard" to set up:
Using "contains" is the only option that gives anything.
Both "like" and "equals" give no results, since each field contains lots of
model numbers, all in text format, some including letters.
Any suggestions how I search for the value entered with a space either side?
If I was doing a query it would be something like: " "&[ModelInfo]&" "
A promising bit of code before the Java is:
<input NAME="ModelInfo" VALUE="<%=Server.HtmlEncode(Request("ModelInfo"))%>"
Any help would be most appreciated

 

[Ian Haynes]

Having saved a copy of the page try altering the code itself.

From

WHERE (ModelInfo LIKE
'%::ModelInfo::%' AND ProductCode LIKE 'hp.%')

to

WHERE (ModelInfo =
':ModelInfo::' AND ProductCode LIKE 'hp.%')

taking the wildcard %'s out too.

 

[Nags]

No luck.
As I say, FPage overwrites any change in the Java part of the code on
saving,so it cant be edited.
Any similar suggestions for the other bit of code I mentioned before ?
This isnt within the Java bit.

And if % means wild card.
What does the colon mean?

 

[Ronx]

First, there is no Java in the code. The code is VBscript.

Change the code in the greyed out comments, and save whilst in Code
View. When you save the Maroon code will be changed to match the grey.

The % indicates a wild card, the :: is a delimiter placed at each end of
a form field name.

You will need to post a sample of both the field contents so that we can
see what you are trying to do.
I assume from your posts that the product number field contains data
similar to:

" 123 1234 340 3408 a23 3408a " (without the quotes).
Question - which field contains the model number? Modelinfo or
Productcode?
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

 

[Stefan B Rusynko]

Because the DB field is a list of model #'s separated by a space,
- the OP can not use an = and must use a Like
My understanding is the field has something similar to
" 123 1234 340 3408 a23 3408a "
An = 3408 will fail
Only a Like " 3408 " will find 3408

That is the problem when the DB is designed in the manner as above
- and a good reason for Not designing fields that way



|> Assuming I trawl through all my records and ensure a space around each
| > model
| > number, it still has the problem:
| > One record will include model number 340
| > Another will include 3408 (say)
| > If the user is looking for 340, he gets the correct result containing 340
| > plus the incorrect record containing 3408
| >
|
| Then surely you don't want to use 'LIKE' but rather '=' ?? (The '%' are
| wildcard characters.)
|
| So
|
| WHERE (ModelInfo = '" & ModelInfo & "'"
|
| or however the code is constructed.
|
|
| --
| Ian Haynes
| MS MVP FrontPage
| http://www.ew-resource.co.uk
|

 

[Nags]

Thanks for your input, I'll try as advised
In the meantime:
In design view it does say Javascrip.
The field containing the model numbers is Modelinfo.
Productcode is the indexed primary field.
Modelinfo includes all the known model numbers for that product code
eg A3405 7659 7764xi
Another record might contain A340 776
There's also the problem that many of the ModelInfo contents are like:
A3409 5468<BR>8760 8879 5670<BR>5645 8907

 

[Ronx]

To overcome the <br> problem use
Where(((ModelInfo like '::ModelInfo:: %') OR (ModelInfo like '%
::ModelInfo:: %') OR (ModelInfo like '%<br>::ModelInfo:: %') OR
(ModelInfo like '% ::ModelInfo::<br>%') OR (ModelInfo like
'%<br>::ModelInfo::<br>%') OR (ModelInfo like '% ::ModelInfo::')) AND
(ProductCode LIKE 'hp.%'))

This caters for all possible variations, including the first and last
model numbers in the field.

In my opinion a different system should be used, where each model number
is in a record of its own.

Note that every record must have ModelInfo starting and ending with
either a model number that is not used, or with a space or <br>
sequence. If not, the first and last model numbers will not be found.

The asp code (between <% and %> symbols) which runs on the server will
be VBscript, and JavaScript is definitely NOT Java.
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

Thanks for your input, I'll try as advised
In the meantime:
In design view it does say Javascrip.
The field containing the model numbers is Modelinfo.
Productcode is the indexed primary field.
Modelinfo includes all the known model numbers for that product code
eg A3405 7659 7764xi
Another record might contain A340 776
There's also the problem that many of the ModelInfo contents are like:
A3409 5468<BR>8760 8879 5670<BR>5645 8907

 

[Ronx]

Ignore this paragraph in the previous post:

Note that every record must have ModelInfo starting and ending with
either a model number that is not used, or with a space or <br>
sequence. If not, the first and last model numbers will not be found.



--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

 

[Nags]

Thanks Ronx, you got me on the right track.
Altering the GREY code above the Javascript code like this works:
WHERE (((ModelInfo LIKE '% ::ModelInfo:: %') OR (ModelInfo LIKE
'%<BR>::ModelInfo:: %') OR (ModelInfo LIKE '% ::ModelInfo::<BR>%')) AND
ProductCode LIKE 'cn.%')
There was a problem in that when the search field is empty (ie when the user
comes across the page), it should show all the records.
In those records that didnt include either two spaces together, or a <BR>
and a space together, they did not show, so I had to add a couple of spaces
to each ModelInfo field data.
GREAT. Thank you all