Selecting records from Foxpro Table

[sduraiappa]

I am trying to extract some records from a table (exam.dbf) and copy the recordset to another dbf file.

I use the following connection string:

CNSTR = "Provider=MSDASQL.1;" & _
"Persist Security Info=False;" & _
"Driver={Microsoft Visual FoxPro Driver};" & _
"SourceDB= " & SRC_DBF_FILE & ";" & _
"SourceType=DBF;Exclusive=No;" & _
"BackgroundFetch=Yes;Collate=General;Deleted=Yes"
cn1.Open CNSTR

And i have the following statements:

STR1 = "select * from exam where date>={^" &
Format(FROMDT, "yyyy/mm/dd") & "} and date<={^" &
Format(TODT, "yyyy/mm/dd") & "} INTO TABLE " &
TDEST_FOLDER & "EXAMNEW.dbf"
cn1.Execute STR1

When i run the statements i dont get the new DBF file EXAMNEW.DBF in the Tdest_folder.

I am not sure where i get the error. If i run the same query in visual foxpro i get the new table. I am not geting the new table while running the query in VB.

Please help. Its urgent.

Thanks

Duraiappan

 

[SBerthold]

>TDEST_FOLDER

Where is this folder? Try some All-Users documents folder and see if that works.

Also, try it without the given criteria. Instead, use the following criteria to just create the table without data:

WHERE 0=1

 

[Swi]

Deos TDEST_FOLDER have a slash after it? If not maybe:

TDEST_FOLDER & "\EXAMNEW.dbf"

Swi

 

[sduraiappa]

TDEST_FOLDER has a value C:\

As i said earlier, i tried this in Visual Foxpro and its working. I have problem while executing this in VB.

Duraiappan

 

[sduraiappa]

Any other to help me.

Please, its urgent.

Duraiappan

 

[SBerthold]

Did you try everything I mentioned?

 

[Swi]

Try this:

STR1 = "select * from exam  where " & Date & " >={^" & 
Format(FROMDT, "yyyy/mm/dd") & "} and " & Date & " <={^" & 
Format(TODT, "yyyy/mm/dd") & "} INTO TABLE " & 
TDEST_FOLDER & "EXAMNEW.dbf"
    cn1.Execute STR1

Also, I am not familiar with why you would need the curly brackets {} and the carets ^ in your statement. Maybe it is a foxpro thing?

Swi

 

[sduraiappa]

SBerthold :

I tried as mentioned by you. Removed the conditions and try. But i dont get the new table.

Swi :

Curly brackets are used to denote date in foxpro.

What changes have u made in the statement? It resembles the same which i had.

Duraiappan

 

[strongm]

CAn we just check what SRC_DBF_FILE is set to?

 

[Swi]

You had the word [red]date[/red] within the quotes. I took it out of the quotes because otherwise I would not think that it would return the current date.

Swi

 

[strongm]

>I would not think that it would return the current date

No, but that's because we are looking at the date column in the table ...

 

[Swi]

Then it would need to be in brackets [date] as it is a reserved word.

Swi

 

[strongm]

Date is not a reserved word in FoxPro SQL

 

[Swi]

Did not know that. Very interesting.

Swi

 

[sduraiappa]

Thanks for your mails.

I manage to change the program, to create a new table EXAMNEW.DBF with the structure of EXAM.DBF and copy the records one by one.

I solve the problem. But still i dont know whats wrong in my earlier query.

Duraiappan.

 

[johnwm]

Since this appears to be a VFP issue rather than a VB issue you might try forum1252

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[BobRodes]

<CAn we just check what SRC_DBF_FILE is set to?

Guess not....