Unexplainable syntax error with updateable view

[ahaws]

Hi guys. I am having this strange error on a form - FRMHELPEDIT - where I have an updatable view. When I select 'save' the changes - I get 'syntax error', and the program exits.(I ask yes (tableupdate) or no (tablerevert)- both options give me the same error) , I looked in my error log, but the line that has the error is on:
DO FORM FRMHELPEDIT - a call from another form altogether.
After I get the syntax error, I cannot 'quit' from the development area of VFP. It tells me that I cannot quit visual foxpro, and I must go to the task list and kill the task to get any further.

Is this a problem with VFP 7 and updateable views? It's driving me crazy! Any help is appreciated.
thanks
Angie

 

[mkrausnick]

Try opening the view in the view designer and viewing the SQL (click the SQL button on the View Designer toolbar) to see if there is an error in the SQL. If you are not a SQL expert, post the sql in your thread and we can look at it for you. If there is an error in the SQL, you might have to re-create the view using the CREATE VIEW command, since you can't modify the SQL directly in the view designer.

Mike Krausnick

 

[ahaws]

Hi Mike. This is so strange.

here's the code...so simple too:

SELECT Olhelp.code, Olhelp.desc, Olhelp.hmemo;
FROM olhelp;
WHERE Olhelp.code = ?lc_help

I did SET STEP ON, and after the save, I have a quit button, and it contains: thisform.release()
- it's throwing the error on the release of the form.
I will have no hair left!
Angie

 

[AllanB1]

Angie:

WHERE Olhelp.code = ?lc_help

Al

 

[ahaws]

Hi Al - not sure what your saying? It's a parameterized view, based on a primary key.
Angie

 

[mkrausnick]

The SQL looks OK to me, assuming the table exists. I don't use parameterized views much, do you need a trailing '.' after the parameter name?

If the error is occuring in thisform.release() then maybe the problem is somewhere in the closing of files, releasing of memvars, etc. or with one of the other objects on the form rather than with the view itself. For example I occasionally have trouble closing forms with grids on them, and it's usually because of something I did like closing the underlying table while the grid is still open, etc.

This might take some time, but you might try copying the form to a separate test form and deleting objects one at a time to see if you can get the form to close cleanly, or conversely, starting with an empty form, add objects with the events coded one at a time until the error occurs.
Mike Krausnick

 

[ramani]

Hi ahaws,

This error is in all probability, not in your views or any such SQL selects. I have seen, mis reporting of error in VFP7 and have spent numerous hours. Only thing is that, I have learnt to avoid this trap long back, and so still trying to recollect what was the exact problem. Hope I recollect that soon.

However, my suggestion is that, you leave the views and check other codes.. subsequent to the Selects, just check if any of the field you are refering is not in the view itself and belongs to some other alias, but erroneously referred without the proper alias. This could be somewhere else subsequent to the alias, but the SELECT is kept as the default alias.

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[ahaws]

Hi guys. Well I went to modify the view - and it kept asking where the table was. I looked and the table was there, So that got me thinking - the table has a cdx and fpt file associated with it.
I copied the cdx and fpt file from the live directory and placed it in my workarea, it asked if I wanted to overwrite the files that were already there. I said yes. Havent had a problem since. Maybe the index was bad for that table?

So strange - but at any rate - "syntax error" is not a very descriptive error message. I wish these guys writing the software could be less general

That reminds me of the time my coworker was installing Novell 6.0 client on a laptop - and he got this message:
"A terrible thing has happened. Program will close"
What is that? Too funny. Thanks for all your help!

Angie

 

[ahaws]

Well - I spoke too soon. It still is giving me that "syntax error". What in the heck is going on? I have built many forms that use updateable parameterized views and never experienced such frustration.

Ramani - I will look a little closer at my controlsources and let you know if I can find the problem.

Thanks!

 

[ahaws]

I am just not sure what's going on, but I do have 3 views on this form.
1. Parameterized - NonUpdateable
2. Non-Parameterized - NonUpdateable
3. Parameterized - Updateable

(1) is used to search on an item such as a name, and bring only those matches to the grid. Readonly. No data on load.

(2) is used to populate a grid with all the tables records (but only 3 fields from a 10 field table)- readonly.

(3) is used to edit/show one record given a primary key such as account number - Updateable. No data on load.


- when tyring to exit from the form, even though I tableupdate() or tablerevert() I'm getting the syntax error.
SO-
I've given up using the view to update. Now I use SCATTER and GATHER to populate. If anyone sees anything wrong with what I am doing, just shout
Thanks
Angie

 

[kueyiar]

Angie,
Did you ever find a solution for that "syntax error" message, i have the same problem.
antonio.

 

[jrajesh]

Hi ahaws,
I'd received this error long back.

The scenario:
Table with a unique index (primary) on a key.
A deleted record with key "A001"
Create and Save a record with the same key "A001" (from updateable view or otherwise). This generated the "Syntax error" message.

Adding a filter on deleted to the index fixed the problem.

Not sure whether this is the case with you.
Hope this helps.
Rajesh