HOWTO: Displaying Boolean Values in Bound DataGrid

ID: Q191717

The information in this article applies to:

SUMMARY

An empty binary field formatted with rules to print text, such as "Yes" for True and "No" for False, will return an error unless nulls are allowed.


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.

(c) Microsoft Corporation 1998. All Rights Reserved.
Contributions by Jim Haugen, Microsoft Corporation

Additional query words: kbDSupport kbdse kbCtrl kbVBp600 kbVBp kbADO kbDatabase 


Keywords          : 
Version           : 
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: June 9, 1999