Hello,

I have a BO setup for an Access table. I've bound the textboxes' data field in a maintenence form. All looks good.

After starting the program, I click "New" edit all the fields. If I click on "Save", I get this error:

BusinessLayerException
 An error occurred while saving an the data to the server.
DataLayerSavingException
 Syntax error in INSERT INTO statement.
OleDbException
 Syntax error in INSERT INTO statement.

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.ExecuteNonQuery()
   at MicroFour.StrataFrame.Data.AccessDataSourceItem.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)

And I cannot save the record. I'm not sure what I'm doing wrong. Any ideas?

Duh...forgot to fill the BO...

Kevin,

If this happens again let me know.  In the pre-release we added a "debug" option that will show you all of the SQL commands before they are executed so you can see more clearly what is going on.  It also allows you to send it to us so we can look at it as well.  At any rate, glad you got it going.

Hmm..I've seem to have found this behavior again. How do I access the debug option?

You can turn on a debug mode for each data connection independently in your application.  So if you only have a single data connection the code below is exactly what you can enter.  This code should be added after the SetConnections() method call in your AppMain.vb or Program.cs class.  If you are manually specifying the connection string and not going through the Connection String Manager, then make sure your connection has been added to the DataSources collection before calling this code, otherwise...BOOM

MicroFour.StrataFrame.Data.DataLayer.DataSources.Item("").DebugMode = True

Once you add this code, run your application.  All SQL statements will be brought up in a message window so you can see everything going on.  This is a good tool to debug the statements.

Ok..I've added the code and I am getting the SQL debug window during the data retrieval methods, however, when I try to save a record, the error comes up, but no debug window.

Kevin,

Can you populate the business object from the Access database and navigate through some records?  If so, can you edit an existing record, make a change, and save?

Yes. I created a simple FillAll method (select * from blah). The BO populates and I can navigate through the records, but I cannot update or insert a record. It must have something to do with schema of this table because I don't have any problems with other tables in the database.

I'm curious...shouldn't the debug window show the SQL used to update the table before it's executed? It would probably help track down the problem. This one is a stumper!

BTW...I downloaded the 4/6/2006 PR. Is there any change notes on this version?

Thanks!

Ok...I figured it out!! I had a field named "LongText". This happens to be a reserved word, but Access allows it's use (the program I'm putting together is a port from an existing Access app).

For the time being, I've renamed the field, so everything is working ok on my end now. Just curious, when Access is specified in the connection, does the BO surround the field names and tables in brackets when building the SQL? If not, adding them may keep this from happening.

Thanks!

We already add the brackets for you.  If you open the YourBO.Designer.vb (or .cs) file and go down to where the properties are defined, you can see that we use brakets to prevent any conflicts with reserved words.  This will only resolve the conflicts with .NET reserved words though and you can still have problems on the side of the database.  Glad that you figured it out!