I have found two issues when trying to store NULL values in mySQL with an ADO recordset.
1 .RS vs. Execute Updates
rs!DateField = NULL ' 'Error Reported on .Update
connection.execute UPATE Table SET DateField=NULL ' Works correctly.
Is there a difference between VB NULL and the NULL executed in an SQL Statement?
2. I have an Infragistics Date control bound to a date field. Connecting to Access if there is a valid date in the database and I then delete the date from the bound control and do a .update, NULL is stored in the database as expected. In mySQL and invalid date value (something like '0016-00-00 00:2009') is stored. The very strange part is that if I add the following statement (where I touch a memo type field) before the .update NULL is correctly stored under mySQL and no error is reported.
rs!MemoField = rs!MemoField
.update
Can someone explain this? For now I unhooked the data controls and use a loop which spins through the controls on the form an manually updates the database. I'd rather bind the controls but don't want to go forward this a "fix" which I can't explain.
Thanks,
Rich...
