Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
correct way to update in vfp7/8 1
- Thread starter kueyiar
- Start date
Mike Lewis
Programmer
kueyiar,
When you say you want to update a view, do you mean that you want the view to update the underlying table? If so, go to the Update Criteria page in the view designer, and specify the key field(s) and the updatable field(s), and enable Send SQL Updates. Then, when you modify the data in the view, the undelying table will also be updated.
To requery a view, just call the REQUERY() function.
If I've misunderstood your question, perhaps you could rephrase it.
Mike
Mike Lewis
Edinburgh, Scotland
My Visual Foxpro web site: My Crystal Reports web site:
When you say you want to update a view, do you mean that you want the view to update the underlying table? If so, go to the Update Criteria page in the view designer, and specify the key field(s) and the updatable field(s), and enable Send SQL Updates. Then, when you modify the data in the view, the undelying table will also be updated.
To requery a view, just call the REQUERY() function.
If I've misunderstood your question, perhaps you could rephrase it.
Mike
Mike Lewis
Edinburgh, Scotland
My Visual Foxpro web site: My Crystal Reports web site:
- Thread starter
- #3
Mike,
The thing is mike, i have the view with send sql updates, pk, updatable fields and everything(its a parametrized view by the way), i run a =requery(), it asks me for the data i wish to search, i input it, it shows me the result, i issue a =tableupdate(.t.) and when i issue a =requery() again to see if the changes where updated i get a "syntax error" message and i'm not able to continue running the form unless i issue a =tablerevert(.t.), so the thing is am i using the correct syntax or something cause i come from vfp3 throug 6 and have always been doing it this way without any problems, just as i updated to vfp7 and 8 i encounter this..
antonio
The thing is mike, i have the view with send sql updates, pk, updatable fields and everything(its a parametrized view by the way), i run a =requery(), it asks me for the data i wish to search, i input it, it shows me the result, i issue a =tableupdate(.t.) and when i issue a =requery() again to see if the changes where updated i get a "syntax error" message and i'm not able to continue running the form unless i issue a =tablerevert(.t.), so the thing is am i using the correct syntax or something cause i come from vfp3 throug 6 and have always been doing it this way without any problems, just as i updated to vfp7 and 8 i encounter this..
antonio
If by "update" you mean fix the .DBC's View due to changes in the underlying tables, then I know of three possibilities.
1) Recreate the view - standard MS response.
2) Use Stonefield Database Toolkit - it will "automatically" fix them for you.
3) Use the free ViewEditor available at A Professional version with even more capabilities is at These will fix it "manually" for you.
Rick
1) Recreate the view - standard MS response.
2) Use Stonefield Database Toolkit - it will "automatically" fix them for you.
3) Use the free ViewEditor available at A Professional version with even more capabilities is at These will fix it "manually" for you.
Rick
- Thread starter
- #5
I'm using the vieweditor .exe that you recommended and its supposedly telling me that the alias of the .dbc is not assigned correctly for instas where it shoutd be hotels!charges.code appears only as charges.code
Its supposed to be the problem or isn't it !?
antonio
Its supposed to be the problem or isn't it !?
antonio
Are you using the free version in VFP 7.0? I'm not sure it works correctly in VFP 8.0 - it wasn't compiled for it, although the "Pro" version was!
It's difficult to "guess" what it's trying to say. You might want to contact Rick Schummer with your details at While he has responded on Tek-Tips, it was a while ago - he's been busy on his new book - just released last week - "Deploying Visual FoxPro Solutions"
Rick
It's difficult to "guess" what it's trying to say. You might want to contact Rick Schummer with your details at While he has responded on Tek-Tips, it was a while ago - he's been busy on his new book - just released last week - "Deploying Visual FoxPro Solutions"
Rick
- Thread starter
- #7
Well, i'm trying it on vfp7.
Its very strange, i just created a new table on a new .dbc
i tried creating a new record, issued the =tableupdate(.t.), used =requery(), modified the record, issued another =requery() and it worked, the thing is i closed the .dbc and tried to do the same operation, but got the "syntax error" again...
I really don't know what could be wrong..
Please help me out...
Its very strange, i just created a new table on a new .dbc
i tried creating a new record, issued the =tableupdate(.t.), used =requery(), modified the record, issued another =requery() and it worked, the thing is i closed the .dbc and tried to do the same operation, but got the "syntax error" again...
I really don't know what could be wrong..
Please help me out...
-
1
- #8
RickSchummer
Programmer
Syntax Error": Have you traced through the code to see the exact line that you are getting the error? It could be that the view is not properly constructed, or it could be code firing in an event you may not have considered at this point.
How about posting the view definition code (Show SQL in the VFP 8 View Designer (since it shows the DBSETPROP(), or "Copy View Code" button in the ViewEditor. I suspect that VFP is getting tripped over the syntax as it is creating the SQL-Update.
_RAS
VFP MVP
How about posting the view definition code (Show SQL in the VFP 8 View Designer (since it shows the DBSETPROP(), or "Copy View Code" button in the ViewEditor. I suspect that VFP is getting tripped over the syntax as it is creating the SQL-Update.
_RAS
VFP MVP
- Thread starter
- #9
_RAS
The error occurs when i try a =requery() right after i have issued a =tableupdate(.t.)
This is what the structure of the view looks like
SELECT *;
FROM ;
charges;
WHERE Charges.code LIKE ( ?parA )
DBSetProp(ThisView,"View","SendUpdates",.T.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","charges")
DBSetProp(ThisView,"View","WhereType",3)
DBSetProp(ThisView+".code","Field","DataType","C(5)")
DBSetProp (ThisView+".code","Field","UpdateName","charges.code")
DBSetProp(ThisView+".code","Field","KeyField",.T.)
DBSetProp(ThisView+".code","Field","Updatable",.T.)
DBSetProp(ThisView+".desc","Field","DataType","C(30)")
DBSetProp(ThisView+".desc","Field","UpdateName","charges.desc")
DBSetProp(ThisView+".desc","Field","KeyField",.F.)
DBSetProp(ThisView+".desc","Field","Updatable",.T.)
DBSetProp(ThisView+".value","Field","DataType","N(9,2)")
DBSetProp(ThisView+".value","Field","UpdateName","charges.value")
DBSetProp(ThisView+".value","Field","KeyField",.F.)
DBSetProp(ThisView+".value","Field","Updatable",.T.)
a strange thing i see in the designer is that it creates a line on the join tab with a "cross join". I erase it but automatically recreates it.
antonio
The error occurs when i try a =requery() right after i have issued a =tableupdate(.t.)
This is what the structure of the view looks like
SELECT *;
FROM ;
charges;
WHERE Charges.code LIKE ( ?parA )
DBSetProp(ThisView,"View","SendUpdates",.T.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","charges")
DBSetProp(ThisView,"View","WhereType",3)
DBSetProp(ThisView+".code","Field","DataType","C(5)")
DBSetProp (ThisView+".code","Field","UpdateName","charges.code")
DBSetProp(ThisView+".code","Field","KeyField",.T.)
DBSetProp(ThisView+".code","Field","Updatable",.T.)
DBSetProp(ThisView+".desc","Field","DataType","C(30)")
DBSetProp(ThisView+".desc","Field","UpdateName","charges.desc")
DBSetProp(ThisView+".desc","Field","KeyField",.F.)
DBSetProp(ThisView+".desc","Field","Updatable",.T.)
DBSetProp(ThisView+".value","Field","DataType","N(9,2)")
DBSetProp(ThisView+".value","Field","UpdateName","charges.value")
DBSetProp(ThisView+".value","Field","KeyField",.F.)
DBSetProp(ThisView+".value","Field","Updatable",.T.)
a strange thing i see in the designer is that it creates a line on the join tab with a "cross join". I erase it but automatically recreates it.
antonio
RickSchummer
Programmer
Couple things I would look at:
1) charges.value could be confusing the "compiler" because you use a reserve word as a column name. This is dangerous and will produce strange behavior. I would seriously consider changing the column name, but this can break lots of code.
2) I never create views with *, this is why views break. Columns are added or removed in a table and now the view definition is broken (see the DBSETPROPs define the columns, but now one is missing or added). Specify the columns and your views will never break. The downside is that as you add columns in the table you have to add the columns to the view (more maintenance, but safer in my opinion).
3) This join is strange. I would expect to see another table in the mix. Make sure their is no table hidden somewhere in the designer.
4) =TABLEUPDATE() is dangerous. You need to check to see the result is .T., if not, you need to check why with AERROR(). REQUERY() with a dirty buffer (failed update will leave the buffer dirty) will trigger an error.
_RAS
VFP MVP
1) charges.value could be confusing the "compiler" because you use a reserve word as a column name. This is dangerous and will produce strange behavior. I would seriously consider changing the column name, but this can break lots of code.
2) I never create views with *, this is why views break. Columns are added or removed in a table and now the view definition is broken (see the DBSETPROPs define the columns, but now one is missing or added). Specify the columns and your views will never break. The downside is that as you add columns in the table you have to add the columns to the view (more maintenance, but safer in my opinion).
3) This join is strange. I would expect to see another table in the mix. Make sure their is no table hidden somewhere in the designer.
4) =TABLEUPDATE() is dangerous. You need to check to see the result is .T., if not, you need to check why with AERROR(). REQUERY() with a dirty buffer (failed update will leave the buffer dirty) will trigger an error.
_RAS
VFP MVP
- Thread starter
- #11
RickSchummer
Programmer
Code:
IF TABLEUPDATE(< insert appropriate parameters >)
REQUERY(< insert view name >)
ELSE
AERROR(laDataError)
* Check the content of array to determine problem
ENDIFOne more thing to look at is a corrupt DBC. You might want to validate the database to see if there are any problems.
_RAS
VFP MVP
- Thread starter
- #13
RickSchummer
Programmer
So we know nothing more than we did earlier {g}.
Was there anything else in the AERROR array that might prove interesting?
Is the line of code with the error really nothing more than: =tableupdate(.t.)? Is there a possibility that the TABLEUPDATE() command has bad syntax?
I also do not understand the reasoning behind the JOIN in your View Designer. Did you solve this?
If I were you, what I would do is recreate the view under a different name and see if it has the same strange behavior. I would also rewrite the line of code for the update in case there is a syntax error hidden in the code.
_RAS
VFP MVP
Was there anything else in the AERROR array that might prove interesting?
Is the line of code with the error really nothing more than: =tableupdate(.t.)? Is there a possibility that the TABLEUPDATE() command has bad syntax?
I also do not understand the reasoning behind the JOIN in your View Designer. Did you solve this?
If I were you, what I would do is recreate the view under a different name and see if it has the same strange behavior. I would also rewrite the line of code for the update in case there is a syntax error hidden in the code.
_RAS
VFP MVP
- Thread starter
- #15
This is how i tried to update it: IF TABLEUPDATE(1,.T.,'VCHARGES')
and i tried :IF TABLEUPDATE(.T.) as well..same error
I've recreated under a differente location the table, .dbc and view, yet the error still appears..
By the way the JOIN still get created automatically..
and i tried :IF TABLEUPDATE(.T.) as well..same error
I've recreated under a differente location the table, .dbc and view, yet the error still appears..
By the way the JOIN still get created automatically..
RickSchummer
Programmer
This is definitely strange. Are there any persistant relationships set up between this table and other tables in the database? If so, RI code generated?
_RAS
VFP MVP
_RAS
VFP MVP
- Thread starter
- #17
Nope no relationships at all. I created a new tryout .dbc , table and view to see if it where something like that, but the error remains.
I did a search for error code 10 on vfp help and all it says is "syntax error"... I really don't know what could be wrong, why can it let me append a new record, but not modify it...
I did a search for error code 10 on vfp help and all it says is "syntax error"... I really don't know what could be wrong, why can it let me append a new record, but not modify it...
RickSchummer
Programmer
OK, back from extensive testing in VFP and was able to reproduce your problem exactly. I think I have solved the problem.
I was partially correct with my second response, with respect to key words. In your case it looks to be the view parameter. parA is the same as PARA which is the four character abbreviation for PARAMETER. When I changed this in the view the view now updates successfully. If I see blue keywords in the code editor in a SQL Select I immediately question my sanity with my column selection. Same goes for view parameters. In my case I never see this error because all my view parameters are prefixed with "vp_". No VFP command or function starts with this string.
I hope this solves your issue.
_RAS
VFP MVP
I was partially correct with my second response, with respect to key words. In your case it looks to be the view parameter. parA is the same as PARA which is the four character abbreviation for PARAMETER. When I changed this in the view the view now updates successfully. If I see blue keywords in the code editor in a SQL Select I immediately question my sanity with my column selection. Same goes for view parameters. In my case I never see this error because all my view parameters are prefixed with "vp_". No VFP command or function starts with this string.
I hope this solves your issue.
_RAS
VFP MVP
- Thread starter
- #19
Its funny that you mention it, i've been using it that way since vfp5 and never had this problem. I tried your suggestion, but no luck at all, still get the same error..
Any other thoughts..
Do you have any special setting or set pre defined on your work area i may be missing ?
Any other thoughts..
Do you have any special setting or set pre defined on your work area i may be missing ?
RickSchummer
Programmer
One more thing, I used the ViewEditor and the View Designer. When I tested this problem I worked mostly in the VFP View Designer to reproduce the problem.
Here is the code of a working view using the same structures (without the reserved word names) in the columns and the view parameter.
If this does not work, I am out of ideas.
_RAS
VFP MVP
Here is the code of a working view using the same structures (without the reserved word names) in the columns and the view parameter.
Code:
CREATE SQL VIEW V_CHARGES2VIAVE AS ;
SELECT charges2.ccode, ;
charges2.cdescript, ;
charges2.nvalue;
FROM testproblems!charges2 ;
WHERE charges2.ccode like ?vp_ccode
* View Properties
DBSetProp("V_CHARGES2VIAVE", 'View', 'UpdateType',1)
DBSetProp("V_CHARGES2VIAVE", 'View', 'WhereType',3)
DBSetProp("V_CHARGES2VIAVE", 'View', 'FetchMemo',.T.)
DBSetProp("V_CHARGES2VIAVE", 'View', 'SendUpdates',.T.)
DBSetProp("V_CHARGES2VIAVE", 'View', 'UseMemoSize',255)
DBSetProp("V_CHARGES2VIAVE", 'View', 'FetchSize',100)
DBSetProp("V_CHARGES2VIAVE", 'View', 'MaxRecords',-1)
DBSetProp("V_CHARGES2VIAVE", 'View', 'Tables',[testproblems!charges2])
DBSetProp("V_CHARGES2VIAVE", 'View', 'Comment',[])
DBSetProp("V_CHARGES2VIAVE", 'View', 'BatchUpdateCount',1)
DBSetProp("V_CHARGES2VIAVE", 'View', 'ShareConnection',.F.)
DBSetProp("V_CHARGES2VIAVE", 'View', 'Prepared',.F.)
DBSetProp("V_CHARGES2VIAVE", 'View', 'CompareMemo',.T.)
DBSetProp("V_CHARGES2VIAVE", 'View', 'FetchAsNeeded',.F.)
DBSetProp("V_CHARGES2VIAVE", 'View', 'RuleExpression',[])
DBSetProp("V_CHARGES2VIAVE", 'View', 'RuleText',[])
DBSetProp("V_CHARGES2VIAVE", 'View', 'ParameterList',[vp_cCode,'C'])
* View Field Properties
DBSetProp("V_CHARGES2VIAVE.ccode",'Field','KeyField',.T.)
DBSetProp("V_CHARGES2VIAVE.ccode",'Field','Updatable',.T.)
DBSetProp("V_CHARGES2VIAVE.ccode",'Field','UpdateName',[testproblems!charges2.ccode])
DBSetProp("V_CHARGES2VIAVE.ccode",'Field','DataType',[C(5)])
DBSetProp("V_CHARGES2VIAVE.ccode",'Field','Caption',[])
DBSetProp("V_CHARGES2VIAVE.ccode",'Field','DefaultValue',[])
DBSetProp("V_CHARGES2VIAVE.ccode",'Field','RuleExpression',[])
DBSetProp("V_CHARGES2VIAVE.ccode",'Field','RuleText',[])
DBSetProp("V_CHARGES2VIAVE.cdescript",'Field','KeyField',.F.)
DBSetProp("V_CHARGES2VIAVE.cdescript",'Field','Updatable',.T.)
DBSetProp("V_CHARGES2VIAVE.cdescript",'Field','UpdateName',[testproblems!charges2.cdescript])
DBSetProp("V_CHARGES2VIAVE.cdescript",'Field','DataType',[C(30)])
DBSetProp("V_CHARGES2VIAVE.cdescript",'Field','Caption',[])
DBSetProp("V_CHARGES2VIAVE.cdescript",'Field','DefaultValue',[])
DBSetProp("V_CHARGES2VIAVE.cdescript",'Field','RuleExpression',[])
DBSetProp("V_CHARGES2VIAVE.cdescript",'Field','RuleText',[])
DBSetProp("V_CHARGES2VIAVE.nvalue",'Field','KeyField',.F.)
DBSetProp("V_CHARGES2VIAVE.nvalue",'Field','Updatable',.T.)
DBSetProp("V_CHARGES2VIAVE.nvalue",'Field','UpdateName',[testproblems!charges2.nvalue])
DBSetProp("V_CHARGES2VIAVE.nvalue",'Field','DataType',[N(9,2)])
DBSetProp("V_CHARGES2VIAVE.nvalue",'Field','Caption',[])
DBSetProp("V_CHARGES2VIAVE.nvalue",'Field','DefaultValue',[])
DBSetProp("V_CHARGES2VIAVE.nvalue",'Field','RuleExpression',[])
DBSetProp("V_CHARGES2VIAVE.nvalue",'Field','RuleText',[])If this does not work, I am out of ideas.
_RAS
VFP MVP
- Status
Similar threads
- Locked
- Question
