Cannot retrieve records from FOX DBF through OLEDB

[Marcelo]

Hi, I just want to retrieve some records from a DBF file to a recordset
throgh VB6 and I get 0 records when I should get a set of them.

This is the code:

Set cnn1 = New ADODB.Connection

strCnn = "Provider=VFPOLEDB.1;Data
Source=C:\Lince\DBF\mer.dbf;Mode=ReadWrite|Share Deny
None;Password='';Collating Sequence=MACHINE"

cnn1.Open strCnn

' Open table
Set rst1 = New ADODB.Recordset
datFecha = "2004/12/30"
datFecha1 = FormatDateTime(datFecha, vbShortDate)
strSQL = "select * from mer where fchr = {^" & datFecha1 & "}"
rst1.Open strSQL, cnn1, adOpenKeyset, adLockOptimistic, adCmdText


Why is it that I get no records? Is it a problem of this provider driver? I
can easily manage a set of records like this with DAO with Data Control but
that's not the idea since I use over a million records and I want to do it
as fast as possible.

Hope you can help me to figure this out. Thanks in advance, Marcelo.

[Fred Taylor]

Why not just use:

strSQL = "select * from mer where fchr = ?datFecha"
--
Fred
Microsoft Visual FoxPro MVP

Post by Marcelo
Hi, I just want to retrieve some records from a DBF file to a recordset
throgh VB6 and I get 0 records when I should get a set of them.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=VFPOLEDB.1;Data
Source=C:\Lince\DBF\mer.dbf;Mode=ReadWrite|Share Deny
None;Password='';Collating Sequence=MACHINE"
cnn1.Open strCnn
' Open table
Set rst1 = New ADODB.Recordset
datFecha = "2004/12/30"
datFecha1 = FormatDateTime(datFecha, vbShortDate)
strSQL = "select * from mer where fchr = {^" & datFecha1 & "}"
rst1.Open strSQL, cnn1, adOpenKeyset, adLockOptimistic, adCmdText
Why is it that I get no records? Is it a problem of this provider driver?
I can easily manage a set of records like this with DAO with Data Control
but that's not the idea since I use over a million records and I want to
do it as fast as possible.
Hope you can help me to figure this out. Thanks in advance, Marcelo.

[Marcelo]

Fred, if I use the sentence you sent me it throws the following error:
Run-time error '-2147217900 (80040e14)': Command contains unrecognized
phrase/keyword.

Post by Fred Taylor
strSQL = "select * from mer where fchr = ?datFecha"
--
Fred
Microsoft Visual FoxPro MVP

Post by Marcelo
Hi, I just want to retrieve some records from a DBF file to a recordset
throgh VB6 and I get 0 records when I should get a set of them.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=VFPOLEDB.1;Data
Source=C:\Lince\DBF\mer.dbf;Mode=ReadWrite|Share Deny
None;Password='';Collating Sequence=MACHINE"
cnn1.Open strCnn
' Open table
Set rst1 = New ADODB.Recordset
datFecha = "2004/12/30"
datFecha1 = FormatDateTime(datFecha, vbShortDate)
strSQL = "select * from mer where fchr = {^" & datFecha1 & "}"
rst1.Open strSQL, cnn1, adOpenKeyset, adLockOptimistic, adCmdText
Why is it that I get no records? Is it a problem of this provider driver?
I can easily manage a set of records like this with DAO with Data Control
but that's not the idea since I use over a million records and I want to
do it as fast as possible.
Hope you can help me to figure this out. Thanks in advance, Marcelo.

[Anders]

SELECT * FROM TableName WHERE fchr = DATE(2000,12,30)
If fchr is a character data type, use DTOC(DATE(2000,12,30))
When you don't specify a DBC database as the data source type, you only set
the connecrion to the folder where your table is, the whole folder is
treated as your database.
For VFP6 table you can always use VFPODBC too.
-Anders

[Marcelo]

Hi Anders, if I use the DTOC it throws me an error, even at VFP Command line
I don't use a DBC but DBF.

Post by Anders
SELECT * FROM TableName WHERE fchr = DATE(2000,12,30)
If fchr is a character data type, use DTOC(DATE(2000,12,30))
When you don't specify a DBC database as the data source type, you only set
the connecrion to the folder where your table is, the whole folder is
treated as your database.
For VFP6 table you can always use VFPODBC too.
-Anders

[Anders]

Hi Marcelo
Too check if the connection is working right, make a simple SELECT, no WHERE
clause.
That shows I suppose that you're dealing with the datatype Date, not with a
string representing a date. Drop DTOC and try DATE(year,month,day). It's
supported since VFP6. Or does that also throw an error.
Passing variables to ADO + OLE DB connections is a separate problem. You may
have to use the Parameters collection of the Command object.
-Anders

