BUG: Visual FoxPro OLE DB Provider only works with CHAR ADODB.Command parameters (321631)

PROCEDURE WriteToDBF(lp1 AS STRING, lp2 AS STRING, lp3 AS STRING, lp4 AS STRING, lp5 AS STRING)
    lp2 = VAL(lp2)
    lp3 = CTOD(lp3)
    lp4 = IIF(lp4 = ".T.", .T., .F.)
    lp5 = NTOM(VAL(lp5))

    INSERT INTO Q321631 VALUES(lp1, lp2, lp3, lp4, lp5)
ENDPROC
				

*-----------------------------------
* CREATED: 07/18/02 09:50:53 AM
* ABSTRACT: Code written to demonstrate workaround to issue
*           outlined in Microsoft Knowledge base article Q321631.
*-----------------------------------
#DEFINE ThisDir         JUSTPATH(SYS(16))
#DEFINE ADCMDTEXT       0x0001
#DEFINE ADCHAR          129
#DEFINE ADPARAMINPUT    0x0001

CLEAR
CLOSE DATABASES ALL
CD (ThisDir)
PUBLIC goCMD AS ADODB.COMMAND, ;
	goConn AS ADODB.CONNECTION

goConn = NEWOBJECT("ADODB.Connection")
goCMD = NEWOBJECT("ADODB.Command")
goConn.OPEN("Provider=VFPOLEDB.1;Data Source=" + ThisDir + "\test.dbc")
? "Connection State: ", goConn.State

WITH goCMD
    .ActiveConnection = goConn
    .CommandText = "WriteToDBF(?,?,?,?,?)"
    .CommandType = ADCMDTEXT

*~  Character parameter
    .PARAMETERS.APPEND(.CreateParameter("CHAR", ADCHAR, ADPARAMINPUT, 10, "Char Param"))
*~  Numeric parameter
    .PARAMETERS.APPEND(.CreateParameter("NUM", ADCHAR, ADPARAMINPUT, 10, "99.24"))
*~  Date parameter
    .PARAMETERS.APPEND(.CreateParameter("DATE", ADCHAR, ADPARAMINPUT, 10, DTOC(DATE())))
*~  Boolean parameter
    .PARAMETERS.APPEND(.CreateParameter("BOOL", ADCHAR, ADPARAMINPUT, 10, ".T."))
*~  Currency parameter
    .PARAMETERS.APPEND(.CreateParameter("MONEY", ADCHAR, ADPARAMINPUT, 10, "99.99"))

    .Execute()
ENDWITH

goConn.CLOSE
RELEASE ALL
CLEAR ALL

USE Q321631
BROWSE
USE IN SELECT("Q321631")
				

MORE INFORMATION

Steps To Reproduce the Behavior

Run the following code in Visual FoxPro 7.0. If you use version 7.0.0.9262 or 7.0.0.9465 of the Vfpoledb.dll (Microsoft OLE DB Provider for Visual FoxPro), you may receive the following error message in Visual FoxPro 7.0:
Alternatively, a Browse window may appear that displays a record that has data only in the character field. All the other fields contain NULLs. If you receive the error, you must click End Task in Visual Fox Pro 7.0 to exit.

