StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Problem with Access DB Insert

Problem with Access DB Insert

Kevin Lingofelter — Sat, 08 Apr 2006 12:57:38 GMT

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?

RE: Problem with Access DB Insert

Trent L. Taylor — Sat, 08 Apr 2006 12:57:38 GMT

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!:D

RE: Problem with Access DB Insert

Kevin Lingofelter — Sat, 08 Apr 2006 10:47:35 GMT

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!

RE: Problem with Access DB Insert

Kevin Lingofelter — Sat, 08 Apr 2006 00:25:06 GMT

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!

RE: Problem with Access DB Insert

Trent L. Taylor — Fri, 07 Apr 2006 19:41:40 GMT

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?

RE: Problem with Access DB Insert

Kevin Lingofelter — Fri, 07 Apr 2006 10:22:33 GMT

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.

RE: Problem with Access DB Insert

Trent L. Taylor — Fri, 07 Apr 2006 10:15:08 GMT

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 :P

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.

RE: Problem with Access DB Insert

Kevin Lingofelter — Thu, 06 Apr 2006 15:28:08 GMT

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

RE: Problem with Access DB Insert

Trent L. Taylor — Thu, 06 Apr 2006 12:34:41 GMT

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

RE: Problem with Access DB Insert

Kevin Lingofelter — Wed, 05 Apr 2006 19:50:14 GMT

Duh...forgot to fill the BO...:D