Updating Date Field to Null or empty string

[chum908]

I am using VB.NET and trying to update a field in a Pervasive database using ODBC (Timberline Data Source to be exact) and am using ADO.
All of my updates work until I try to Null out a date as in the following example with Rehire_Date it errors.

Here is the line in question:
strSQL = "UPDATE MASTER_PRM_EMPLOYEE SET Termination_Date = NULL, Rehire_Date = '" & Format(CDate(txtRH.Text), "yyyy-MM-dd")', Phone_Number = '" & Trim(txtPhone.Text) & "' WHERE Employee = '" & Trim(txtID.Text) & "'"

I have tried NULL, '', 0000-00-00, chr(0) to no avail.
Everything I try gives me the Invalid Date, Time stamp error.

I have spent 4 hours trying to find anything on this with no results. Please help!!
Thanks!

 

[mirtheil]

It's possible that the data doesn't allow a null values for that field.
If the data allows a "null" value, you might want to check what the value Timberline uses as the "null" value.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[chum908]

Mirtheil,
I figured it out. After trying everything I decided to try setting the field to a time stamp. So I set the field to 12:00:00 and believe it or nut it worked! I looked at the data and it shows up as empty in the database and my app. I am not understanding why but hey it works!
Thanks for your response.
Mike