VFP 9 Odd String Search Problem

It is hard to answer because above lines of code are separated from some bigger code snippet and we don't know all necessary dependences...

You should also distinguish between "Ite does not work" and "It causes an error".
If "it does not work" then you should explain how it should behave, e.g. "it returns .F. but I would expect .T."
If "it causes an error" then you have to write the error number and/or error message.

You did not even disclose the value in lcDescr...

Following commands must work under all obvious circumstances:

lcLookForProd = "Product #"
lcDescr = "Some longer text containing the Product # string"
IF (m.lcLookForProd $ m.lcDescr)
  ? "1. Correct result of the above expression"
ELSE
  ? "1. Unexpected program continuation"
ENDIF
IF ("Product #" $ m.lcDescr)
  ? "2. Correct result of the above expression"
ELSE
  ? "2. Unexpected program continuation"
ENDIF

Select allOpen in new window

Note "m." prefixes in above code which tell to interpreter "use memory variables if the field of the same name exists".

And now what can I say to your questions:
"IF ("Product #" $ lcDescr) : works with both Product ID pulled from db and assigned number." ... I don't see any relation between Product ID and this command.

"IF (lcLookForProd $ lcDescr) : this works when I manually assign the number, but not when it's pulled from the db" ... Again, the number assignement is not visible here.

IF ((lcLookForProd) $ lcDescr)  ... This will "work" if both variables(?) will contain a text string. The result will depend on the string value.
IF ('lcLookForProd' $ lcDescr)  ... This command will search the "lcLookForProd" string inside the lcDescr variable or inside the lcDescr field in a table open in current workarea
IF (('lcLookForProd') $ lcDescr) ... same behavior as the previous command (extra parentheses are not necessary).

It will be better if you describe what you want to achieve and we may help to define the right FoxPro command.

Another option is to place SET STEP ON command to your code and then you may test various expressions and commands in VFP Command windows or in the Debug wwindow. It is fast and easy and you'll better understand what values are you working with.

> IF (lcLookForProd $ lcDescr) : this works when I manually assign the number, but not when it's pulled from the db

As pcelba mentioned, this has absolutely nothing to do with a numeric value, either from a lookup or manual assignment. Both are string variables, correct?

If not, there's your first clue. I suspect you have a variable assigned a string sometimes and a numeric other times or something of that ilk.

From what you show, the lnProducts_ID is not used at all. So what do you expect?

And if you want to add lnProducts_ID to lcLookForProd, you need to convert it to a string, you can't add a number to a string.

Or another question: Your expression looks for lcLookForProd in lcDescr. How is lnProducts_ID involved in it?

What error do you get?

Bye, Olaf.

Sorry, I was trying to make it short as I can't expect you to weed through all my code to find the problem. But I realize now that without it you can't possibly see what's going on.

Here's the code and the problem:

*** Note: I distinguish between a character variable and a numeric variable by prefixing the character with 'lc' and the numeric with 'ln'

lcLookForProd = "Product #"

lcQuery="SELECT product_id FROM product"
SQLEXEC(pnConnection,lcQuery,'PIDU')
IF !USED('PIDU')
	DO SQLQueryError IN error_messages WITH lcQuery
ENDIF
IF RECCOUNT('PIDU') > 0 
	SELECT ('PIDU')
	SCAN
	llProdURL = .F.
	
		lnProducts_ID = pidu.product_id && this returns the only product id in the table, 59569.
		
		&& When run either the line directly above this or directly below this is commented out.
		
		lnProducts_ID = 59569 && the same number but directly assigned for testing purposes.
		
	lcProducts_Id = ALLTRIM(STR(lnProducts_ID))

		IF VARTYPE(lnProducts_ID) = "N"
			WAIT WINDOW "its numeric"
			&& fires in both cases showing the value is numeric
		ENDIF
		WAIT WINDOW "the value is " + lcProducts_ID && both show the correct '59569'
		
		lcQuery="SELECT product_description FROM product " + ;
		"WHERE products_id = ?lcProducts_ID"  
		SQLEXEC(pnConnection,lcQuery,'DESC')
		IF !USED('DESC')
			DO SQLQueryError IN error_messages WITH lcQuery
		ENDIF
		
			WAIT WINDOW "reccount desc = " + lcProducts_ID + "-" + ALLTRIM(STR(RECCOUNT('DESC'))) && both return '1'
		
		IF RECCOUNT('DESC') > 0 
		
			lcDescr = desc.product_description
			lcDescr = ALLTRIM(m.lcDescr)
			IF USED('DESC')
				USE IN ('DESC')
			ENDIF
			
				WAIT WINDOW "lcdescr = " + SUBSTR(m.lcDescr,1,165) && both display the exact same content in the wait window. The content includes 'Product #'
			
			IF (m.lcLookForProd $ m.lcDescr)
				llProdURL = .T.
				WAIT WINDOW "1- llprodurl = T"
				&& directly assigned number returns .T.
				&& same exact number pulled from db returns .F. THIS IS THE PROBLEM.
			ELSE
				WAIT WINDOW "1- llprodurl = F"
			ENDIF

Select allOpen in new window

The absolutely only difference is that in one instance the value of '59569' is pulled from a database, and in the other I directly assign it to the variable. Yet the one pulled from the db returns .F. in the last statement, while the one directly assigned returns .T. as it should.

