>Thank you Dennis, I will take a look into issue "cannot update memo fields set to a zero-length string".

Thanks--I really appreciate it.

>I believe other two problems are not related to VFP because they are connection string specific. Are you using the latest MDAC version? Try to upgrade if not.

I think so. As I said, I have XP Pro SP1 which supposedly has the latest MDAC stuff, and some of the .DLLs in C;\program files\common files\...\system\msadc are 2.71. But I installed the 2.71 mdac_typ.exe just for bangs, and no change.

And, as I said, the same connection works fine in someone else's software, at least with memo fields > 255 characters.

From your other message:

>In the mean time, you may use a CursorAdapter.BeforeUpdate event as a workaround:

FUNCTION BeforeUpdate (cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd)
	*?cUpdateInsertCmd
	LOCAL cAlias
	cAlias=ALIAS()
	SELECT (this.Alias)
	if(LEN(ProdDescription)=0)
		cUpdateInsertCmd=STRTRAN(cUpdateInsertCmd,"?"+this.Alias+".ProdDescription","''",1,1,1)
		*?cUpdateInsertCmd
	ENDIF 
	SELECT (cAlias)
ENDFUNC 

>It is possible to write generic code to handle all empty character and memo fields.

I was just replacing zero-length memo fields with 1 space before tableupdate() to work around this, but this a real solution. Thanks.

BTW, empty character fields are not a problem. Just memo.

>You can use similar approach for other issues.

> In worst case, you can always call ADODB.Command object directly in BeforeUpdate event and then set cUpdateInsertCmd to empty string (this tells CursorAdapter to do nothing for the record).


Without first reading up on ADODB.COMMAND, I tried calling ADODB.COMMAND directly as you suggested, with this code near the bottom of what you did, but before SELECT (cAlias)

this.commandobj.commandtext="UPDATE sfproducts SET PRODDESCRIPTION='" + ;
 sfproducts.proddescription + "' WHERE PRODID='" + ;
 OLDVAL'prodid','sfproducts') + "'"
		 
this.commandobj.execute()	
		
cUpdateInsertCmd = ""

A memo field length >255 characters now works, and .null. would probably work if I knew how to make a .null. constant for Access

Looks to me like this problem is also in VFP 8.

I put the latest test prg at ftp://hosenose.com/cabug

Questions:

*When I call ADODB.COMMAND directly, is ConversionFunc ignored, or is it processed before calling BeforeUpdate?

*Is there an easy way to get the name of the field being updated in BeforeUpdate()? Is getting everything between the first =? and the next " " going to work in all instances?

*What happens if the data contains a single quote? Is there any other quote character that will work as with VFP? Or do I just ave to convert a single quote to a back quote?

*If you know offhand how to send .null. constant to Access, please tell me. Otherwise I'll figure it out.

Would it help if I install the .MDB on my server with a fixed IP address and set up a user for you?

Please advise how to proceed. I'm so close to having this project finished I can taste victory, thanks to you! Let me know how I can help.

Dennis

Update:

This also works for > 255 characters and empty, but don't call with .null.:

If you set the command to ='quoted constant' instead of =?alias.fieldname, and let VFP call ADODB.COMMAND, all is well.

	FUNCTION BeforeUpdate (cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd)

		?cUpdateInsertCmd
		LOCAL cAlias, FldVal
		cAlias=ALIAS()
		SELECT (this.Alias)
		

*	this fixes empty string and >255 characters
		FldVal = TRIM(sfproducts.proddescription)

		cUpdateInsertCmd=STRTRAN(cUpdateInsertCmd, ;
			"?"+this.Alias+".ProdDescription","'" + FldVal + "'", ;
			1,1,1)
			
		?cUpdateInsertCmd

*	this does too	
#IF .f.		
		this.commandobj.commandtext="UPDATE sfproducts SET PRODDESCRIPTION='" + ;
		 TRIM(sfproducts.proddescription) + "' WHERE PRODID='" + ;
		 OLDVAL('prodid','sfproducts') + "'"
		 
		this.commandobj.execute()	
		
		cUpdateInsertCmd = ""
#endif

		
		
		SELECT (cAlias)
				
	ENDFUNC