mibuso.com
Home› NAV/Navision Classic Client
Options

Insert Values By SQL

bbcaibbcai Member Posts: 80
edited 2005-06-17 in NAV/Navision Classic Client
Is it possible to insert blank values with SQL like Navision insert the data into table cause i saw most of the fields in Navision SQL Server is not allowed null values?

Comments

  • Options
    itspeteritspeter Member Posts: 105
    If not mistaken, Navision never store NULL, but it store empty string.
    Regards,
    Peter Ng
  • Options
    kinekine Member Posts: 12,562
    There is one property on the Database..

    Look there - Open Navision, connect to the DB, main menu - File - Database -
    Alter -Integration -Maintain Defaults - check it, save it... you will have
    defaults created and you do not need to enter all fields if you are
    inserting data...

    BUT!!! Inserting data with external software into Navision can corrupt your
    database if you do not know exactly all aspects and impact of your work...
    (for example some connections can be corrupted etc.) I recommand you to
    import data only into some special tables, created only for that purpose,
    and transfer the data to standard tables with C/AL code to be able to run
    all validations etc... But please, do not import Accounting data in this
    way, use journals...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    jonsan21jonsan21 Member Posts: 118
    Hi,

    If you know what you're doing - yes you can do that. You can let the SQL's Allow Null property for a particular field and then key in NULL values to that field.

    However as itspeter mentioned, when you recompiled the Navision table (in Navision) it will reset the Allow Null property - as Navision seems to always insert empty string instead of NULL values.

    Rgds,

    Jon.
    Rgds,

    Jon.
  • Options
    kinekine Member Posts: 12,562
    Yes, setting something directly on SQL is no solution. Use the standard Navision property for the defaults and you have correct solution. It will only set defaults to '' or 0 on all fields (and the first date on all date fields).
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    NobodyNobody Member Posts: 93
    It is easy really, just not intuitive.

    Just use '' (single quote, single quote) for the value in the insert statement. This will insert a "blank" value without using NULLS into the field. That is how the NDBCS.DLL in Navision does it.

    Example
    Insert Tablename(Field1, Field2), Values('','')

    FYI - Using NULLS in TSQL design and programming can be dangerous to your health. =;
  • Options
    kinekine Member Posts: 12,562
    Nobody wrote:
    It is easy really, just not intuitive.

    Just use '' (single quote, single quote) for the value in the insert statement.

    Yes, but after you add some field, you must correct the TSQL... it is very hard to keep it in sync. The defaults on SQL solve it...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
Sign In or Register to comment.