foxPro ASP ODBC

[mrGreeno]

Hello

I'm desperate for some help with this one guys.

we have an old training database called registrar which uses foxpro tables. we have been using the odbc which gets installed with it. It is the old odbc driver that is no longer suported.
we have been running assessments which update and insert records into the fp tables.

out of the blue we started getting the "can't find installable isam" error rebuilt the server three times now, it works for a while and then the error starts again.

so firstly if anyone know's a way around that, that would be the easiest solution for me.

since then, i tried using the VFP odbc driver, this works except for when i'm trying to insert dates from an ASp page with SQL.

here's the statement:

mySQL = "INSERT INTO PLAN (DUE, EARLIEST, USER_ID, UPDATED, XUNIQUEKEY, XCODE) VALUES (#"& DueDate &"#, #"& DueDate &"#, 'PREG', #"& transdate &"#, '123456', 'VR116' );"


then i get this error:


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Visual FoxPro Driver]Syntax error



I can insert everything else except the dates


it used to work with the old driver as well


any help welcome

kind regards

Matt uk

 

[GriffMG]

Hi,

With the VFP ODBC driver using .asp you could try using the
standard VFP terminology...

mySQL = "INSERT INTO PLAN (DUE, EARLIEST, USER_ID, UPDATED, XUNIQUEKEY, XCODE) VALUES (ctod("& DueDate &"), ctod("& DueDate &"), 'PREG', ctod("& transdate &"), '123456', 'VR116' );"

The only snag is - does VFP think your client/server are using the same regional basis 8/12 or 12/8... Regards

Griff
Keep [Smile]ing

 

[mrGreeno]

thanks for your help

i don't know much about VFP so i don't know what you mean by the regional basis.


I've tried what you suggested though, seem to have made a bit of progress, I get a different error now:

INSERT INTO PLAN (DUE, EARLIEST, USER_ID, UPDATED, XUNIQUEKEY, XCODE) VALUES (ctod(18/9/2002), ctod(18/9/2002), 'userID', ctod(9/18/2002), '123456', 'VR116' );
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Visual FoxPro Driver]Function argument value, type, or count is invalid.

/foxpro/Default.asp, line 67


is there anything wrong with the format of the date?

regards

Matt

 

[GriffMG]

Oops, I should have enclosed the date variables in quotes:

mySQL = "INSERT INTO PLAN (DUE, EARLIEST, USER_ID, UPDATED, XUNIQUEKEY, XCODE) VALUES (ctod('"& DueDate &"'), ctod('"& DueDate &"'), 'PREG', ctod('"& transdate &"'), '123456', 'VR116' );"

By regional I mean an irrating feature of PWS/IIS to vary the way it accepts/expects dates to be presented - you can very easily end up with one day it thinks 8/12 is the eighth of December, then sometimes thinking it is the twelth of August!

There is a trick however, force the server to try and save an unambiguous date (31/12/1999 is always good) early in a given session and then read it back - if it comes back empty, reverse the month and day - and keep writing that way for the rest of the session.

Regards

Griff
Keep [Smile]ing

 

[mrGreeno]

thanks for your help again Griff

it hasn't quite worke unfortunately, I don't get the error anymore but when i check the database nothing has been inserted into the date fields, but everything else has??

do you have any other suggestions?

regards

 

[roswald]

The reason you are getting the error - ([Microsoft][ODBC Visual FoxPro Driver]Function argument value, type, or count is invalid), is for one or both of the following reasons... The function ctod() converts a char string into a date. Either the driver identifies transdate/duedate as dates already or the fields due, earliest, and updated aren't being recognized as date fields.
One suggestion is to check and make certain that the source file is using an actual date and not a datetime field. You could try replacing ctod() with ttod(), which will convert a datetime into a date. The other suggestion is look at the way the date is formatted on the source and then look up the date and time functions for Fox in the MSDN help file. You will certainly be able to find something that will convert your source date into a date that makes the Fox happy.

Hope it helps,

roswald

 

[satyenshanker]

Did the SQL syntax work earlier and do you have indexes created on the FoxPro table.

 

[GriffMG]

The empty fields are an example of the regional problem I was noting!

Try swapping the month and day elements.

Fox returns an empty field for an invalid date such as:

42/12/2002

of if working in dd/mm/yyyy:

12/31/2002

of in US format:

31/12/2002

If you follow me!
Regards

Griff
Keep [Smile]ing

 

[mrGreeno]

thanks guys

in response to satyenshanker, the sql did work when we could use the old FoxPro ODBC Driver before we started getting the "can't find installable ISAM" error.

I don't know an awful lot about foxpro but the tables are the backend for a training database application called registrar, in the directory where the tables are there are .CDX index files for each table, is this what you mean by the index?

but, thank you very much Griff, i've played about with the date format and finally got a date inserted!

hopefully I can get it working everytime.

cheers

guys