FiveTech Support Forums

To All

I have done several Google searches and not been able to find how to write a blank or null date to a datetime field in MS Sql server using ADO recordset update. Consider this example:

|DATE |
| null |

dDATE := oRs:Fields("DATE"):Value // xHarbour sees a NULL date as NIL

writing back a blank date

oRs:Fields( "DATE" ):Value := IF( EMPTY( dDATE ), NIL, dDATE )

This works .. however it puts a DEFAULT date of 12/30/1899 in for the NIL parameter and writes that to the table. I need to be able to let a user blank out a date and store it as BLANK or NULL in the SQL table .. ctod("") gives an ADO run-time if you try to write that value to the MS SQL table.

Any ideas ??

Rick Lipkin
SC Dept of Health, USA

Fernando

I am writing the same program in pure ADO and in ADORDD .. I have not tested the capability of writing rdd back to the table .. and I will do that .. MS SQL seems to be different in quite a few many ways as Access or MySql .. I have put my adordd program on hold for the time being because adordd uses indexes for seeks .. and MS Sql does not support seeks on indexes ..

I will test rdd on writing back ctod("00/00/00") to the table and report back here.

Thanks
Rick Lipkin

To All .. the FIX

Here is some 'snipits' of my xHarbour thread with Jose Gimenez who has fixed the problem and is available in binary from his site or on CVS.

Rick Lipkin
SC Dept of Health, USA

//----------------------------

Rick,

> This works .. however it puts a DEFAULT date of 12/30/1899 in for the NIL
> parameter and writes that to the table. I need to be able to let a user
> blank out a date and store it as BLANK or NULL in the SQL table ..
> ctod("") gives an ADO run-time if you try to write that value to the MS
> SQL table.

This was fixed last Feb, 16th, but last official binaries from xHarbour are
older, so you have to update from CVS. Now, writing a ctod("") value results in a real NULL date.

//--------------------------------------------

The change is very simple:

• search the function hb_oleItemToVariant() in
  \xharbour\source\rtl\win32ole.prg
• search the case HB_IT_DATE: in the switch sentence
• change the first line:

    if( bByRef )
  

  into:
  if( pItem->item.asDate.value == 0 )
  {
  pVariant->n1.n2.vt = VT_NULL;
  }
  else if( bByRef )

That's all