FOREIGN KEY problem


FOREIGN KEY problem
View
Options
Author
Message
Doron Farber
Doron Farber
Doron Farber
posted 16 Years Ago
StrataFrame User (152 reputation)StrataFrame User (152 reputation)StrataFrame User (152 reputation)StrataFrame User (152 reputation)StrataFrame User (152 reputation)StrataFrame User (152 reputation)StrataFrame User (152 reputation)StrataFrame User (152 reputation)StrataFrame User (152 reputation)
Group: Forum Members
Posts: 92, Visits: 612
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)
Reply Like 0
Greg McGuffey
Greg McGuffey
Greg McGuffey
posted 16 Years Ago
Strategic Support Team Member (3.3K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
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.
Reply Like 0
Peter Jones
Peter Jones
Peter Jones
posted 16 Years Ago
Advanced StrataFrame User (504 reputation)Advanced StrataFrame User (504 reputation)Advanced StrataFrame User (504 reputation)Advanced StrataFrame User (504 reputation)Advanced StrataFrame User (504 reputation)Advanced StrataFrame User (504 reputation)Advanced StrataFrame User (504 reputation)Advanced StrataFrame User (504 reputation)Advanced StrataFrame User (504 reputation)
Group: Forum Members
Posts: 386, Visits: 2.1K
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

Reply Like 0
Trent Taylor
Trent Taylor
Trent Taylor
posted 16 Years Ago
StrataFrame Developer (10K reputation)StrataFrame Developer (10K reputation)StrataFrame Developer (10K reputation)StrataFrame Developer (10K reputation)StrataFrame Developer (10K reputation)StrataFrame Developer (10K reputation)StrataFrame Developer (10K reputation)StrataFrame Developer (10K reputation)StrataFrame Developer (10K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 6.9K
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.
Reply Like 0
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...



Submit

Similar Topics

Post Quoted Reply
Reading This Topic

Powered by InstantForum 2017-1 Final © 2024
Execution: 0.000. 3 queries. Compression Enabled.

Login

Explore
Messages
Mentions
Search