WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: PaulSamuelson on March 04, 2019, 11:12:16 AM

Title: Clear Date Field with ADO
Post by: PaulSamuelson on March 04, 2019, 11:12:16 AM
I have a routine I use to change data in an ADO recordset (Access File). It works for all types I have tried, except it does not allow me to clear a date.

It will change a date field, but not clear one. How do I clear a date field?

If FieldValue==""
  RS1.Collect(dbField) = ObjectType("NULL",0)
Else
  LastError() ;reset to 0
  ErrorMode(@off)
  RS1.Collect(dbField) = FieldValue
  ErrorMode(@cancel)
  Error = LastError()
  If Error!=0
    Message("Oops","Record [":iGridSubjects.RowKey(editRow):"] Field [":dbField:"] not updated (probably the wrong data type).")
  End If
End If
RS1.Update()


Thanks,

Paul
Title: Re: Clear Date Field with ADO
Post by: td on March 04, 2019, 02:07:08 PM
I deleted my original post because it was off the mark but do you get an error message when you attempt to assign the NULL variant to the field or when you call Update?  Even if you don't get an error, I suppose it could be a restraint set on a particular database to prevent NULL values.
Title: Re: Clear Date Field with ADO
Post by: stanl on March 04, 2019, 02:26:40 PM
Might not be relevant but if your date field is required in your table structure nothing will work.
Title: Re: Clear Date Field with ADO
Post by: td on March 04, 2019, 10:21:14 PM
I have a very vague notion that Access will set a NULL date field to some default date. Don't know if this is configurable or if it is even a feature.   May simply be a false memory.
Title: Re: Clear Date Field with ADO
Post by: PaulSamuelson on March 05, 2019, 07:14:05 AM
Quote from: td on March 04, 2019, 02:07:08 PM
I deleted my original post because it was off the mark but do you get an error message when you attempt to assign the NULL variant to the field or when you call Update?  Even if you don't get an error, I suppose it could be a restraint set on a particular database to prevent NULL values.

I removed the Error stuff, which is no longer needed, because I know exactly which data types are affected (that code was originally used on a grid, where I didn't necessarily know the type). The following code now DOES work. It will update (including remove) date or text types.

Apparently, the problem had to do with the order I cleared the fields. If I cleared the text field first, the record was no longer in the recordset when it came back to clear the date. If I cleared the date first, everything works as it should.

Thanks,

Paul

If editVal == ""
  RS1.Collect(dbField) = ObjectType("NULL",0)
Else
  RS1.Collect(dbField) = editVal
End If
RS1.Update()
Title: Re: Clear Date Field with ADO
Post by: td on March 05, 2019, 07:26:18 AM
Access does allow a table to be configured with required fields.  This could cause a problem similar to the one you experienced. Obviously, it wasn't an issue here. 

Thanks for reporting your solution.  It may be helpful to others in the future.