FoxPro doesn't support @@IDENTITY, so the code has been added to return the MAX value of the AutoInc field which should be safe since VFP only supports integer data types for an AutoInc field and any new record added should reliably be the MAX value in the table.

The BEST solution is to get any VFP data moved over to SQL Server ASAP so that you don't run into the VFP bottlenecks...that would be my first suggestion (coming from massive amounts of VFP experience ).

Thank you, I will try that out.

Does this return the last ID for the current DB session like @@identity?  or just return the max number for the table?  Because I am new to FoxPro, so not sure how that works in VFP thru the OLEDB.

OK....sorry for the delay here, but we went ahead and added the functionality to support the retrieval of an AutoInc field for VFP.  The problem was that there was an @@IDENTITY command in the VFPDataSourceItem that is only supported by SQL Server.  So we changed this query for the VFPDataSourceItem to execute a scalar method to retrieve the MAX(AutoInc) field after the save so that the BO will be updated correctly.  Attached is the Base assembly and a sample that shows how it works...it really doesn't require anything other than setting the PrimaryKeyFieldIsAutoIncrementing property to True.

Be sure to drag the MicroFour StrataFrame Base.DLL into the GAC (C:\Windows\Assembly) and also copy it to the C:\Program files\Common Files\MicroFour\StrataFrame folder.  Then get out of any open Visual Studio instances and then go back in.  You should be able to run the attached sample and create a new record to see how the PK is updated.  Let me know if you have any issues or questions regarding this.

Hi

I am getting the same error on VFP 9.0 SP1 data.  For autoInc field, I have turned off PrimaryKeyIsAutoIncrementing and include the field in FieldsToExlcudeFromInsert, but it is giving me the error  "Cannot create INSERT command because the updating DataTable does not contain columns for all PrimaryKeyFields."

Since I am dealing with an existing program from another party, so I cannot make any change to the data structure.  What is the best way to handle autoInc Primary key in FoxPro?

Thanks,

Calvin

Thanks for the info, sounds like the VFPOLEDB provider will be good for us.



Even in VFP I try to use SQL SELECTS over SCAN loops when I can. I have seen replacing a SCAN loop with a good SELECT drastically reduce execution time.

As long as you remember that you're not directly connected to the database, you'll be good to go.  Don't write a method like you would in VFP where you use a bunch of SEEKs and SCANs across 10 tables to find your data.  If you create 10 BOs and fill them and then try to Seek() on them, it's going to be slower.  Convert all of those SEEKs and SCANs down into some good SELECTs and only bring in what you need and you'll be fine.

We have a prescription medication database that contains several million records for testing drug/drug interations when prescribing medications for patients.  Several of the 75 or so tables have 1M+ records in them.  We haven't noticed a slowdown on selecting records from the table, however, we have used a few local views in VFP, and it runs much faster when accessing the view through OLE DB when we use the Enterprise Server and the IIS for the ES is on the same machine as the .dbc containing the views and .dbfs for the files (cuts down on the network traffic between the view and the tables).  The only slowdown you're going to notice is probably inserting records.  SELECTs are fast, but when you do an INSERT through OLE DB, it's not quite as fast as the talking directly to the data through a VFP app.  Nothing to complain about, but if you're inserting 100+ records in a loop, you'll notice it.

Thanks for the info.



I just purchased a full version of StrataFrame and upgraded my trial version. Your framework has allowed me to get up to speed on .NET a lot quicker than I would have otherwise. Thanks for the great product!



One more question on the VFPOLEDB drivers. I have seen other VFP programmers state very adamantly that it should not be used in any real .NET development. Apparently they had many problems with it and they recommend SQL Server Express.



However, it sounds like you guys have made extensive use of it with out any problems. What is the largest database you have accessed with it?

Yes.  The VFPOLEDB provider is basically the same engine that you use within the VFP run-time environment, so most of the features available at run-time are also respected through the most recent OLEDB provider.  There are some limitations to the OLEDB provider, but there are also work arounds for most of these.

Ok, thats fine. Since I am writing a small app from scratch, and have complete control over my table structure, I just switched to using GUIDs for my primary keys.



It would be a good idea though to add support for AutoInc fields to a future version of StrataFrame. Could be very helpful to those VFP developers that have to migrate now that VFP development has been stopped by MS.



BTW I have never used VFP tables through OLEDb. Does it use full Rushmore optimization? How does the speed compare to VFP?



Thanks for your help.