Microsoft & .NETVisual BasicVB Coding Tip: Using the Date Type with an ADO Source

VB Coding Tip: Using the Date Type with an ADO Source

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

The first thought to handle a date in VB is to use a Date type variable. In fact, this will not work for Null dates coming from an ADO source. The reason is that the date internal type has a different behavior than the adDate ADO type. To see the differences between Date and adDate take a look at the following code (under VB6 with Microsoft ActiveX Data Objects Recordset 2.0 Library, but the same would happen with Microsoft ActiveX Data Objects 2.0 Library):

Private Sub Date_handling()
Dim lDate As Date
Dim lDateVar As Variant
Dim lRs As ADOR.Recordset

'Initialization:
Set lRs = New ADOR.Recordset
lRs.Fields.Append "MyDate", _
                  adDate, , adFldIsNullable
lRs.Open
lRs.AddNew
lRs!MyDate = Date
MsgBox lRs!MyDate
'Storing:
lDate = lRs!MyDate
lDateVar = lRs!MyDate
'Setting:
'lDate = Null 'This would not work
lDateVar = Null 'This will work
lRs!MyDate = lDateVar
'lRs!Update
lRs.Close
End Sub

A String is not more appropriate: a Null string does not represent a Null date. Unfortunately, the default setting in VB6’s DataEnvironment puts a TextBox on the form when you drag and drop a date field. To have a good internal representation of the date, you should use a Variant.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories