help11111111111


Hello everyone,

I have some memo fields with carriage returns that I need to strip out. The problem is that some of the fields have more than 255 characters, so I get the error:

[Error Code: 903, SQL State: S1000] [Microsoft][ODBC Visual FoxPro Driver]String is too long to fit.

I am very new to Foxpro but no matter what sort of magic I try I can't seem to figure out how to make this work!

Any ideas

Thanks!




Re: strtran problem - string is too long

dni


It is a native error of ODBC driver. You may need to split field or try to store in a variable depending on your vfp version.

http://msdn.microsoft.com/library/default.asp url=/library/en-us/odbc/htm/vfpodbcvisualfoxproodbcdrivernativeerrormessages.asp







Re: strtran problem - string is too long

Naomi Nosonovsky

Can you please show us some code where do you experience this problem Are you accessing VFP data from the web page Can you try doing it through OleDB instead




Re: strtran problem - string is too long

CetinBasoz

What does it have to do with StrTran

ODBC driver works with string variables longer than 255 in length but not with literals longer than 255. Using OleDb driver and parameters collection is the easiet way to handle.





Re: strtran problem - string is too long

help11111111111

Here is an example of what I am trying to do, a query and a memo field that I am trying to strip an ending carriage return from.

select STRTRAN(FIELDNAME,'\n',' ') from TABLENAME

01/11/2000 11:11:11 (xxxxxxx)
a super duper really long memo field a super duper really long memo fielda super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo fielda super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field a super duper really long memo field





Re: strtran problem - string is too long

Naomi Nosonovsky

With this test

Code Snippet

TEXT TO lcMemo noshow
01/11/2000 11:11:11 (xxxxxxx)
a super duper really long memo field
a super duper really long memo fielda
super duper really long memo field a super duper really
long memo field a super duper really
long memo field a super duper really
long memo field a super duper really
long memo field a super duper really
ENDTEXT

CREATE CURSOR test (MemoField M)
INSERT INTO test VALUES (m.lcMemo)
#define CRLF CHR(13) + CHR(10)
select STRTRAN(MemoField,CRLF,' ') as NewMemo from test

I got the same error as you did in VFP8. In VFP9 you may try to use CAST function to try to cast into Memo type,

e.g. I got this SQL working fine in VFP8

select LEFT(STRTRAN(MemoField,CRLF,' '),254) as NewChar from test

In VFP9 this would work (confirmed by Borislav Borissov)

Code Snippet
select cast(STRTRAN(MemoField,CRLF,' ') as M) as NewMemo

You need OleDB for this to work, since ODBC driver was not updated since VFP6.





Re: strtran problem - string is too long

CetinBasoz

Oh it's not a strtran() problem. You're creating a character field which is 254 at max and trying to put a long content in it. select the memo itself w/o using functions. You can strtran later if you want.



Re: strtran problem - string is too long

help11111111111

Thanks to everyone for replying. As to the posts about creating a characting field, or it being the driver...I should have been more specific. This very command was working on the shorter memo fields, just not on the longer ones...so it was indeed the string length.

So, thanks so much to Naomi! Adding the LEFT function was exactly what I need and now it is working great - thanks so much!!!





Re: strtran problem - string is too long

Naomi Nosonovsky

Glad to help. Also check the CAST suggestion for VFP9.