How To Display Boolean Values in Bound DataGrid (191717)

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new standard EXE project in Visual Basic. Form1 is created by default.
2. In the project, reference Microsoft ActiveX Data Objects 2.0 Library and Microsoft Data Formatting Object Library.
3. Add a DataGrid to the form.
4. Add the following code and run:

         Option Explicit
   
         Private rs As ADODB.Recordset
         Private fmtBooleanData As StdDataFormat
   
         Private Sub Form_Load()
         Dim i As Integer
   
         Set rs = New ADODB.Recordset
         rs.Fields.Append "Field1", adBSTR, 64
         rs.Fields.Append "BooleanField", adBoolean
         rs.Open
   
         rs.AddNew
         rs.Fields("Field1").Value = "Field1"
         rs.Fields("BooleanField").Value = True
         rs.Update
   
         For i = 1 To 5
            rs.AddNew
            rs.Update
         Next i
         rs.MoveFirst
   
         Set DataGrid1.DataSource = rs
   
         ' set up Boolean Formatting
         Set fmtBooleanData = New StdDataFormat
         fmtBooleanData.Type = fmtBoolean
         fmtBooleanData.TrueValue = "Yes"
         fmtBooleanData.FalseValue = "No"
         fmtBooleanData.NullValue = ""
   
         Set DataGrid1.Columns(1).DataFormat = fmtBooleanData
   
         End Sub
   
   					

When the code is run, you will see "Yes" in the 2nd column of the first row, and #ERROR in the rest.

This occurs because OLE DB cannot determine what to return for an empty non-nullable field, and therefore raises an error. Marking the field as nullable by changing the line:

   rs.Fields.Append "BooleanField", adBoolean
				

to:

   rs.Fields.Append "BooleanField", adBoolean, , adFldIsNullable
				

will result in the empty fields returned as NULLs rather than errors.