|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 321631 - Last Review: February 7, 2005 - Revision: 2.1 BUG: Visual FoxPro OLE DB Provider only works with CHAR ADODB.Command parametersThis article was previously published under Q321631
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.
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 = ".T.", .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.
*-----------------------------------
#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")
This problem was fixed in Visual FoxPro 8 OLE DB Provider. 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
#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:
Object reference not set to an instance of an object.
APPLIES TO- Microsoft OLE DB Provider for Visual FoxPro 9.0
- Microsoft OLE DB Provider for Visual FoxPro 9.0
 Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated. 
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
|
 |
|
 |
|
|
|
| |