August 26, 2002 at 10:26 am
This returns the correct pricing in a cart where the Session("vType") collects the appropriate pricing level for the customer who is logged in. The Search can be done by entering the first 3 letters of a word; however, the word must appear in the first position in the custyprd table:
rsMaster.Open "SELECT * FROM CUSTYPRD WHERE [Prod-Desc] LIKE '" + vItemName + "%' AND TYPE = '" & Session("vType") & "'" , "DRIVER={SQL Server};SERVER=111;DATABASE=test;UID=test;PWD=test"
Example: search by spr returns only products beginning with sprinkles and displays the correct price.
******************************************
If we change the query to read like the following, then the word can appear anywhere in the [Prod-Desc] field in the custyprd table; however, the incorrect price is displayed. However, if we enter the complete word, then the correct pricing displays:
rsMaster.Open "SELECT * FROM CUSTYPRD WHERE [Prod-Desc] LIKE '%" + vItemName + "%' AND TYPE = '" & Session("vType") & "'" , "DRIVER={SQL Server};SERVER=111;DATABASE=test;UID=test;PWD=test"
Examples:
Search by spr returns products with the word sprinkles, spring, espresso anywhere in the [Prod-Desc] field; however, the incorrect price displays.
*****************************************
Objective:
To be able to enter the first 2 or 3 letters for Search by Description and capture the correct pricing for the customer - The search word should not have to be in any particular position in the search field [Prod-Desc]
August 26, 2002 at 10:35 am
Your method should work just fine. You must have a bug in your recordset loop that's displaying the prices.
If you run the sample query in Query Analyzer, does it give you the desired results? Can you capture the query using Profiler to make sure the correct values are being sent to SQL?
- Troy King
Edited by - katravax on 08/26/2002 10:36:13 AM
- Troy King
August 26, 2002 at 10:59 am
That is the issue...the correct pricing does display except....here is an example:
If the customer searches by spr (using query #2 in the original message):
Records returned are with incorrect pricing:
spring water
espresso
sprinkles
spring
However, if the customer queries with sprink then only records that have the word sprinkles anywhere in the description are returned with the correct price - again using query #2 in the original message.
Unfortunately, query #1 always returns the correct prices; however, the queried word must be in the first position in the [Prod-Desc] field.
We did note that MicroSoft has an explanation about this and recommends upgrading to 2000 - we did that.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q286787
We did run the query in Query Analyzer....no problem....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply