By Brooks Adair - 4/15/2007
I have created a Business Object mapping to a VFP table. I have created a maintenance form and mapped a few fields to it. Navigation works fine, editing and saving an existing record works fine, but when I save a new record I get an exception: "Command contains unrecognized phrase/keyword." After the error message I am left in edit mode. However, If I check the table, the record has been saved.
I searched the forum and found out about the debug log. So I set it up. It appears that the command causing the error is "SELECT @@IDENTITY". It looks like the initial INSERT command goes fine, and I guess the second command is an attempt to retrieve the newly generated primary key value. Am I doing something wrong here? Where do I need to look or what do I need to change?
Below is the stack trace:
BusinessLayerException
An error occurred while saving an the data to the server.
DataLayerSavingException
Command contains unrecognized phrase/keyword.
OleDbException
Command contains unrecognized phrase/keyword.
Source : MicroFour StrataFrame Business
Stack Trace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteScalar()
at MicroFour.StrataFrame.Data.DbDataSourceItem.InternalExecuteScalar(DbCommand Command, Boolean IsTransactional, String TransactionKey)
at MicroFour.StrataFrame.Data.VfpDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)
at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)
at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
at MicroFour.StrataFrame.Data.DataLayer.SaveByForm(DataTable TableToSave, Boolean Transactional, String TransactionKey)
at MicroFour.StrataFrame.Business.BusinessLayer.SaveByForm(Boolean Transactional, String TransactionKey)
at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save(Boolean Transactional, String TransactionKey)
at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save()
at MicroFour.StrataFrame.UI.Windows.Forms.MaintenanceFormToolStrip.cmdSave_Click(Object sender, EventArgs e)
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
|
By Trent L. Taylor - 4/16/2007
VFP does not support the @@identity statement to automatically return the primary key. To correct the problem, double-click your BO in the solution designer and set the PrimaryKeyIsAutoIncrementing to False. Then give it another try and you should be good to go. 
|
By Brooks Adair - 4/16/2007
I tried that and got some other error at a different point. I am not at the system with the problem. I will try and get back with more information.
Thanks for the quick reply.
|
By Trent L. Taylor - 4/16/2007
Just let me know what you are getting know. We update VFP tables in high volume so it is very possible obviously  I will look for your post.
|
By Brooks Adair - 4/16/2007
OK, I set PrimaryKeyIsAutoIncremented to False. Then when I save I get an error that the primary key field was read-only. I tried setting the PK field in the BO Mapper to ReadOnly and that made no difference.
I then tried adding the primary key to FieldsToExlcudeFromInsert, that resulted in the error "Cannot create INSERT command because the updating DataTable does not contain columns for all PrimaryKeyFields."
What am I missing?
|
By StrataFrame Team - 4/17/2007
What version of VFP database are you using?I believe all of the VFP tables we've worked with are from back before VFP tables allowed the PK to be auto-incremented, so we have to manually set our PKs. If you're working with 9+, then we might have to update the VfpDataSourceItem to retrieve the auto-assigned primary key from the database. I'm not sure it does that right now...
|
By Brooks Adair - 4/17/2007
Yes. The tables are from VFP 9.0 with SP1.
|
By StrataFrame Team - 4/17/2007
I'll have to take a look at this. No promise on the timeframe; I'll have to discuss it with the rest of the development team.
|
By Brooks Adair - 4/17/2007
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.
|
By Trent L. Taylor - 4/18/2007
Does it use full Rushmore optimization? How does the speed compare to VFP?
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.
|
By Brooks Adair - 4/21/2007
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?
|
By StrataFrame Team - 4/23/2007
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.
|
By StrataFrame Team - 4/23/2007
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.
|
By Brooks Adair - 4/23/2007
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.
|
By Calvin Y Tang - 1/31/2008
HiI 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
|
By Trent L. Taylor - 2/1/2008
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.
|
By Calvin Y Tang - 2/1/2008
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.
|
By Trent L. Taylor - 2/1/2008
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  ).
|