Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Location
    Coventry, England
    Posts
    2

    Unhappy Unanswered: [SoftVelocity Inc.][TopSpeed ODBC Driver]Expected lexical element not found: FROM

    Hi,
    I am trying to use version 4 of the ODBC Driver for the softvelocity topspeed database in Visual Basic 2005 Express edition.

    I have created a connection to the database and am using an SQL string to open a recordset unsing ADO. The problem is that there is a field in the database that I need to use MS Access reports as being a memo data type.

    In the first place, when trying to use the field, the program simply closes without error. I can view/preview the data in other fields in the recordset in break mode (with the handy pop up that appears when you hover over a field reference) but the pop up shows "error: cannot obtain value" for the Memo data type (Named NOTES) for all properties. Once I have 'popped up' this information the program closes without error again after a few seconds.

    I am guessing that this is because of the size of the field, it takes too long to read or is so large (even when empty) that the program just gives up!???

    So, I am trying to get around it by 'casting' the field as something more manageable, a string, in my SQL statement when opening the recordset. Only problem is that I am now getting the error
    [SoftVelocity Inc.][TopSpeed ODBC Driver]Expected lexical element not found: FROM
    Because inserting the CAST() function seems to confuse the database.

    If anyone who is familiar with the topspeed database could let me know how I should pass the functions or other alternatives to achieve my desired results that would be most appreciated.

    In case I didn't say what I'm trying to achieve; I want to read the contents of the NOTES field into another field based on certain criteria. This is because the API for the program I'm using doesn't allow me to write to the field I need in the first place.

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fancy posting your SQL statements?
    One with and and one without cast.
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Location
    Coventry, England
    Posts
    2

    Unhappy

    Originally using:
    SELECT * FROM SalesOrderHeader WHERE SPECIALINSTRUCTIONS2='' AND SALESORDERNUMBER>23516 AND CAMPAIGNREFERENCE<>'CCC DAILY'
    to return all fields

    then:
    SELECT SALESORDERNUMBER, SPECIALINSTRUCTIONS2, CAST(SalesOrderHeader.NOTES AS CHAR(50)) AS NOTES FROM SalesOrderHeader WHERE SPECIALINSTRUCTIONS2='' AND SALESORDERNUMBER>23516 AND CAMPAIGNREFERENCE<>'CCC DAILY'

    Tried with and without AS NOTES field name declaration and also tried CAST(SalesOrderHeader.NOTES, TEXT) but the SQL references I've checked specify the one in the 2nd SQL statement. Don't know where I got the idea that this one might work though!?

    Have since enumerated through the fields returned by * and can get the field names and values for all fields except NOTES which either crashes the program when trying to view it or generates:
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    when trying to add its value to another field.

Posting Permissions

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