Microsoft KB Archive/116276

{| = PRB: Selecting BIT Field in SQL Server Returns All True Values =
 * width="100%"|

ID: Q116276

2.60   | 2.60a WINDOWS | MACTINTOSH kbprg kbprb The information in this article applies to:


 * Microsoft FoxPro for Windows, version 2.6
 * Microsoft FoxPro for Macintosh, version 2.6a

SYMPTOMS
When you are using a SELECT command to select a field of type BIT from a SQL Server database, the resulting cursor file contains all TRUE (.T.) values.

RESOLUTION
Change the SELECT command so that it selects the BIT field as a calculated field. See the program below for an example.

MORE INFORMATION
A BIT type field in a SQL Server database is a column that holds the value 1 or 0.

Steps to Reproduce Problem
Run the following program (substituting the correct data source name, user name, and password). Notice that the first Browse window will contain all "T" values (representing True or .T.). The second Browse window will contain the correct numeric values.

CLOSE DATABASES CLEAR

**Load Library IF !"FPSQL"$SET("LIBRARY") PRIVATE m.err m.err=.F.     ON ERROR m.err=.T.      SET LIBRARY TO ; LOCFILE("FPSQL","FLL","Where is FPSQL.FLL?") ADDITIVE ON ERROR IF m.err RETURN ENDIF ENDIF

**Connect to SQL Server WAIT WINDOW "Connecting..." NOWAIT * On the following command line, substitute the correct data source * name for 'test', the correct user name for 'sa', and the correct * password for ''. m.handle=DBCONNECT('test','sa','') IF m.handle > 0 WAIT WINDOW "Connected Successfully" NOWAIT ELSE WAIT WINDOW "No connection made" NOWAIT RETURN ENDIF

**Create table with 'bit' type field WAIT WINDOW "Creating Table ..." NOWAIT m.retval=DBEXEC(m.handle,'CREATE TABLE bitjunk (thebit bit)') m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (1)') m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (0)') m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (1)') m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (1)') m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (0)') m.retval=dbexec(m.handle,'SELECT * FROM bitjunk','one') WAIT WINDOW "Notice all values are True" NOWAIT BROWSE m.retval=dbexec(m.handle,'SELECT thebit*1 FROM bitjunk','two') WAIT WINDOW "Notice values are Correct" NOWAIT BROWSE m.retval=dbdisconn(m.handle) Additional reference words: FoxWin FoxMac 2.60 2.60a ODBC KBCategory: kbprg kbprb KBSubcategory: FxinteropOdbc Keywords         : kbVFp FoxWin FxinteropOdbc KBQ Version          : 2.6 2.6a Platform         : MACINTOSH WINDOWS Issue type       : kbprb
 * }