ADO .. how to write ctod('00/00/00') to MS Sql Server

[Rick Lipkin]

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

[Rick Lipkin]

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

[Rick Lipkin]

To All .. the FIX

Here is some 'snipits' of my xHarbour thread with Jose - 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