Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    Newcastle, Australia
    Posts
    3

    Unhappy Unanswered: Foxpro/dBase ODBC update problem

    Hi all,

    I have a one off (at this stage!!) need to update one dbf file 'test' from another 'mmdata'. I'm not sure what is possible using the Visual Foxpro ODBC driver, but I certainly don't understand the error message it is generating. SQL I'm running is:

    update test set speed = mmdata.speed where
    prodtype = mmdata.prodtype and
    avediam = mmdata.diameter

    Have tried several variations etc. Field names are correct etc.
    Error produced is:

    [Microsoft][ODBC Visual FoxPro Driver]SQL: Column ' ' is not found.

    Have carried out simple updates without problems.
    Is structure of above incorrect?
    Driver version is 6.1.8629.1 running under Win2000.
    Any ideas? I just don't get it!
    Have tried Foxpro 5 command line as well as SQLedit and CuteSQL.
    SQLedit and CuteSQL give same error .... Foxpro just doesnt do anything.

    Would appreciate any advice!!
    Thanks,
    Greg Shearer

  2. #2
    Join Date
    Mar 2003
    Location
    Newcastle, Australia
    Posts
    3

    Smile

    Hi again,

    Managed to work around my problem.
    Steps required:
    1. obtain trial alternative driver 'DataDirect 4.1 dBASEFile'
    www.datadirect-technologies.com
    2. restructure query using subselects as below:

    update prod set speed =
    (select mmdata.bestspee from mmdata
    where mmdata.prodtype = prod.prodtype and
    mmdata.diameter = prod.avediam and
    pagenumber = 1),
    tagname =
    (select mmdata.tagname from mmdata
    where mmdata.prodtype = prod.prodtype and
    mmdata.diameter = prod.avediam and
    pagenumber = 1)
    where machine not like 'SL%'

    Above worked fine. I assume the standard Visual Foxpro driver has bugs or just isn't that capable, as I still haven't had any success with it.
    Unfortunately the DataDirect driver is US $625!!!

    Hope someone finds this information useful.

  3. #3
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Foxpro/dBase ODBC update problem

    Sorry, I think that your problemm is not the VFP ODBC driver, but you SQL command:

    update test set speed = mmdata.speed where
    prodtype = mmdata.prodtype and
    avediam = mmdata.diameter


    the field mmdata.prodtype is from table mmdata, which is not added in your SQl command. The only table added is test "update test ....". You need the clause FROM in your update command, like this:

    update test set speed = mmdata.speed
    from test join mmdata on test.prodtype=mmdata.prodtype and test.avediam=mmdata.diameter
    where .... additional conditions


    in the clause above I assumed that the relation between the two tables test and mmdata is established through the pair of fields: (prodtype,avediam) -> (prodtype,diameter)


    ionut

  4. #4
    Join Date
    Mar 2003
    Location
    Newcastle, Australia
    Posts
    3

    Post

    Ionut,

    Thanks for the suggestion. I had already tried using a 'from' clause, but it seems these aren't recognised by the Foxpro driver. The table to update from is implied instead by the 'table.field' naming convention. When a 'from' clause is inserted the driver responds with:

    'Command contains unrecognised phrase/keyword'.

    Even the DataDirect dBase driver doesn't seem recognise the 'from' clause. I'm most familiar with SQL under Ingres where a from clause is compulsory!! I did try the structure you suggested .... but no luck.

    Thanks again,
    Greg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •