We are facing an SQL error with saving property values in Umbraco v7.15.6.
SQL Error:
[API] Exception was thrown in class 'SqlConnection' while invoking method 'OnError', with the message: 'String or binary data would be truncated.
The statement has been terminated.'.
System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
We checked with the database, the values try to save in dbo.cmsPropertyData under dataNvarchar property.
the property set as nvarchar(500) , that's the issue, once the input valued limit exceeds 500 it will throw the error.
We can directly update the field datatype in SQL direct query, but we are not preferred that, is there any other ways to handle this, or
Is this issue fixed in some other Upgraded Umbraco versions, If so we need the upgraded Umbraco version name details.
And these Value Types are 'mapped' to the particular column in the property editor table that the value are stored in - you can see the mapping logic here:
So if your property editor has a 'String' value type, then changing this to be a 'Text' type will mean it's values get stored in the NText column, and you won't have the overflow problem.
If this is a core property editor, you might have to implement your own variation (same implementation, but give the editor a different alias, and obviously 'value type'.
Switching from one type to another, you'll need to migrate the data from the NVarchar Column to the NText column via SQL!
I'm not sure which property editor it is? that you are cramming the data into :-P ?
eg if it's a third party plugin or a core Umbraco property editor?
But essentially you could define your own new custom editor, based on the existing property editor functionality, but just change the ValueType to store the data in the NText field.
Table - CMSPropertyData - dataNvarchar field type Error
Hi Team,
We are facing an SQL error with saving property values in Umbraco v7.15.6.
SQL Error:
We checked with the database, the values try to save in dbo.cmsPropertyData under dataNvarchar property. the property set as nvarchar(500) , that's the issue, once the input valued limit exceeds 500 it will throw the error.
We can directly update the field datatype in SQL direct query, but we are not preferred that, is there any other ways to handle this, or
Is this issue fixed in some other Upgraded Umbraco versions, If so we need the upgraded Umbraco version name details.
Hi Vetrivel
Which Property is causing the issue and what type is it?
A Property is based upon a 'Data Type' which is in turn based upon a Property Editor.
The Property Editor has an underlying Value Type.
https://our.umbraco.com/Documentation/Extending/Property-Editors/Declaring-your-property-editor
And these Value Types are 'mapped' to the particular column in the property editor table that the value are stored in - you can see the mapping logic here:
https://github.com/umbraco/Umbraco-CMS/blob/b68f1d44f1c5c640c0c49264b34ecd6a9caa6a44/src/Umbraco.Core/PropertyEditors/ValueTypes.cs
So if your property editor has a 'String' value type, then changing this to be a 'Text' type will mean it's values get stored in the NText column, and you won't have the overflow problem.
If this is a core property editor, you might have to implement your own variation (same implementation, but give the editor a different alias, and obviously 'value type'.
Switching from one type to another, you'll need to migrate the data from the NVarchar Column to the NText column via SQL!
regards
Marc
Hi Marc,
Thanks for the reply, We have created a new property and add into the IContent.
I don't know how to change the property type from nVarchar to nText using the above API.
Because, we used the above Api (SetValue) to assign the values into the custom property, and it's not created in Umbraco back end. Hope it's clear.
Let me check once and let you know.
Meanwhile Is there any way to handle this...
Kindly share your thoughts if any.
Hi Vetrivel
The ValueType is set for the Property Editor when it is created at boot time by Umbraco, not when it's populated via code.
https://our.umbraco.com/Documentation/Extending/Property-Editors/Declaring-your-property-editor
I'm not sure which property editor it is? that you are cramming the data into :-P ?
eg if it's a third party plugin or a core Umbraco property editor?
But essentially you could define your own new custom editor, based on the existing property editor functionality, but just change the ValueType to store the data in the NText field.
regards
marc
Thanks for the reply Marc Goodson,
Let me check the reference and let you know. (y)
is working on a reply...