StrataFrame Forum

FOREIGN KEY problem

http://forum.strataframe.net/Topic17214.aspx

By Doron Farber - 6/20/2008

Hi All,

There could be some situations that I have several pick list related to some main table. But when I want to save the record on the main table I get the below err unless I go into the Enforce Forgiven Key Constraint and set it up to Off.  The other pick list FK values could be either a NULL or  value.

Is there something that can be done in SF to avoid the below error?

Thanks

Doron


BusinessLayerException
  An error occurred while saving an the data to the server.
DataLayerSavingException
  The INSERT statement conflicted with the FOREIGN KEY constraint "MemberShipType to MemberShipInfo". The conflict occurred in database "HumanFindAdmin1", table "dbo.MemberShipType", column 'MemberShipType_PK'.
The statement has been terminated.
SqlException
  The INSERT statement conflicted with the FOREIGN KEY constraint "MemberShipType to MemberShipInfo". The conflict occurred in database "HumanFindAdmin1", table "dbo.MemberShipType", column 'MemberShipType_PK'.
The statement has been terminated.
Source     : MicroFour StrataFrame Business
Stack Trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at MicroFour.StrataFrame.Data.DbDataSourceItem.InternalExecuteReader(DbCommand Command, Boolean IsTransactional, String TransactionKey)
   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.MaintenanceFormToolStrip.cmdSave_Click(Object sender, EventArgs e)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   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.ToolStrip.WndProc(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)
By Greg McGuffey - 6/20/2008

This is error is the result of SQL foreign key (FK) constraints. I.e. you have foreign keys that don't have values (and they must, enforced by SQL server). You have to set these FKs before saving a record.



You have several options to get this done:



If you are using the parent relationship, that will take care of one of one of them, assuming you set the parent bo on the instance on the form. I believe currently, SF only allows one parent (i.e. one FK relationship) to be defined (I also seem to recall that is changing soon), so for the others you'll have to use one of the other methods.



If the FK is known when the record is created (when calling the BO's Add method, e.g. clicking on the New button on the maintenance toolstrip), then you can use the BO's SetDefaultValues event handler (on the form) to set the FKs.



If the FKs are data the user chooses, then you would just bind the FK fields to (typically) combos that show the FK values they can choose from. Then in the BO, you'd set these fields are "required", so a broken rule is used if they user doesn't select a value.



Hope that makes sense.
By Peter Jones - 6/20/2008

Hi Doron,

Also, if you can permit a null foreign key in SQL Server then you need to set the FK to Nullable in SQL Server.

Cheers, Peter

By Trent L. Taylor - 6/23/2008

Both Greg and Peter's posts are very good...this has nothing to do with SF but rather how you have your constraints setup on the SQL Server side....so if you want to prevent this error, then you will need to provide all of the contraints (or comply to the contraints) or remove the contraints from the SQL Server side if it is not correct.