Unanswered: [SoftVelocity Inc.][TopSpeed ODBC Driver]Expected lexical element not found: FROM
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.
SELECT * FROM SalesOrderHeader WHERE SPECIALINSTRUCTIONS2='' AND SALESORDERNUMBER>23516 AND CAMPAIGNREFERENCE<>'CCC DAILY'
to return all fields
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.