Set Date Value to Null

[Former Member]

Hi All,

I have create a custom field using Date type.

How do i set this custom field to null using data interface?

When I try :

SapLines.UserFields.Fields.Item(ReferenceDate).Value=Nothing,

Result :

Failure

When I try :

SapLines.UserFields.Fields.Item(ReferenceDate).Value=""

Result :

It return 1/1/1970

Can someone please advise me how to date field to null? or nothing?

Thanks

Regards,

david

[Former Member]

HI

Try this

SapLines.UserFields.Fields.Item(ReferenceDate).Value=DBNull.Value ;

Hope it helps you

Regards

Vishnu

[Former Member]

Hi, I am using SDK 2005.

ReferenceDate is a const string value, so that will work on my code. It doesn't matter with double quote or not in the field.

We try to set dBNUll/System.DBNULL.Value as you can see in the post above, it won't work.

The only workaround is to delete all lines and re-insert. That's my workaround and it current work for me.

Which mean do not set value to date if the expected value is nothing.

[former_member933888]

Hi,

I remember ast tile i set my udf field value to "18991230" and it works.

Now i want to upload Sales order and make the Cancellation date blank or NULL.

How can i do this?

Q.

[Former Member]

Hello David,

So by Di API you cannot set the date to null.

Because:

1. the date is interpreted as any valid date value. If you try to set as null, the SQL Minimum date will be used as the result (it is a date type). basically it is : 18991231 or similar. It can be customized by SQL server / database

To understand try the following (SQL)

-- SQL
DECLARE @datevar datetime 
SELECT @datevar AS DateVar;
    -- Returns: NULL

And try it in the following VB code:

 Public Sub SetNullorMinDate()
        Dim oRs As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset)
        oRs.DoQuery("DECLARE @datevar datetime SELECT @datevar AS DateVar")
        '
        Dim So As SAPbobsCOM.Documents = oCompany.GetBusinessObject(BoObjectTypes.oOrders)
        So.CardCode = "V0358"
        So.DocDueDate = Date.Today()
        So.Lines.ItemCode = "1211001214"
        So.CancelDate = oRs.Fields.Item(0).Value
        So.Add()

    End Sub

Regards,

J

[former_member933888]

Hi Janos,

The SQL return NULL,

I use your method.

But the the cancelled date still show 30/12/1899

Q

[Former Member]

Hi I too have this problem

I have a UDF of type DATE

and i have to put it to Empty

oDocOrder.UserFields.Fields.Item("U_NextDate").Value = " " -> Result is "12/01/99"

oDocOrder.UserFields.Fields.Item("U_NextDate").Value = vbNull - > Result is a Crash

oDocOrder.UserFields.Fields.Item("U_NextDate").Value = System.DbNull.Value -> Result is a crash

oDocOrder.UserFields.Fields.Item("U_NextDate").Value = "_" ' _ Being Space -> Result is "12/01/99"

i Will keep on looking David but so far no results that are tangible as soon as i find ans amswer i will post here

[Former Member]

Erm, you are getting different result as me.

I even tried running sql query to update those date to NULL

But the weird things is that, you are getting 12/01/99, i m getting 01/01/1970.

Why are they difference?

Is there any default value we can set as far as you know?

Before I overwritten the value, i noticed that the value was 01/01/1989, then, if i don't set anything on it, it become 01/01/1970.

VBNUll/DBNUll.value doesn't work.

I even try to set it to nothing. it still coming up at 01/01/1970.

My workaround is not to set it

Whenveer u want to popular a document order, just delete all lines first, then repopulate/ this is not very practical in the programming terms.

In your layout designer, make sure you change it as well. Now I am trying to work out the formulae for this, as I am using Advance Layout Designer.

[Former Member]

Hi David,

try

SapLines.UserFields.Fields.Item("ReferenceDate").Value="" - the referencedate in quotes and two quotes as value without space, it works on my 2004. If this doesn`t work fine, write your version of SBO.

Maybe check, if there is in DB sets default value for this field (default value replaces all null values with defaults).

Petr