Microsoft KB Archive/321631

= BUG: Visual FoxPro OLE DB Provider only works with CHAR ADODB.Command parameters =

Article ID: 321631

Article Last Modified on 2/7/2005

-

APPLIES TO


 * Microsoft OLE DB Provider for Visual FoxPro 9.0
 * Microsoft OLE DB Provider for Visual FoxPro 9.0

-



This article was previously published under Q321631



SYMPTOMS
You can use the classic ADODB Command object or the Visual Studio .NET System.Data.OleDb OleDBCommand object with the OLE DB Provider for Visual FoxPro. However, only command parameters of type CHAR that are used with Command and OleDBCommand objects work correctly.



RESOLUTION
To work around this problem, call a stored procedure in a Visual FoxPro database instead of firing INSERT-SQL commands directly. For example, a stored procedure that is similar to the following can insert into a Visual FoxPro table that is named Q321631, after it casts the received parameters accordingly: 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 = &quot;.T.&quot;, .T., .F.) lp5 = NTOM(VAL(lp5))

INSERT INTO Q321631 VALUES(lp1, lp2, lp3, lp4, lp5) ENDPROC You call this stored procedure through Visual FoxPro 7.0 and classic ADO as follows:

Note This assumes that the following program is in the same directory as the database with the stored procedure that is mentioned earlier: *---
 * CREATED: 07/18/02 09:50:53 AM
 * ABSTRACT: Code written to demonstrate workaround to issue
 * outlined in Microsoft Knowledge base article Q321631.
 * 1) DEFINE ThisDir        JUSTPATH(SYS(16))
 * 2) DEFINE ADCMDTEXT      0x0001
 * 3) DEFINE ADCHAR         129
 * 4) DEFINE ADPARAMINPUT   0x0001
 * 1) DEFINE ADPARAMINPUT   0x0001

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

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

WITH goCMD .ActiveConnection = goConn .CommandText = &quot;WriteToDBF(?,?,?,?,?)&quot; .CommandType = ADCMDTEXT

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

.Execute ENDWITH

goConn.CLOSE RELEASE ALL CLEAR ALL

USE Q321631 BROWSE USE IN SELECT(&quot;Q321631&quot;) Note This problem was fixed in Visual FoxPro 8 OLE DB Provider.



STATUS
Microsoft has confirmed that this is a problem in Microsoft OLE DB Provider for Visual FoxPro, versions 7.0.0.9262 and 7.0.0.9465 (VFP7 SP1).



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:

OLE exception error: One or more arguments are invalid. OLE object may be corrupt.

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


 * 1) DEFINE ThisDir      JUSTPATH(SYS(16))
 * 2) DEFINE ADCMDTEXT    0x0001
 * 3) DEFINE ADCHAR       129
 * 4) DEFINE ADPARAMINPUT 0x0001
 * 5) DEFINE ADDOUBLE     5
 * 6) DEFINE ADDBDATE     133
 * 7) DEFINE ADBOOLEAN    11
 * 8) DEFINE ADCURRENCY   6

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

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

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

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

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

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

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

goConn.CLOSE RELEASE ALL CLEAR ALL

USE Q321631 BROWSE USE IN SELECT(&quot;Q321631&quot;) 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(&quot;Provider=VFPOLEDB.1;Data Source=C:\&quot;) Dim oCmd As New OleDbCommand

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

.CommandText = &quot;Insert Into Q321631 VALUES (?,?,?,?,?)&quot; .Parameters.Add(&quot;CHAR&quot;, OleDbType.Char).Value = &quot;Char Param&quot; .Parameters.Add(&quot;NUM&quot;, OleDbType.Double).Value = 99.24 .Parameters.Add(&quot;Date&quot;, OleDbType.DBDate).Value = Now .Parameters.Add(&quot;Bool&quot;, OleDbType.Boolean).Value = True .Parameters.Add(&quot;Money&quot;, 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(&quot;C:\Q321631.dbf&quot;) End Sub

End Module Run the code. You receive the following error message:

Object reference not set to an instance of an object.

Keywords: kbbug kbpending KB321631

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.