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)

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?

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.

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.

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.