0 can not be saved in a field of type integer

11 posts, 0 answers
  1. Gilberto
    Gilberto avatar
    4 posts
    Member since:
    Mar 2011

    Posted 28 Jun 2013 Link to this post

    hello how are you, I hope someone can help me

    When I try to save to a field of type int to 0 in a database SQL Server 2008 is not possible because it saves NULL instead, if I try to store another number let me no problem, I am using the OpenAccess version 2013.2.611.1 when using the version 2012.1.214.1 not happen this.
  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 03 Jul 2013 Link to this post

    Hello Gilberto,

     Do you have default values defined for those properties in your database? Basically in between those releases we added support for default values where we are counting on the 0 and null to know when a value is set and when the default one should be respected.
    It will be of great help if you can share with us your configuration.

    Regards,
    Petar
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvementsshipped with this release.

  3. DevCraft banner
  4. Gilberto
    Gilberto avatar
    4 posts
    Member since:
    Mar 2011

    Posted 04 Jul 2013 Link to this post

    Hello Petar,

    The field in the database is of type INT and allows NULL values​​, however the problem arises when I want to save the value 0, do the step by step from the
    Visual Studio and I see that I am sending the value 0 in the OpenAccess but not saved it leaves a NULL instead.

    It is necessary that the field will save the value 0.

    Thank you very much.
  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 09 Jul 2013 Link to this post

    Hello Gilberto,

     I am using the following table:

    USE [TestDB]
    Go
     
    CREATE TABLE [dbo].[NullableIntTestTable](
        [AutoInc] [int] IDENTITY(1,1) NOT NULL,
        [NullableInt] [int] NULL,
     CONSTRAINT [PK_NullableIntTestTable] PRIMARY KEY CLUSTERED
    (
        [AutoInc] 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

    As you can see here we have an auto incremented primary key and a nullable integer column. In my code I am doing the following:
    EntitiesModel1 db = new EntitiesModel1();
                db.Add(new NullableIntTestTable() { NullableInt = 0 });
                db.SaveChanges();

    And I can confirm that the above code inserts a 0 in the database and not a null.
    Can you please share with me any additional details in regards to your model? Is it possible for you to share with me the structure of your table? Maybe there is a default value defined there or some other value constraint?

    Regards,
    Petar
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvementsshipped with this release.

  6. Gilberto
    Gilberto avatar
    4 posts
    Member since:
    Mar 2011

    Posted 16 Jul 2013 Link to this post

    Hello Petar, 

    apology for the delay in replying.

    Here I show you the code of the table but there is no default value in the fields.

    CREATE TABLE [dbo].[TransactionLog](
    [TransactionId] [bigint] IDENTITY(1,1) NOT NULL,
    [AccountId] [bigint] NOT NULL,
    [GatewayId] [int] NULL,
    [IsTest] [bit] NULL,
    [TransactionTypeId] [int] NULL,
    [CardAccountTypeId] [int] NULL,
    [FranchiseId] [int] NULL,
    [Bank] [varchar](250) NULL,
    [HostTransactionId] [bigint] NULL,
    [CardNumber] [varchar](50) NULL,
    [Fees] [int] NULL,
    [Score] [int] NULL,
    [Token] [varchar](500) NULL,
    [Amount] [numeric](18, 0) NULL,
    [Tax] [varchar](50) NULL,
    [Description] [varchar](500) NULL,
    [UserType] [int] NULL,
    [AuthCode] [varchar](50) NULL,
    [MsgResp] [varchar](500) NULL,
    [NumRec] [varchar](50) NULL,
    [StatusId] [int] NULL,
    [Reference1] [varchar](250) NULL,
    [Reference2] [varchar](250) NULL,
    [Reference3] [varchar](250) NULL,
    [MerchantOption1] [varchar](250) NULL,
    [MerchantOption2] [varchar](250) NULL,
    [MerchantOption3] [varchar](250) NULL,
    [CreatedDate] [datetime] NOT NULL,
    [IdType] [varchar](50) NULL,
    [IdNumber] [varchar](50) NULL,
    [UpdateByGateway] [bit] NULL,
    [UpdateByGatewayDate] [varchar](250) NULL,
    [UpdateByService] [bit] NULL,
    [UpdateByServiceDate] [varchar](250) NULL,
     CONSTRAINT [PK_LPTransaction] PRIMARY KEY CLUSTERED 
    (
    [TransactionId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
  7. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 17 Jul 2013 Link to this post

    Hi Gilberto,
    Thank you for sharing your details.
    Based on the previous posts, I assume that the CLR type of the property in your class where you want to have the value of 0 is a non nullable type(I guess 'int').
    If the CLR type is a non
    nullable type, we have to interpret the 0 as null, since the 0 is the default value for non nullable int types. 

    To solve this, you can change the CLR type to 'int?' and you should be able to store the 0 in the database. This is what my colleague Petar proved previously.

    I hope this information is helpful for you.
    Do come back in case you need further assistance.

    Regards,
    Ralph
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvementsshipped with this release.

  8. Andrew Lastochkin
    Andrew Lastochkin avatar
    14 posts
    Member since:
    Sep 2012

    Posted 26 Dec 2013 Link to this post

    Hello,

    I have similar problem, but the opposite one:

    1) I have int? nullable property based on MetaField with HasDefaultValue = true
    2) Create an entity and without touching this nullable property try to save record
    3) The following exception occurs:
    Insert of '30102130-' failed: Telerik.OpenAccess.RT.sql.SQLException: Cannot insert the value NULL into column 'Price', table 'BookShop.dbo.Book'; column does not allow nulls. INSERT fails.
    Because value of this nullable property (with default value set in database) is transfered explicitly as NULL in INSERT statement.

    I have the following code to initialize mapping configuration for this property:
    prop = tableMappingConfig.HasArtificialPrimitiveProperty(metaColumn.Name, typeof(int?));
    prop.HasFieldName(metaColumn.Name);
    prop.ToColumn(metaColumn.Name);
    prop.HasColumnType(metaColumn.SqlType);
    prop.IsNullable();
    Tried this code with artificial and normal field, problem persists.

    Could you please tell me why problem is here?
  9. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 27 Dec 2013 Link to this post

    Hello Andrew,

    Generally, the behaviour you are experiencing is the expected one. Telerik OpenAccess ORM supports database default values only in Database First scenarios with a domain model (.rlinq file). The feature is not implemented for Code - Only Mapping. There is a request on our Ideas and Feedback portal about expanding the scope of the feature, and I kindly invite you to vote about it.

    Please, excuse us for the inconvenience caused. If you have additional questions, do not hesitate to get back to us.


    Regards,
    Doroteya
    Telerik
    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  10. Edgardo
    Edgardo avatar
    1 posts
    Member since:
    Jul 2013

    Posted 25 Apr Link to this post

    I'm having the same problem....any suggestion? I see in Visual Studio that it has a 0 but when I verify on the database the value is NULL.
  11. Simeon Simeonov
    Admin
    Simeon Simeonov avatar
    24 posts

    Posted 28 Apr Link to this post

    Hi Edgardo,

    The issue you are experiencing seems to be related to a known limitation of OpenAccess for all nullable value types. If you set to a mapped field of nullable value type, the default value for the value type, this is not considered a change. As a result OpenAccess treats the field as not modified. The end result is that, because the field is considered not modified, OpenAccess stores in the database the default value for nullable (null). For example if you have a field of type "Long?" and you set it to 0 (which is the default value for "Long"), the field is not considered modified and to the database is send the last known value for the field - in this case null.

    This is a known issue which is very hard to fix and at the current moment is not planed to be corrected in the immediate future.

    In order to workaround this limitation you need to make sure that you first set the field to a non default value and then set it to the default value. For example if you are using properties you can modify the property setter and when the property is set to 0, first set the underlying field to 1 and then to 0, so the change is taken into account by OpenAccess. For example:
    Co

    private long? _LANG_KEY;
    public virtual long? LANG_KEY
    {
        get
        {
            return this._LANG_KEY;
        }
        set
        {
            if (value == 0)
            {
                this._LANG_KEY = 1;
                this._LANG_KEY = 0;
            }
            else
            {
                this._LANG_KEY = value;
            }
        }
    }


    Regards,
    Simeon Simeonov
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  12. Steve
    Steve avatar
    1851 posts
    Member since:
    Dec 2008

    Posted 13 Jul in reply to Simeon Simeonov Link to this post

    Here's a feedback request for this exact problem, could use a vote or 10

    http://feedback.telerik.com/Project/114/Feedback/Details/141484-fix-default-value-fk-null-0-implimentation

    I just encountered it yet again, still makes no sense, I'd love an option to globally disable this weird logic in my model.
Back to Top
DevCraft banner