Error dtoc() with empty date-field in MySQL

[Marc Vanzegbroeck]

Hi,

I'm using MySQL with ADO.
I have a stange error while browsing a data-fields.
I'm using

Code: Select all  Expand view

dtoc(oRs1:Fields( 'datum' ):Value)

for browsing a date if in MySQL.
If I add an empty record to my recordset I get an error

Error description: Error BASE/1118 Argument error: DTOC
Args:
[ 1] = U

I'm not able to browse the recordset anymore until I fill a valid date.
How can I solve this? I can first fill the data with a dummy-date, but I don't find that a nice solution, because sometimes the date can be empty...
I can do a

Code: Select all  Expand view

IIF(!empty(oRs1:Fields( 'datum' ):Value),dtoc(oRs1:Fields( 'datum' ):Value),'  /  /  ')

in my browse, but why is the field not a date field anymore when it's empty? Browsing an empty DBF-date field don't have this error.

[Enrico Maria Giordano]

That's how SQL databases work. Any fields can contain a value of its data type plus NULL (NIL). You can change the field default value, if I remember correctly.

EMG

[Rick Lipkin]

Mark

With xBrowse you can use this code to mask your NIL value for an empty date .. change aCols[x] and your recordset field to fit your situation.

Hope this helps
Rick Lipkin

Code: Select all  Expand view

oCol := oLbx:aCols[ 5 ]
oCol:bStrData := { |x| x := oRsUser:Fields("lastlog"):Value, If( Empty(x), '00/00/0000',DToC(x) ) }
 

[Marc Vanzegbroeck]

Mark

With xBrowse you can use this code to mask your NIL value for an empty date .. change aCols[x] and your recordset field to fit your situation.

Hope this helps
Rick Lipkin

Code: Select all  Expand view

oCol := oLbx:aCols[ 5 ]
oCol:bStrData := { |x| x := oRsUser:Fields("lastlog"):Value, If( Empty(x), '00/00/0000',DToC(x) ) }
 

Rick,

Thanks for the reply.
That's what I'm using now (see my original post). I was just wondering why the type of that field is a data-type if it's empty. I can also change the default value (like Enrico suggest), but than I don't know the different that it's empty or not, because there is allways a date...

[Rick Lipkin]

Mark

SQL uses NIL as an valid datatype like we would think of a .dbf field = " ". If you do not assign a value to a SQL field it is NIL and not just " ".

When you test for any value from SQL you should always test for Empty()... just in case, or you will wind up with an unexpected run-time failure... especially if you have inherited a database from another application\developer.

Rick Lipkin

ps .. note, some SQL databases ( ms sql server ) do not allow you to write ctod("") back to a date\time field and those that do, write NIL back when you do this :

oRs:Fields("Date"):Value := ctod("") .. actually writes NIL to the sql field