FoxPro database connector - Cannot retrieve records from FOX DBF through OLEDB

Author

Cannot retrieve records from FOX DBF through OLEDB

Marcelo

2005-09-27, 8:25 pm

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|S
hare Deny
None;Password='';Col
lating Sequence=MACHINE"

cnn1.Open strCnn

' Open table
Set rst1 = New ADODB.Recordset
datFecha = "2004/12/30"
datFecha1 = FormatDateTime(datFe
cha, 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

2005-09-27, 8:25 pm

Why not just use:

strSQL = "select * from mer where fchr = ?datFecha"

--
Fred
Microsoft Visual FoxPro MVP

"Marcelo" <forum@uniplus.com.ar> wrote in message
news:%23hh4se7wFHA.1124@TK2MSFTNGP12.phx.gbl...
> 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|S
hare Deny
> None;Password='';Col
lating Sequence=MACHINE"
>
> cnn1.Open strCnn
>
> ' Open table
> Set rst1 = New ADODB.Recordset
> datFecha = "2004/12/30"
> datFecha1 = FormatDateTime(datFe
cha, 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

2005-09-28, 3:25 am

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

2005-09-28, 9:24 am

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

"Fred Taylor" <ftaylor@mvps.org!REMOVE> escribió en el mensaje
news:uogTfp8wFHA.2232@TK2MSFTNGP11.phx.gbl...
> Why not just use:
>
> strSQL = "select * from mer where fchr = ?datFecha"
>
>
> --
> Fred
> Microsoft Visual FoxPro MVP
>
>
> "Marcelo" <forum@uniplus.com.ar> wrote in message
> news:%23hh4se7wFHA.1124@TK2MSFTNGP12.phx.gbl...
>
>

Marcelo

2005-09-28, 9:24 am

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" <anders@anders> escribió en el mensaje
news:eGwpd2$wFHA.2348@TK2MSFTNGP15.phx.gbl...
> 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

2005-09-28, 11:24 am

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

"Marcelo" <forum@uniplus.com.ar> skrev i meddelandet
news:eLNZuoCxFHA.612@TK2MSFTNGP10.phx.gbl...
> 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

2005-09-28, 1:24 pm

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.

"Anders" <anders@anders> escribió en el mensaje
news:eBXd0EExFHA.2348@TK2MSFTNGP15.phx.gbl...
> 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
>
> "Marcelo" <forum@uniplus.com.ar> skrev i meddelandet
> news:eLNZuoCxFHA.612@TK2MSFTNGP10.phx.gbl...
>
>

Anders

2005-09-28, 8:26 pm

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

2005-09-28, 8:26 pm

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"

"Anders" <anders@anders> escribió en el mensaje
news:Oo2g3zFxFHA.736@tk2msftngp13.phx.gbl...
> 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

2005-09-29, 3:25 am

I tried this in VFP without problems:

LOCAL cn AS ADODB.Connection, rs AS ADODB.Recordset
cn=CREATEOBJECT('ADO
DB.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

2005-09-29, 7:25 am

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!! :)

"Anders" <anders@anders> escribió en el mensaje
news:O$4KL7LxFHA.2348@TK2MSFTNGP15.phx.gbl...
>I tried this in VFP without problems:
>
> LOCAL cn AS ADODB.Connection, rs AS ADODB.Recordset
> cn=CREATEOBJECT('ADO
DB.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

2005-09-29, 11:24 am

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

Sponsored Links