DOCUMENT:Q191717  24-AUG-1998  [vbwin]
TITLE   :HOWTO: Displaying Boolean Values in Bound DataGrid
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Professional and Enterprise Editions for
   Windows, version 6.0
---------------------------------------------------------------------

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
======================================================================
Platform          : WINDOWS
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.