Copied to clipboard

Flag this post as spam?

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


  • Vetrivel 21 posts 130 karma points
    Dec 11, 2020 @ 06:54
    Vetrivel
    0

    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:

    [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.

  • Marc Goodson 2155 posts 14408 karma points MVP 9x c-trib
    Dec 13, 2020 @ 21:31
    Marc Goodson
    0

    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

  • Vetrivel 21 posts 130 karma points
    Dec 14, 2020 @ 00:21
    Vetrivel
    0

    Hi Marc,

    Thanks for the reply, We have created a new property and add into the IContent.

    IContent.SetValue(string alias, object value);
    

    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.

  • Marc Goodson 2155 posts 14408 karma points MVP 9x c-trib
    Dec 14, 2020 @ 13:54
    Marc Goodson
    0

    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

  • Vetrivel 21 posts 130 karma points
    Dec 14, 2020 @ 14:27
    Vetrivel
    0

    Thanks for the reply Marc Goodson,

    Let me check the reference and let you know. (y)

Please Sign in or register to post replies

Write your reply to:

Draft