VFP9 Requery 1

[asergeev]

Hi,
Our program updates a parameterized remote view against MS SQL Server. When we are trying to REQEURY this view after successful update with a new parameter VFP 9 doesn't update view and REQUERY returns -1. VFP 9 Help doesn't provide any information about return value of -1 for REQUERY.

Here is code example.

* Open remote parameterized view
use abc!remvw in 0

*VFP asks for parameter input
*Input parameter value: '000000342'

browse
* VFP opens view with required record.

* Change some field
replace fld1 with "abc"

* Commit changes
?TABLEUPDATE(.T.)

*Result .T.

*Let's REQUERY with a new parameter value

parm1 = '000000348'
?REQUERY('remvw')

*Result -1 !!!

browse
*View contains previous record.

When we are doing the same in VFP8 we don't have any problems with REQUERY - it returns 1 and view has a new record.

Any thoughts will be helpful.

Thanks,
Andrei

 

[jesylv]

Did you test your code using Set EngineBehavior 80



Jean

http://www.sigp.ca

 

[Mike Lewis]

Andrei,

You need to find the error that SQL Server has returned. To do so, add this code after your REQUERY():

AERROR(laError)
  ? laError(1)   && display error message
ENDIF

As an alternative, enable the "Display Warnings" checkbox in the Connection Designer. That will cause error messages from the server to be displayed in your development environment. But be sure to disable it again before handing it over to the user.

Jean: SET ENGINEBEHAVIOR won't help here, as this is a SQL Server issue. ENGINEBEHAVIOR applies to VFP's SQL syntax.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jesylv]

Hi Mike,

You may be right but Andrei mentionned that he did not have that problem in VFP8. it seems logical to test this possibility...

PS Thanks for your numerous contributions to this community

Jean

http://www.sigp.ca

 

[asergeev]

Mike,
Thank you, as always, for your help.
I checked an AERROR results and they were the following:

VFP Error number: 1526
Error Message: Connectivity error: [Microsoft][ODBC Driver Manager] Function sequence error
ODBC Error Message: [Microsoft][ODBC Driver Manager] Function sequence error
ODBC Current State: S1010
The error number from the ODBC data source: 0
The ODBC connection handle: 1

I also tried to monitor SQL server response using SQL profiler, but the VFP call doesn't go beyond ODBC.
So far I couldn't find anything helpful to correct the above error.

Thanks,
Andrei.

 

[Mike Lewis]

Andrei,

Hmmm. A "function sequence error" basically means that you are trying to do two things at the same time (that's an oversimplification, but it gives the general picture). Typically, you would get his if you are requerying two views at the same time on the same connection. Or, perhaps requerying a view while the same view is still be requeried from an earlier attempt.

This is an ODBC error, by the way, and has nothing to do with SQL Server.

Does that help at all?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[asergeev]

Mike,
Everything that I was doing was from command window in VFP. Nobody else was using the same connection and I was not running any other operations along with REQUERY. I also unset ASYNCHRONOUS EXECUTION option in connection, but it did not help.
I found another recommendation for such ODBC error - to use OLEDB instead of SQL driver, but unfortunately remote views don't allow to use OLEDB connections.
And the most important that we don't have this type of error in VFP8. It should be something new (or buggy) to VFP 9.

Thanks,
Andrei.

 

[Mike Lewis]

Andrei,

Sorry I can't suggest anything further. I can only say that I have been using remote views extensively in VFP 9.0, and have never seen this problem.

Let us know if you eventually come up with a solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[asergeev]

Mike,
I solved the problem by installing VFP 9 SP1 beta. It was maybe a VFP bug that came out in my environment. Microsoft did patch couple places with REQUERY (see the fix list

http://download.microsoft.com/downl...-43d3-ada1-8d0ea0e6803e/VFP9SP1_FixList.htm).

Thanks once more.
Andrei.

 

[Mike Lewis]

Andrei,

That's good to hear. It never occurred to me to consider the SP.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)