Memo field maxlength??

[311rage]

With the regards to FoxPro memo field...is there any reason why I should not be allowed to insert more than 225 characters with an Insert statement.

I can insert as many characters when I type directly into the field using the db interface, but when I use an insert statement, it errors out at 225.

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

[Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword.

I figured it had to be a driver problem but I just spent a few hours making sure everything is up to date. They are still using 6.0 so I dont know if this is something that was fixed in 7.0.

 

[SGLong]

I've run into this before and I believe there is truly a limit on inserting into a memofield. The work-around I came up with was to copy the memo field to a string, add my new text to the string (either 'prepend' or 'concatenate') and then store the string to the memofield.

Sample:

lcTmp=alltrim(filename.memofieldname)
lcTmp=lcNewString +chr(13)+chr(10)+lcTmp
replace filename.memofieldname with lcTmp

Hope this helps.

 

[weedz]

Maybe you could check the SET MEMOWIDTH setting that has a maximum of 8192.

HTH,
Weedz (Wietze Veld)
My private project:

http://www.crowncap.demon.nl\info\crwnbase

Download the CrownBase source code !!

 

[jonscott8]

311rage, theoretically there is no limit to the number of characters inserted into a memo field because it's data is stored in the seperate .FPT file. The only limit should be the available diskspace to store the .FPT file.

Your error message indicates you are using ADO/OLEDB to perform the insert. Using ADO2.5 & the VFP ODBC driver (ver. 6.01.8629.01), I've seen this behavior when attempting to assign 255+ characters to a memo field. I think the workaround I used was to send the memo field 254 characters at a time.

FWIW, you could try upgrading to the OLEDB provider included with VFP7 or the newer ODBC drivers. I've yet to upgrade, so I dont know if it will alleviate this problem. Jon Hawkins

 

[MayaRoselip]

Hey everyone,

I personally had this same problem when I rewrote a program to use SQL Inserts and Updates via ADO/OLEDB/ODBC.

The problem actually lies in the amount of characters allowed in an SQL statement via the VFP ODBC driver.

Try using a parameterized query. If you store the memo data in a variable, you can reference the variable in the INSERT or UPDATE with a question mark before it:

?lcParameter

If the variable is available when he SQL statement is prepared / executed then the contents of that variable will "passed-through" with the SQL Statement. This made it possible for me to insert binary data / files whithin the confines of an SQL statement (Up to 10MB even!). As you probably know, if you try to place the literal strings in an SQL statement you must scrub apostrophes and any other "invalid" characters or it will ruin your SQL statement.

Hope This Helps,

Maya Maya Roselip - True Data Storage Networks Inc. - New York City
Premier CA VAR, ARCserve Training Center, StorageTek Dealer
Maya.Roselip@TrueDataSAN.com

 

[jonscott8]

Maya - as I'm sure you are aware, in ADO, you do not use local variables to perform parameterized queries - you use ADO parameter objects or the parameter argument in the execute method of the command object. All my testing using ADO to perform SPT resulted in the 255 character limitation while inserting to a memo field. If yours has proved differently, I'd be interested in viewing it.

311rage - The following may prove useful.
HOWTO: Add Records with Memo Data Using the Visual FoxPro ODBC Driver

http://support.microsoft.com/support/kb/articles/Q208/2/08.ASP

Jon Hawkins