VB.NET + NAV Web service: Set date to "empty"

dpardomdpardom Posts: 74Member
edited 2014-10-08 in NAV Three Tier
Hi.

How can I set a date field to "empty" using VB.NET and Nav web services?

My code:
Dim a As New NAV.Job_Service
a.UseDefaultCredentials = True
Dim b As NAV.Job
b = a.Read("a-job-no")
b.some_date_field = [color=#FF0000]???[/color]
a.Update(b)

Tried with Nothing/null, system.dbnull, date.minvalue, "1753-01-01"... but nothing seems to work.

Thanks.




Solution.
Vjeko has written a blog posting about the 0D bug: http://navigateintosuccess.com/blog/bug ... services-2

Hotfix, as seen in blog's comments.
Soren January 12, 2012 at 23:19
KB2606541 – https://mbs2.microsoft.com/Knowledgebas ... us$2606541
Seems like this should be the fix for it. But i have not tested it.

I tested it and works. Now SqlDateTime.MinValue is the "null" date for Nav.

Answers

  • mihail_kolevmihail_kolev Posts: 379Member
    the 0D value in NAV is '1753-01-01 00:00:00:000'. In .NET the functions expect DateTime variable.
    -Mihail- [MCTS]
  • dpardomdpardom Posts: 74Member
    Sorry, I don't understand very well what do you mean.

    In .NET, it expects a "Date" variable.

    With cdate("01/01/1753") it throws: "The * table contains a field with a date value that cannot be used with SQL Server".

    \:D/
  • David_SingletonDavid_Singleton Posts: 5,367Member
    dpardom wrote:
    Sorry, I don't understand very well what do you mean.

    In .NET, it expects a "Date" variable.

    With cdate("01/01/1753") it throws: "The * table contains a field with a date value that cannot be used with SQL Server".

    \:D/

    Did you even read mihail's post. He gave you the exact answer. ](*,)
    David Singleton
  • dpardomdpardom Posts: 74Member
    I read it again, but can't understand.

    Also, as I said, .NET function expects a "Date", not a "Datetime".

    Can you explain it, please?
  • mihail_kolevmihail_kolev Posts: 379Member
    No, you might wrote a method which expects Date variable. If you publish a function from NAV the Date parameter is represented as DateTime in .NET
    -Mihail- [MCTS]
  • dpardomdpardom Posts: 74Member
    Thanks for helping, mihail.
    No, you might wrote a method which expects Date variable.

    I published a function in a codeunit with a "date" parameter and a "datetime" parameter. Both are the same from .NET (both are "date" .NET type), and can't use sqldatetime.minvalue (wich seems to be the "null" value for Nav) with none ot them.

    The problem seems to be that '1753-01-01 00:00:00:000' is not converted to '0D'. Maybe a regionalization problem? We got other problems related with dates and decimals due to diferences in representation.
    If you publish a function from NAV the Date parameter is represented as DateTime in .NET

    I don't understand how to modify a date through a Nav web services without publishing a function. (ie, a page with its "modify" function or a function in a codeunit)


    Please, check the workaround I added to the first post. Maybe it helps.

    Thanks again.
  • Luc_Van_DyckLuc_Van_Dyck WilrijkPosts: 3,624Member, Moderator, Administrator
    Vjeko has written a blog posting about the 0D bug: http://navigateintosuccess.com/blog/bug ... services-2
    No support using PM or e-mail - Please use this forum. || Search is your friend
    NAV TechDays 2016: 17 & 18 November 2016, Antwerp (Belgium)
  • dpardomdpardom Posts: 74Member
    Thank you very much, Luc Van Dyck. That was the problem. I copied the answer to the first post.

    Thank you too, mihail_kolev, for trying to help.

    And for David Singleton an (optional) advice: Don't give such answers, even if you are right.

    See you.
  • mdPartnerNLmdPartnerNL Posts: 746Member
    dpardom wrote:
    Hotfix, as seen in blog's comments.
    Soren January 12, 2012 at 23:19
    KB2606541 – https://mbs2.microsoft.com/Knowledgebas ... us$2606541
    Seems like this should be the fix for it. But i have not tested it.

    I tested it and works. Now SqlDateTime.MinValue is the "null" date for Nav.

    Hi,
    1.
    Has this hotfix been retracted?? if i click on download I cannot select the hotfix.

    2.
    Is this the latest hotfix or can I use a newer one to fix all problems?
  • dpardomdpardom Posts: 74Member
    1.- You must login with one of the four methods listed in the page. Then click "View and request hotfix downloads", and provide an email address to receive the hotfix link.

    2.- Sorry, I don't know.
  • mdPartnerNLmdPartnerNL Posts: 746Member
    dpardom wrote:
    1.- You must login with one of the four methods listed in the page. Then click "View and request hotfix downloads", and provide an email address to receive the hotfix link.

    2.- Sorry, I don't know.
    Yes, that is correct, but on the page after "View and request hotfix downloads" I can type in my emailaddress but there is no hotfix to select. Could anyone check this?
  • KarlRhodesKarlRhodes Posts: 39Member
    We're converting a website from using a custom wrapper that was created some years ago to use Navision Web Services to read/write data from/to Dyanamics NAV/Navision.

    The system is also being upgraded from 2009r2 to 2013r2.

    However, we're experiencing the same issue when trying to update a record so that it has a "null" date.

    When we set the value to either SqlDateTime.MinValue or CDate("1/1/1753") we get the same error as the OP.

    It seems that the link to the "fix" is no longer available, but I believe I found the post by looking through the sites archives. However after looking at the post is doesn't seem to help when using web services. We're accessing the data through a published Page rather than a CodeUnit, and it seems like a huge overhead to call a function in a CodeUnit just to do something the page should already be able to do.

    However, that's the approach we're going to have to take until this gets fixed properly.

    For reference and for anyone else experiencing the same issue, the suggested solution is to extend the page with an extension CodeUnit and have a function for each date field you're likely to want to clear (set to a null date in Nav). You can then pass in the keys fields to identify the correct record, then set the date to 0D in code.
Sign In or Register to comment.