Hi all -



I just went through a pretty intense debugging session based on two BOs that are hooked up to two separate SQL Server tables, both of which have a set of Instead-of triggers, some of which fire off calls to other stored procedures. The journey through the various paths is a bit too convoluted to recap here.



Now that I have come out on the other side there are some questions I have regarding BOs wired up to tables on which triggers exist. I am letting the data layer gen the SQL for .Save()s (i.e., not using stored procedures to handle the Save()).



Part of the problem I was chasing down had to do with the following exception:

BusinessLayerException

An error occurred while saving an the data to the server.

DataLayerSavingException

Invalid attempt to read when no data is present.

InvalidOperationException

Invalid attempt to read when no data is present.



Source : MicroFour StrataFrame Business



Stack Trace:

at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)

at System.Data.SqlClient.SqlDataReader.get_Item(String name)

at MicroFour.StrataFrame.Data.SqlDataSourceItem.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.UpdateDataTable(DataTable TableToUpdate, Boolean Transactional, String TransactionKey)

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)

Looking at some of the postings on here led me to consider changing the SET NOCOUNT setting on a few of the triggers. That worked for some, but not all of them. I had also previously read about the need to have stored procedures do a SELECT rather than a RETURN to give StrataFrame something to lock into on the return.



Based on the above:



1.) It's looking to me like whatever happens on the backend better be returning a result set... even if it's a "bogus" one that is created by doing something like SELECT @@ROWCOUNT. Yes?



2.) Where can I find a write-up that discusses this, and any other requirements that would come into play such that future triggers are developed with these requirements in mind?



3.) What does StrataFrame interrogate in the data returned from SQL Server? As part of the problem I was troubleshooting there was a scenario where a SELECT in a separate sproc called by one of the triggers was actually allowing the trigger--which would have otherwise thrown the above exception--to "succeed." This seems a bit dangerous to me.



TIA



________________

_____/ Regards,

____/ al


________________
_____/ Regards,
____/ al