A general question regarding how those of you out there using SPT and Auto update cursors onto a Client Server back ends deal with record inserts into a table.
As some of you may well know, I'm in the process of writing a set of data access classes for VFP to Client server back ends. OK, you may say, it has been done before but I wanted a specific architecture for a project I'm involved in so thought it would be a good idea to develop a set of classes from the floor up. My question is, when you use SPT to generate a VFP cursor which you then make updatable, how do you handle the insertion of records? I know how I do it and have always done it, but that is not the point and I simply wanted to get some feedback from others. Specifically: 1 Do you allow free access to the VFP cursor and native VFP commands like "append blank" and "insert into..." as well as the Auto insert when using grids to display the attached cursor which in effect controls the data from the front end GUI down and auto updates the back end? OR 2. Handle all the table insertions via an SPT "Insert into..." and then requery the back end from VFP to fill the GUI controls with the newly added blank record data. Why, you may ask do I need this information? Well I just came across a VFP/SPT situation where using (1) above results in a type of deadly embrace or impasse situation when using Identity fields (the equivalent of VFP Autoinc fields) on the server for Primary Keys. When adding a record into the client/server table the SQL Server automatically generates the identity (Primary Key) at the back end and then you use (in M$SQL anyway) the "select @@Identity" command to retrieve back the new Server PK into VFP if you require it for further processing, the PK in the VFP cursor will be 0 however and this is the problem. You can't write the Server PK back into the VFP cursor to make the client and server mirror each other, as you get a table conflict error. This is caused because the PK in the VFP table (which VFP sees as 0) is now different to the correct PK on the server and apart from anything else you cannot modify PK's from the front end anyway as the server will throw an error!! However, we presumably now want to populate this newly inserted record with data in VFP so the only way to do this is to requery the whole dataset of records in the dataset from the server which gives you the newly inserted record which you can navigate to, update with record the new data fields from VFP and allow auto update to write back the changes to the back end. I can currently see no other way of doing this without the requery unless anyone has any other ideas. Note that for existing records this is no problem as the PK is never "out of sync" between VFP and the server. I guess we are really spoilt when it comes to using Grids with VFP in that we can scroll up/down, insert and delete records on the fly without bothering with the underlying data changing because we always see live data in the grid, not a background dataset copy. Obviously, the best thing is simply to avoid using live data grids and go back to the record by record approach from days gone by where you can only edit the record that is being worked on in a Form which always refreshes the data from the server before going into edit mode - the equivalent of the 'requery' stage above. As I say, I'm interested in finding out how others out there do it. A bit of a diatribe, I know but explaining it is not as easy as it seems. Comments welcome. Dave Crozier _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
