Foreign Key Constraint error when adding new event

Nov 28, 2015 at 9:03 PM
Edited Nov 28, 2015 at 9:04 PM
Hi Sebastian

This error surfaced after I successfully ran turbo. Is there a way around it?

Thanks!

InnerMessage:The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Journal_ContentItems". The conflict occurred in database "423345_rheebo_2008", table "dbo.ContentItems", column 'ContentItemID'. The statement has been terminated.

InnerStackTrace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource
1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at PetaPoco.Database.Execute(String sql, Object[] args)
at DotNetNuke.Data.PetaPoco.PetaPocoHelper.ExecuteNonQuery(String connectionString, CommandType type, String sql, Object[] args)
at DotNetNuke.Services.Journal.JournalDataServiceImpl.Journal_UpdateContentItemId(Int32 journalId, Int32 contentItemId)
at DotNetNuke.Services.Journal.JournalControllerImpl.SaveJournalItem(JournalItem journalItem, Int32 tabId, Int32 moduleId)
at Ventrian.PropertyAgent.Social.Journal.AddPropertyToJournal(PropertyInfo objProperty, Int32 portalId, Int32 tabId, Int32 journalUserId, Int32 journalGroupID, String url, String summary)
at Ventrian.PropertyAgent.EditProperty.Update()
at Ventrian.PropertyAgent.EditProperty.cmdUpdate_Click(Object sender, EventArgs e)
Dec 31, 2015 at 11:58 AM
sorry for the late reply.
This seems to be an issue of Ventrian Property Agent, which adds a Journal item with a ContentItemID, which doesn't exist (TurboDNN enforces the Foreign Key Constraint).
I will try to reproduce and find a workaround
Dec 31, 2015 at 2:49 PM
Hi Sebastian

Thanks for looking into this. Sorry for the additional work.. I do appreciate you trying to find a workaround. Is there something I can do, like modify the ventrian stored procedure. I don't know sql well but can hack through things.

Have a happy new year. All the best for a prosperous 2016!

Carl
Dec 31, 2015 at 6:44 PM
Carl,
would you mind to run the following statement in Host > SQL, restart DNN and check, whether the issue persist:
IF EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Journal_UpdateContentItemId]'))
    DROP PROCEDURE {databaseOwner}[{objectQualifier}Journal_UpdateContentItemId]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Journal_UpdateContentItemId]
    @JournalId      int, -- required
    @ContentItemId  int  -- required, might be Null|-1
AS
BEGIN
    UPDATE {databaseOwner}[{objectQualifier}Journal]
        SET ContentItemId = CASE WHEN IsNull(@ContentItemId, -1) <=0 THEN Null ELSE @ContentItemId END
    WHERE JournalId = @JournalId
END /* Procedure */
GO
Dec 31, 2015 at 8:38 PM
Edited Dec 31, 2015 at 8:38 PM
Sebastian. Ran the SQL and received the same error as above.

Thanks for working on this.
Dec 31, 2015 at 10:52 PM
next try:
IF EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Journal_UpdateContentItemId]'))
    DROP PROCEDURE {databaseOwner}[{objectQualifier}Journal_UpdateContentItemId]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Journal_UpdateContentItemId]
    @JournalId      Int, -- required
    @ContentItemId  Int  -- required, might be Null|-1
AS
BEGIN
    DECLARE @CID Int = Null;
    SELECT @CID = [ContentItemId] FROM {databaseOwner}[{objectQualifier}ContentItems] WHERE ContentItemId = IsNull(@ContentItemId, -1);
    UPDATE {databaseOwner}[{objectQualifier}Journal]
     SET   [ContentItemId] = @CID
     WHERE [JournalId] = @JournalId
       AND IsNull([ContentItemId], -1) != IsNull(@CID, -1);
END /* Procedure */
GO
this should prevent the error to show up
Marked as answer by leupold on 1/10/2016 at 6:03 AM
Jan 1, 2016 at 12:40 AM
IT WORKED! How can I send you a beer? Thank You.
Jan 1, 2016 at 11:15 AM
Carl,
thanks for the feedback.
On the one hand side, I am glad that you got rid of the error message, on the other hand, the fix indicates a bug in the module, which tries to insert an invalid ContentItemID, which either indicates the ContenItems record not be created or the ContentID value not properly transferred. This might affect search index or future integrated functions of DNN using ContentItems (e.g. a comments repository).
Marked as answer by leupold on 1/10/2016 at 6:03 AM