DOCUMENT:Q216955  27-JUL-2001  [foxpro]
TITLE   :PRB: Negative Values Put in Remote View Rounded Up in SQL Table
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:2.5,3.0,3.0b,5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:kbDatabase kbODBC kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDS

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

 - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0 
 - Microsoft Data Access Components version 2.5 
-------------------------------------------------------------------------------

SYMPTOMS
========

Negative values inserted into numeric fields of remote views of SQL Server
tables are rounded up by 0.01.

RESOLUTION
==========

There are three alternative resolutions for this issue:

1. Install the 3.70 ODBC driver.

2. Set the DataType of the numeric field of the remote view to "Y" (currency) as
   in the following example:

   =DBSETPROP('testview.mykey','Field','KeyField',.T.)
   =DBSETPROP('testview.mykey','Field','Updatable',.T.)
   =DBSETPROP('testview.mykey','Field','UpdateName',lcsysdbo+'myKEY')
   =DBSETPROP('testview.mykey','Field','DataType',"C(12)")
   =DBSETPROP('testview.myamt','Field','KeyField',.F.)
   =DBSETPROP('testview.myamt','Field','Updatable',.T.)
   =DBSETPROP('testview.myamt','Field','UpdateName',lcsysdbo+'myamt')
   =DBSETPROP('testview.myamt','Field','DataType',"Y")

3. Use SQL Pass-through commands as in the following example:

   sqlcommand="INSERT INTO testamount (mykey, myamt)" + ;
      "VALUES ('" + m.cmykey + "', " + STR(0-m.nnumber,6,2) + " )"
   =sqlexec(gnconnhandle,sqlcommand)

MORE INFORMATION
================

This behavior occurs under the following conditions:

 - The DataType for the field of the remote view is N (numeric).

 - The value inserted into the numeric field is negative.

 - The DataType for the corresponding field of the base SQL Server table is
   NUMERIC.

 - The 3.60.0319 version of the SQL Server ODBC driver is being used.

Steps to Reproduce Behavior
---------------------------

1. Create a program file named "SQLRND.PRG" (without the quotation marks) using
   the following code:

   LOCAL lcconnstring 
   LOCAL lncounter, lnhandle, lnchecksource
   LOCAL lbsourceexists, lbtableexists, lbconnexists, lbviewexists
   lcconnstring    =  "DRIVER={SQL Server};" + ;
      "SERVER=MY_SERVER;DATABASE=PUBS;UID=sa;PWD="
   lncounter       =   0
   lnhandle = SQLSTRINGCONN(lcconnstring)
   IF lnhandle>0
      lnchecksource=sqltables(lnhandle,'TABLE','sourcetabs')
      SELECT sourcetabs
      SCAN
         IF UPPER(ALLTRIM(table_name))="TestSQL"
            lbsourceexists=.T.
            EXIT
         ENDIF
      ENDSCAN
      IF !lbsourceexists
         tmpcommand="CREATE TABLE dbo.TestSQL (MYKEY CHAR(10),"
         tmpcommand=tmpcommand+" MYAMT NUMERIC(18,4))"
         lnreturnresult=sqlexec(lnhandle,tmpcommand)
      ELSE
         sqlcommand="DELETE FROM TestSQL"
         lnreturnresult=SQLEXEC(lnhandle,sqlcommand)
      ENDIF
      =sqldisconnect(lnhandle)
      SET EXCLUSIVE ON
      SET MULTILOCKS ON
      CLOSE DATA ALL
      CREATE DATABASE sqltest
      CREATE CONNECTION TEST1 CONNSTRING (lcconnstring)
      =DBSETPROP('TEST1','Connection','Asynchronous', .F.)
      =DBSETPROP('TEST1','Connection','BatchMode', .T.)
      =DBSETPROP('TEST1','Connection','Comment', '')
      =DBSETPROP('TEST1','Connection','DispLogin', 3)
      =DBSETPROP('TEST1','Connection','ConnectTimeOut', 15)
      =DBSETPROP('TEST1','Connection','DispWarnings', .T.)
      =DBSETPROP('TEST1','Connection','IdleTimeOut', 0)
      =DBSETPROP('TEST1','Connection','QueryTimeOut', 0)
      =DBSETPROP('TEST1','Connection','Transactions', 2)
      CREATE SQL VIEW testview REMOTE CONNECT 'TEST1' SHARE ;
         AS SELECT * ;
         FROM TestSQL ;
         ORDER BY mykey
   ENDIF
   =DBSETPROP('testview','View','SendUpdates',.T.)
   =DBSETPROP('testview','View','UpdateType',1)
   =DBSETPROP('testview','View','WhereType',3)
   =DBSETPROP('testview','View','BatchUpdateCount',1)
   =DBSETPROP('testview','View','UseMemoSize',255)
   =DBSETPROP('testview','View','FetchSize',100)
   =DBSETPROP('testview','View','MaxRecords',-1)
   =DBSETPROP('testview','View','Tables','dbo.TestSQL')
   =DBSETPROP('testview','View','Prepared',.F.)
   =DBSETPROP('testview','View','FetchMemo',.F.)
   =DBSETPROP('testview','View','CompareMemo',.F.)
   =DBSETPROP('testview','View','FetchAsNeeded',.F.)
   =DBSETPROP('testview','View','FetchSize',100)
   =DBSETPROP('testview','View','Comment',"")
   =DBSETPROP('testview','View','ShareConnection',.T.)
   =DBSETPROP('testview.mykey','Field','KeyField',.T.)
   =DBSETPROP('testview.mykey','Field','Updatable',.T.)
   =DBSETPROP('testview.mykey','Field','UpdateName','dbo.TestSQL.myKEY')
   =DBSETPROP('testview.mykey','Field','DataType',"C(12)")
   =DBSETPROP('testview.myamt','Field','KeyField',.F.)
   =DBSETPROP('testview.myamt','Field','Updatable',.T.)
   =DBSETPROP('testview.myamt','Field','UpdateName','dbo.TestSQL.myamt')
   =DBSETPROP('testview.myamt','Field','DataType',"N(15,2)")
   USE testview IN 0
   SELECT testview
   DELETE FROM testview
   =REQUERY('testview')
   FOR lncounter = 1 TO 10
      m.nnumber = 0-lncounter
      m.cmykey = PADL(ALLTRIM(STR(lncounter,10,0)),FSIZE("MYKEY")-4 , '0')
      INSERT INTO Testview (mykey, myamt) VALUES (m.cmykey,m.nnumber )
   NEXT
   BROW TITLE 'Display values inserted into remote view'
   =TABLEUPDATE(.T.)
   =REQUERY('testview')
   BROW TITLE 'Negative Values inserted into view with NUMERIC datatype' + ;
      'are rounded up by 0.01'
   USE
   CLOSE ALL
   CLEAR ALL

2. From the Command window, type:

   DO SQLRND

3. Note the numeric values when the first BROWSE window appears.

4. Note that the numeric values displayed in the second BROWSE window have been
   rounded up by 0.01.

(c) Microsoft Corporation 1999, All Rights Reserved.
Contributions by John Desch, Microsoft Corporation


Additional query words:

======================================================================
Keywords          : kbDatabase kbODBC kbSQL kbvfp300 kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDSFox kbGrpDSMDAC kbDSupport kbMDAC250 kbSQLProg 
Technology        : kbVFPsearch kbAudDeveloper kbMDACSearch kbMDAC250 kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Version           : WINDOWS:2.5,3.0,3.0b,5.0,5.0a,6.0
Issue type        : kbprb

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

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 2001.