TABLEUPDATE problem with remote view to SQL data

[samoody]

We have a program that updates SQL data through a remote view. Inside a PRG, the remote view is opened and then buffered
CURSORSETPROP("Buffering", 5, "formsview")

The correct record is found and then a field in that record is changed and then updated using…
TABLEUPDATE(0,.F.,'formsview')

The view itself is a straight query of SQL data with no filters or joins. I have double and triple checked the view definition and everything appears to be set up properly.

The problem is the update works inconsistently. Sometimes all works correctly, other times the update fails. I have been unable to pinpoint exactly why this is happening.

I have done the exact same change to the same record in the data and had it work sometimes and not work other times. So, I believe I have ruled out access or data validation issues.

Does anyone have any insight into this problem? Any help would be appreciated.

 

[Mike Lewis]

A couple of points to try:

- Try doing the TABLEUPDATE() twice. The first time you do it, it will commit changes to the view. The second time, it will actually send the updates.

- Check the "SQL Where" settings on the update criteria page. If your updates include a memo field, for example, you might have to select "key fields only".

Also, with this type of problem, it helps enormously if you can tell us what back end you are using. "SQL data" doesn't convey very much. Remote views behave differently with different back ends.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[samoody]

Thanks Mike... Hope this makes things a little clearer.

The Data is stored in Microsoft SQL Server. The 'SQL Where' clause is set to key fields only.
The
TABLEUPDATE(0,.F.,'formsview')
statement returns TRUE sometimes and FALSE other times.

Would issuing a TABLEUPDATE twice help in this case?

 

[Mike Lewis]

If TABLEUPDATE() returns .F., you need to find out why. One way to do that would be to trap the SQL Server error, for example, like so:

IF NOT TABLEUPDATE(0,.F.)
* Something went wrong
AERROR(laErrorArray)
MESSAGEBOX(laErrorArray(3))
ENDIF

That way, you will see why the update failed. Perhaps you could try that and report back.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[samoody]

I figured it out...

I believe the problem was....

My SQL table had 114 records. My remote view only was pulling 100 records at a time. Therefore when I tried to do a tableupdate, I received a connection is busy error, because it was still in the process of retrieving records. When it was working correctly, I'll bet the SQL table had less than 100 records in it. (The number of records in this table does fluctuate)

I changed my remote view to pull all records (under the Advanced Option menu) and all worked correctly. This table should never be that large, so I think I can get away with pulling all records each and every time.

Thanks for pointing me in the right direction!