Post by Marcelo
Hi Anders, if I use the DTOC it throws me an error, even at VFP Command line
I don't use a DBC but DBF.

[Marcelo]

Hi Anders, I tried a simple "select * from mer" and it works ok. It
retrieves all the records from the DBF.
Anyway, do you think this is a case where you must use parameters
collection? I'm not using stored procedures (I don't think VFP supports it
either) in order to pass through parameters.

I also tried to code this all in a single string line like: "select * from
mer where date='2004/08/30'" and it result in 0 records again.

I'll try DATE() function within VB6 code with the SQL String and let you
know what happen.

Post by Anders
Hi Marcelo
Too check if the connection is working right, make a simple SELECT, no
WHERE clause.
That shows I suppose that you're dealing with the datatype Date, not with a
string representing a date. Drop DTOC and try DATE(year,month,day). It's
supported since VFP6. Or does that also throw an error.
Passing variables to ADO + OLE DB connections is a separate problem. You may
have to use the Parameters collection of the Command object.
-Anders

Post by Marcelo
Hi Anders, if I use the DTOC it throws me an error, even at VFP Command line
I don't use a DBC but DBF.

[Anders]

If your date is datatype Date you can't pass a string date. Pass
{^2000/8/30}

or DATE(2000,8,3)

If you send the command

SET STRICTDATE TO 0

You can skip the ^ in the date and use whaterever SET DATE format is
currently in force.

-Anders

[Marcelo]

Hi Anders, I understand what you mean but it won't work anyway.
I've already tried with the following sentences:

'strSQL = "select * from mer where fchr=" & datFecha1
'strSQL = "select * from mer where fchr = ?datFecha1"
'strSQL = "select * from mer where fchr='" & datFecha1 & "'"
'strSQL = "select * from mer where fchr = #" & datFecha1 & "#"
'strSQL = "select * from mer where fchr = {^" & datFecha1 & "}"
'strSQL = "select * from mer where fchr = {^" & datFecha1 & "}"
'strSQL = "SELECT * FROM mer WHERE (mer.fchr>{ts '2004/12/30 00:00:00'}
And mer.fchr<{ts '2004/12/30 23:59:59'})"
'strSQL = "select * from mer"

Post by Anders
If your date is datatype Date you can't pass a string date. Pass
{^2000/8/30}
or DATE(2000,8,3)
If you send the command
SET STRICTDATE TO 0
You can skip the ^ in the date and use whaterever SET DATE format is
currently in force.
-Anders

[Anders]

I tried this in VFP without problems:

LOCAL cn AS ADODB.Connection, rs AS ADODB.Recordset
cn=CREATEOBJECT('ADODB.Connection')
cn.ConnectionString=[Provider="VfpOLEDB";Data Source="C:\PROGRAM
FILES\MICROSOFT VISUAL FOXPRO
9\SAMPLES\northwind\northwind.DBC";Exclusive=No]
cn.Open
rs=cn.Execute("SELECT * FROM Orders WHERE orderdate = DATE(1996,7,5)")
FOR i= 0 TO rs.Fields.Count-1
DEBUGOUT rs.Fields(i).Value
NEXT

The format {ts '2004/12/30 00:00:00'} is an ODBC convention. Not sure it
works with OLEDB
In my test it doesn't seem to be accepted by OLEDB.
-Anders

[Marcelo]

ANDERS, YESSSSS!!!! (ehem... sorry...)

The ts is for ODBC. You were right too.

That's the way to execute the query:

strSQL = "Select * from mer where fchr=DATE(" & Year(datFecha) & "," &
Month(datFecha) & "," & Day(datFecha) & ")"

I hope I don't have the same problems with Insert, Update and Delete
methods.
By the way. Is it possible to use Stored Procedures with VFP? It'd be cool.

Thanks a lot Anders for keeping this thread till the end!! :)

Post by Anders
LOCAL cn AS ADODB.Connection, rs AS ADODB.Recordset
cn=CREATEOBJECT('ADODB.Connection')
cn.ConnectionString=[Provider="VfpOLEDB";Data Source="C:\PROGRAM
FILES\MICROSOFT VISUAL FOXPRO
9\SAMPLES\northwind\northwind.DBC";Exclusive=No]
cn.Open
rs=cn.Execute("SELECT * FROM Orders WHERE orderdate = DATE(1996,7,5)")
FOR i= 0 TO rs.Fields.Count-1
DEBUGOUT rs.Fields(i).Value
NEXT
The format {ts '2004/12/30 00:00:00'} is an ODBC convention. Not sure it
works with OLEDB
In my test it doesn't seem to be accepted by OLEDB.
-Anders

[Anders]

VFPOLEDB can do stored procedures and even execute programs stored in a
folder. A procedure call can return a query result cursro into an ADO
RecordSet. See SetResultSet in the documentation.

-Anders