We help IT Professionals succeed at work.

Setting value to NULL with ADO Recordset's Update method

MelissaEvans
MelissaEvans asked
on
How can I use the ADO Recordset's Update method to set a field to NULL? It's not a huge problem for string data types (I've been cheating and setting it to ''); but datetimes don't like that.
<code>
RS.Fields(FieldName).Value = ???
RS.Update
</code>
I've played with this on a field of type char(30) so I can see whatever gets used, and I've noticed that vbNull sets it to 1 and "null"/"NULL"/"<NULL>" sets it to the string "null"/"NULL"/"<NULL>" respectively. I'm using an ODBC connection to a SQL Server 7.0 database. I know that Ctl 0 (zero) will put in a null when I'm in SQL Server's Enterprise Manager, but I don't know if that'll help here (I can't find any reference to using--not detecting--the CTRL character).

Thanks in advance. =)
~Melissa
Comment
Watch Question

BRONZE EXPERT
Top Expert 2012
Commented:
Have you tried:
RS.Fields(FieldName).Value = Null
RS.Update

Anthony

Commented:
In my experience if you simply don't set a value into the field it by default is NULL.  When you execute the update, the record is updated with null values in any field you didn't explicitly set.

Now this will be OK if the recordset field has an attribute of adFldIsNullable. Otherwise you will get an error indicating the field cannot contain a null or some weird result like that.

BRONZE EXPERT
Top Expert 2012

Commented:
Melisa,

vbNull is the constant for VarType and has a value of 1.

"null"/"NULL"/"<NULL>" are all strings, hence the result.

Try without the quotes,
Anthony
BRONZE EXPERT
Top Expert 2012

Commented:
The Ctrl-0 in SQL Server's Enterprise Manager is just a short-cut in EM to update a column with a Null and is not relevant anywhere else.

Anthony

Author

Commented:
How silly; I think I tried everything but that.  Sometimes it's the most obvious answer.  Thanks Anthony.  =)

Edwinson - you're right, but sometimes the user changes his/her mind.  My user brought this to my attention because she had put data in, then realized she didn't want it there, and tried to take it out.  It was a datetime, so the database was unhappy with trying to set the field to ''.  

~Melissa

Author

Commented:
How silly; I think I tried everything but that.  Sometimes it's the most obvious answer.  Thanks Anthony.  =)

Edwinson - you're right, but sometimes the user changes his/her mind.  My user brought this to my attention because she had put data in, then realized she didn't want it there, and tried to take it out.  It was a datetime, so the database was unhappy with trying to set the field to ''.  

~Melissa

Commented:
He not a problem.  It always boils down to the user in any situation.  The use of the VB constant vbNull is also a good alternative, however I have also had a situation where the software comes back as an invalid use of Null.