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)

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.

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.

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.

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?

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...

Yes. The tables are from VFP 9.0 with SP1.

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.

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.

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.