To demonstrate that character parameters do work successfully, modify the program, uncomment the first line, (*#DEFINE CharOnly) and then run the code again. A Browse window appears that displays one record.

Note The character parameter was inserted into the table correctly.

*-----------------------------------
* CREATED: 07/18/02 09:50:53 AM
* ABSTRACT: Code written to demonstrate issue outlined in
*           Microsoft Knowledge base article Q321631.
*-----------------------------------

*#DEFINE CharOnly

#DEFINE ThisDir       JUSTPATH(SYS(16))
#DEFINE ADCMDTEXT     0x0001
#DEFINE ADCHAR        129
#DEFINE ADPARAMINPUT  0x0001
#DEFINE ADDOUBLE      5
#DEFINE ADDBDATE      133
#DEFINE ADBOOLEAN     11
#DEFINE ADCURRENCY    6

CLEAR
CLOSE DATABASES ALL
CD (ThisDir)
PUBLIC goCMD AS ADODB.COMMAND, ;
	goConn AS ADODB.CONNECTION

goConn = NEWOBJECT("ADODB.Connection")
goCMD = NEWOBJECT("ADODB.Command")
goConn.OPEN("Provider=VFPOLEDB.1;Data Source=" + ThisDir)
? "Connection State: ", goConn.State

WITH goCMD
    *~  Make a sample table to use.
    .ActiveConnection = goConn
    .CommandText = "CREATE TABLE Q321631 (FCHAR c(10), FNUM n(4,2), FDATE D, FBOOL L, FCUR Y)"
    .CommandType = ADCMDTEXT
    .Execute()

    *~ Now, try to input a record using Command Parameters

    *~  Character parameter
    .PARAMETERS.APPEND(.CreateParameter("CHAR", ADCHAR, ADPARAMINPUT, 10, "Char Param"))

    #IFNDEF CharOnly
        *~  Numeric parameter
        .PARAMETERS.APPEND(.CreateParameter("NUM", ADDOUBLE, ADPARAMINPUT, 4, 99.24))
        *~  Date parameter
        .PARAMETERS.APPEND(.CreateParameter("DATE", ADDBDATE, ADPARAMINPUT, 10, DATE()))
        *~  Boolean parameter
        .PARAMETERS.APPEND(.CreateParameter("BOOL", ADBOOLEAN, ADPARAMINPUT, 1, .T.))
        *~  Currency parameter
        .PARAMETERS.APPEND(.CreateParameter("MONEY", ADCURRENCY, ADPARAMINPUT, 4, $99.99))
    #ENDIF

    #IFDEF CharOnly
        .CommandText = "Insert Into Q321631 (FCHAR) VALUES (?)"
    #ELSE
        .CommandText = "Insert Into Q321631 VALUES (?,?,?,?,?)"
    #ENDIF
    .Execute()
ENDWITH

goConn.CLOSE
RELEASE ALL
CLEAR ALL

USE Q321631
BROWSE
USE IN SELECT("Q321631")
				

You can also reproduce this behavior in Visual Studio .NET. Create a new Visual Basic Console application in Visual Studio .NET on a computer that has the Microsoft OLE DB Provider for Visual FoxPro installed (version 7.0.0.9262 or 7.0.0.9465.) Paste the following code in the default module1.vb:

Imports System.Data.OleDb
Imports System.IO

Module Module1

    Sub Main()
        '-----------------------------------
        ' CREATED: 07/18/02 09:50:53 AM
        ' ABSTRACT: Code written to demonstrate issue outlined in
        '           Microsoft Knowledge base article Q321631.
        '-----------------------------------
        Dim oConn As New OleDbConnection("Provider=VFPOLEDB.1;Data Source=C:\")
        Dim oCmd As New OleDbCommand()

        With oCmd
            .Connection = oConn
            .Connection.Open()
            ' Create a sample FoxPro table
            .CommandText = "CREATE TABLE Q321631 (FCHAR c(10), FNUM n(4,2), FDATE D, FBOOL L, FCUR Y)"
            .CommandType = CommandType.Text
            .ExecuteNonQuery()

            .CommandText = "Insert Into Q321631 VALUES (?,?,?,?,?)"
            .Parameters.Add("CHAR", OleDbType.Char).Value = "Char Param"
            .Parameters.Add("NUM", OleDbType.Double).Value = 99.24
            .Parameters.Add("Date", OleDbType.DBDate).Value = Now
            .Parameters.Add("Bool", OleDbType.Boolean).Value = True
            .Parameters.Add("Money", OleDbType.Currency).Value = 99.99
            Try
                .ExecuteNonQuery()
            Catch oExcp As Exception
                MsgBox(oExcp.Message)
            End Try
        End With

        oConn.Close()
        oConn.Dispose()
        oCmd.Dispose()

        File.Delete("C:\Q321631.dbf")
    End Sub

End Module
				

Run the code. You receive the following error message: