Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Jason Espin 368 posts 1335 karma points
    Oct 24, 2014 @ 13:15
    Jason Espin
    0

    Cannot save or modify data Umbraco back office

    Hi all,

    I am working with Umbraco v7.1.8 and have created a new site using the NuGet package in Visual Studio. Everything was working fine in my development environment and I am ready to start testing my site on multiple devices so I have ported the database and published the code the staging server. I have ensured that all of the relevant permissions have been set on the Umbraco folders using the following recommendations:

    http://our.umbraco.org/wiki/reference/files-and-folders/permissions

    The problem I now have is when I try and save or update or modify an content in the back office I am presented with the following error message:

    Server error: Contact administrator, see log for full details. Cannot insert the value NULL into column 'id', table 'AxumWebsite.dbo.cmsContentVersion'; column does not allow nulls. INSERT fails. The statement has been terminated.

    Does anyone know how to address this?

    The following is what is presented in the log file:

    System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'ForceRedirect', table 'AxumWebsite.dbo.icUrlTracker'; column does not allow nulls. INSERT fails. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at umbraco.DataLayer.SqlHelpers.SqlServer.SqlServerHelper.ExecuteNonQuery(String commandText, SqlParameter[] parameters) at umbraco.DataLayer.SqlHelper1.ExecuteNonQuery(String commandText, IParameter[] parameters) ClientConnectionId:4f0e9253-32b0-4546-a0f2-0e55798c1fc4 2014-10-24 12:03:49,264 [319] INFO Umbraco.Core.Publishing.PublishingStrategy - [Thread 254] Content 'Web Development' with Id '1085' has been published. 2014-10-24 12:03:49,373 [319] INFO Umbraco.Core.Persistence.UmbracoDatabase - [Thread 254] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>cDisplayClassa.9() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Insert(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco) 2014-10-24 12:04:11,993 [319] ERROR umbraco.DataLayer.SqlHelper1[[System.Data.SqlClient.SqlParameter, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] - [Thread 11] Error executing query INSERT INTO icUrlTracker (OldUrl, RedirectRootNodeId, OldUrlQueryString, Is404, Referrer) VALUES (@oldUrl, @redirectRootNodeId, @oldUrlQueryString, 1, @referrer) System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'ForceRedirect', table 'AxumWebsite.dbo.icUrlTracker'; column does not allow nulls. INSERT fails. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at umbraco.DataLayer.SqlHelpers.SqlServer.SqlServerHelper.ExecuteNonQuery(String commandText, SqlParameter[] parameters) at umbraco.DataLayer.SqlHelper1.ExecuteNonQuery(String commandText, IParameter[] parameters) ClientConnectionId:b4dad3d7-4ab9-4ccd-ba9b-db85767d8c4e 2014-10-24 12:04:15,488 [319] INFO Umbraco.Core.Publishing.PublishingStrategy - [Thread 258] Content 'Web Development' with Id '1085' has been published. 2014-10-24 12:04:15,503 [319] INFO Umbraco.Core.Persistence.UmbracoDatabase - [Thread 258] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>cDisplayClassa.9() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Insert(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco) 2014-10-24 12:05:27,435 [319] INFO Umbraco.Core.Publishing.PublishingStrategy - [Thread 64] Content 'Web Development' with Id '1085' has been published. 2014-10-24 12:05:27,451 [319] INFO Umbraco.Core.Persistence.UmbracoDatabase - [Thread 64] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>cDisplayClassa.9() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Insert(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco) 2014-10-24 12:08:09,972 [319] INFO Umbraco.Core.Publishing.PublishingStrategy - [Thread 127] Content 'Web Development' with Id '1085' has been published. 2014-10-24 12:08:10,003 [319] INFO Umbraco.Core.Persistence.UmbracoDatabase - [Thread 127] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>cDisplayClassa.9() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Insert(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco)

    At first I thought it was a database connection issue but then the front end of the site would not display properly if this were the case. Any help would be greatly appreciated.

  • Ismail Mayat 4405 posts 9633 karma points MVP 2x admin c-trib
    Oct 24, 2014 @ 13:22
    Ismail Mayat
    0

    Jason,

    How did you port the db? Did you create a sql script or use sql pubishing wizard?  Basically i have had similar issue before where i have scripted db with data, however if you do db backup then restore then that works fine.

     

    Regards

     

    Ismail

  • Jason Espin 368 posts 1335 karma points
    Oct 24, 2014 @ 15:02
    Jason Espin
    0

    Hi Ismael,

    Our hosting provider does not allow us to do a backup and restore so we have to use the export data tool in SQL Server Management studio to export the data from our test environment to our live. This has worked fine on previous projects we have worked on so I do not be live that this is the issue here.

    Regards,

    Jason

  • Ismail Mayat 4405 posts 9633 karma points MVP 2x admin c-trib
    Oct 24, 2014 @ 15:18
    Ismail Mayat
    0

    Jason,

    I would drop the db then recreate the script and try again. Like I say whenever I have scripted db is has never worked, I have always found indexes and secondary keys etc dont get created and the whole db becomes un usable.

    Regards

    Ismail

  • Jason Espin 368 posts 1335 karma points
    Oct 24, 2014 @ 15:27
    Jason Espin
    0

    Hi Ismail,

    I have just repeated the process and once again it does not work :-( Just to be clear, I am not actually scripting the database here as that is a seperate option.

    In Microsoft SQL Server Management Studio I am running the Tasks > Export Data option which brings up the SQL Server Import and Export wizard. It says there are 52 tables and all are updated / copied to the new database succesfully so I dont believe the issue is here.

    Kind regards,

    Jason Espin

  • Ismail Mayat 4405 posts 9633 karma points MVP 2x admin c-trib
    Oct 24, 2014 @ 15:44
    Ismail Mayat
    0

    Jason,

    Ok, so did you create the db first with a script then run the export?  If so maybe the initial creation failed, so drop the db and recreate may fix the issue?

    Regards

    Ismail

  • Jason Espin 368 posts 1335 karma points
    Oct 24, 2014 @ 15:49
    Jason Espin
    0

    Hi Ismail,

    Our provider uses a plesk control panel so the database was created from there with no content or tables. I then ran the update process I have described above.

    Regards,

    Jason

  • jeffrey.schoemaker@perplex.nl 317 posts 1300 karma points MVP 3x c-trib
    Oct 24, 2014 @ 17:29
    jeffrey.schoemaker@perplex.nl
    0

    Hi Jason,

    can you put the part of the script here that generates the table. It looks like there isn't an auto-identity set on the table. I think it should look something like this:

    CREATE TABLE [dbo].[cmsContentVersion](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [ContentId] [int] NOT NULL,
        [VersionId] [uniqueidentifier] NOT NULL,
        [VersionDate] [datetime] NOT NULL,
        [LanguageLocale] [nvarchar](10) NULL,
     CONSTRAINT [PK_cmsContentVersion] PRIMARY KEY CLUSTERED
    (
        [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[cmsContentVersion] ADD  CONSTRAINT [DF_cmsContentVersion_VersionDate]  DEFAULT (getdate()) FOR [VersionDate]
    GO

    ALTER TABLE [dbo].[cmsContentVersion]  WITH CHECK ADD  CONSTRAINT [FK_cmsContentVersion_cmsContent_nodeId] FOREIGN KEY([ContentId])
    REFERENCES [dbo].[cmsContent] ([nodeId])
    GO

    ALTER TABLE [dbo].[cmsContentVersion] CHECK CONSTRAINT [FK_cmsContentVersion_cmsContent_nodeId]
    GO

    If you put the script here, I can take a look in it.


    Greetings Jeffrey

  • Jason Espin 368 posts 1335 karma points
    Oct 24, 2014 @ 18:33
    Jason Espin
    0

    Hi Jeffrey,

    I do not generate any scripts in this manner. I run the SQL SERVER MANAGEMENT STUDIO WIZARD as I have specified above.

    Kind regards,

    Jason Espin

  • Thomas 35 posts 131 karma points c-trib
    Aug 13, 2015 @ 22:08
    Thomas
    1

    Hi Jason (and others),

    Fairly late to this thread but if anyone else has a similar issue, I have also come into these problems when using the Tasks > Export Data option. Like you, I was hoping I could use it to easily set up my database on shared hosting.

    Basically, the keys are not being copied across with the Export Data wizard, since as the name suggests it is only really meant to copy data between databases. When it comes to schema it is lacking.

    To check this, you can open up the copied table within SQL Server and then expand the keys, if they are empty then it obviously won't work.

    Solutions would be...

    • create empty umbraco site and get that to set up all the tables for you, then do the export data.
    • using a tool such as SQL Compare to copy across the table structure
    • restore and backup you database, which like you found out is rather tricky with most shared hosting companies but support team might be able to do this for you.

    It would be nice if there were better checks and error messages that Umbraco can provide to essentially say your database schema is not correct.

Please Sign in or register to post replies

Write your reply to:

Draft