Hopefully this is clearer and you can see my dilemma.

Just thought I'd try a little more comparison on lnProducts_ID, so I did:

lcQuery="SELECT product_id FROM product"
SQLEXEC(pnConnection,lcQuery,'PIDU')
IF !USED('PIDU')
	DO SQLQueryError IN error_messages WITH lcQuery
ENDIF
IF RECCOUNT('PIDU') > 0 
	SELECT ('PIDU')
	SCAN
	llProdURL = .F.
	
		lnProducts_ID1 = pidu.product_id && this returns the only product id in the table, 59569.
		
	&& both are uncommented for this test
		
		lnProducts_ID2 = 59569 && the same number but directly assigned for testing purposes.
		
		
	lcProducts_Id1 = ALLTRIM(STR(lnProducts_ID1))
	lcProducts_Id2 = ALLTRIM(STR(lnProducts_ID2))

	IF lnProducts_ID1 == lnProducts_ID2
		WAIT WINDOW "they're equal numerically"
		&& both fire this
	ENDIF
	IF lcProducts_ID1 == lcProducts_ID2
		WAIT WINDOW "they're equal as characters"
		&& both fire this
	ENDIF

Select allOpen in new window

In every way I know how to test they are exactly the same, yet this is the one and only difference and must ultimately be the cause of why the
IF (m.lcLookForProd $ m.lcDescr)
returns .F. when it should return .T.

pcelba - Thanks.
You are correct about the query. I accidentally altered the table name from product_description to product in the second query only on paste here though. It is correct in the program and the product_id field in the second table is in fact products_id (don't ask me why, I didn't design it).

In the program the query is correct and the testing showes that the values are exactly the same.

I also attempted the query using lnProducts_ID as opposed to lcProducts_ID as you pointed out in (2) above but with the exact same results.

One more funny bit to add to the puzzle though. If I substitute "Product #" for every occurrence of lcLookForProd, then it returns .T. for both as it should.

I've narrowed it down to it's simplest form. I pulled out anything unnecessary along with the second mysql lookup and replaced it in this code with the actual sting value from the db assigned to lcDescr.

lcLookForProd = "Product #"

lcQuery="SELECT product_id FROM product"
SQLEXEC(pnConnection,lcQuery,'PIDU')
IF !USED('PIDU')
	DO SQLQueryError IN error_messages WITH lcQuery
ENDIF
IF RECCOUNT('PIDU') > 0 
	SELECT ('PIDU')
	SCAN
	llProdURL = .F.
	lnProducts_ID = pidu.product_id
*!*		lnProducts_ID = 59569
				
	lcProducts_Id = ALLTRIM(STR(lnProducts_ID))
		
	lcDescr = "Terminates Unused F Ports On Splitters, Amplifiers, Combiners, etc. May Help To Reduce Signal Interference. Also great with Product #59563 and Category #10385."
			
	IF (lcLookForProd $ lcDescr)
		llProdURL = .T.
		IF lcProducts_ID = "59569"
			WAIT WINDOW "llprodurl = T"
		ELSE
			WAIT WINDOW "llprodurl = F"
		ENDIF
	ENDIF
	
		
		IF lcProducts_ID != "59569"
			LOOP
		ENDIF
		SELECT ('PIDU')
		
ENDSCAN
ENDIF

Select allOpen in new window

The results are the same and still make no sense. As before I swap between pulling lnProducts_ID from the db and directly assigning it.

IF (lcLookForProd $ lcDescr)
Returns .T. when directly assigned.
Returns .F. when number is pulled from db.

* If:
IF ("Product #" $ m.lcDescr)  && returns .T.
* BUT:
lcLookForProd = "Product #"
IF (m.lcLookForProd $ m.lcDescr)  && returns .F.

*then you have to compare:
IF (m.lcLookForProd == "Product #")

The lcLookForProd variable could be overwritten or it can contain hard space instead of space etc. Everything is possible today... but I have never seen string modified this way without the appropriate command in VFP program.

Do you use this variable on some other place in your code?

IF (lcLookForProd $ lcDescr)
Returns .T. when directly assigned.
Returns .F. when number is pulled from db.

How do you recognize  .F.  in your code?  Did you see the WAIT WINDOW "llprodurl = F" ?
How many records is in the PIDU cursor? Did you check the  pidu.product_id  value?

Are you testing in VFP development version?  SET STEP ON is your best friend...

I trashed the whole thing, re-wrote it incorporating some query suggestions by pcelba and a few other suggestions here and now it works.

I'm not sure why and as much as I'd like to know, I have to move on.

I appreciate all the help here and I'm on to the next step which I'm likely to post a question about here shortly.

As always thanks to all for the help

Ok, problem solved by rewrite...

But you don't see the wood for the trees.

You say: As before I swap between pulling lnProducts_ID from the db and directly assigning it.

It has nothing to do with (lcLookForProd $ lcDescr), has it? How is the numeric value involved in that statement at all?

Just one thought: Doing (lcLookFor $ cDescr) in a WHERE clause or FOR clause does check this for all records. Just doing ? (lcLookFor $ lcDescr) at the command line does this for the current record only, and if you' at EOF you get .F., because at EOF every field is taken as empty value, eg you check an empty cDescr field.

You bark up the wrong tree. Do you have an error handler telling you error and line of error at all?

Bye, Olaf.