Microsoft KB Archive/246656

= PRB: SQL SELECT Cursor Resultset Changes when Variable Changes =

Article ID: 246656

Article Last Modified on 5/12/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q246656



SYMPTOMS
The resultset of a cursor does not match the selection criteria from a SQL SELECT statement that includes a variable in the WHERE clause.

-or-

An error message similar to the one below is displayed when attempting to browse a cursor created with a SQL SELECT statement that includes a variable that is out of scope in the WHERE clause.

Variable 'MyVariable' not found



CAUSE
This behavior occurs when SET EXACT is set to OFF.



RESOLUTION
 Set EXACT to ON.  Use a SQL SELECT statement that does not set a filter condition: MyVariable="Test" SELECT * FROM MyTable WHERE MyField=MyVariable AND .T. INTO CURSOR MyCursor 



Steps to Reproduce Behavior
  Create a program file named DEMOPRG using the following code: luExact_Value=SET('EXACT') CLOSE ALL IF !FILE('testa.dbf') CREATE TABLE testa (col1 c(5),col2 c(5)) INDEX ON col1 TAG col1 OF testa INSERT INTO testa (col1,col2) VALUES ("1","a") INSERT INTO testa (col1,col2) VALUES ("2","b") INSERT INTO testa (col1,col2) VALUES ("3","c") INSERT INTO testa (col1,col2) VALUES ("3","d") INSERT INTO testa (col1,col2) VALUES ("4","e") INSERT INTO testa (col1,col2) VALUES ("4","f") INSERT INTO testa (col1,col2) VALUES ("5","g") ENDIF IF !FILE('testb.dbf') CREATE TABLE testb (col1 c(5),col2 c(5)) INSERT INTO testb (col1,col2) VALUES ("1","a") INSERT INTO testb (col1,col2) VALUES ("2","b") INSERT INTO testb (col1,col2) VALUES ("3","c") INSERT INTO testb (col1,col2) VALUES ("3","d") INSERT INTO testb (col1,col2) VALUES ("4","e") INSERT INTO testb (col1,col2) VALUES ("4","f") INSERT INTO testb (col1,col2) VALUES ("5","g") ENDIF SET EXACT OFF DO demoa WITH '1' BROWSE TITLE DBF TIMEOUT 10 && Get variable MyVariable_a not found message with the index
 * !* SET EXACT ON

MyVariable="3" DO demob BROW TITLE DBF+" Note values in COL1" TIMEOUT 10 MyVariable="2" BROWSE TITLE DBF+" Now note values in COL1" TIMEOUT 10

MyVariable="4" DO alt_method BROW TITLE DBF+" Alternate Method Note values in COL1" TIMEOUT 10 MyVariable="3" BROWSE TITLE DBF+" Alternate Method Now note values in COL1" TIMEOUT 10 SET EXACT &luExact_Value CLOSE ALL

PROCEDURE demoa PARAMETER MyVariable_a SELECT * FROM testa NOWAIT WHERE col1=MyVariable_a INTO CURSOR cursortesta

PROCEDURE demob SELECT * FROM testa NOWAIT WHERE col1=MyVariable INTO CURSOR cursortesta

PROCEDURE alt_method SELECT * FROM testa NOWAIT WHERE col1=MyVariable AND .T. INTO CURSOR cursortesta  Run the program and note that when the first BROWSE command is executed, the following error message is displayed:
 * !* This illustrates a SQL SELECT statement that does not set a filter condition

Variable 'MyVariable_a' not found

When this error message appears, select Ignore. Note the number of records and the values displayed when the second BROWSE command is executed. The values displayed should be as follows:

</li> Note the number of records and the values displayed when the third BROWSE command is executed. The values displayed should be as follows:

</li> Note the number of records and the values displayed when subsequent BROWSE commands are executed. The values displayed should be as follows:

</li>  Change the following line of code from: SET EXACT OFF to: SET EXACT ON and re-run the program. Note that the Variable Not Found error message does not occur and also note that the second and third times the BROWSE command is executed the cursor resultset remains constant. </li></ol>

Keywords: kbsqlprog kbdatabase kbprb kbpending KB246656

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.