Hi all.
I dunno if this has been addressed anywhere, (I'm sure it probably has, but I haven't been able to find it...)
When I started with my first FoxPro project last year, I ran into the problem of SQL Server not liking strings with apostrophies. I didn't know how to deal with it, so I told the users of my application just not to use apostrophies anywhere (there are only about 10 of them ;-)
Well, this was all fine and dandy, (they didn't complain about it too much), until I started my next project. It involved a web-interfase to my SQL Server database, to which my original FoxPro application is linked.
Needless to say, I can't ask users on the web *not* to use apostrophies, so I had to come up with a solution on both ends, because if web users are inserting apostrophies, the FoxPro application users are going to have to be able to read them...
I may have been making a bigger deal out of this than I needed to, but for a novice this was a large stumbling block. One of the things that took me a while to figure out was that using updatable remote views, the apostrophies were handled internally, but in the instances where I use SQL-SELECT statements to modify tables, I had problems.
This is a function I came up with that I run every string through just before saving the string fields to a SQL table via a SQLEXEC statement:
**********************************
FUNCTION FixTick(InText)
* Function will check for any occurances of an apostrophe (') and add another.
* SQL necesitates the double apostrophe in order to read a string containing one.
apoCount = OCCURS("'", InText)
FOR x = 1 to apoCount
&& For each occurance of an apostrophe, add another next to it.
tmpPlace = AT("'", InText, (x*2-1)) && Skip apostrophes already accounted for
InText = LEFT(InText, tmpPlace) + RIGHT(TRIM(InText), (LEN(InText) - tmpPlace + 1))
ENDFOR
RETURN InText
**********************************
The purpose of posting this was two-fold:
1.) If anyone can see problems with this function, or has suggestions on simplifying it, I'd love to hear.
2.) If this helps any user in the same boat I was in last year, I'll feel a little better knowing I've given something back to the UT in exchange for all the fantastic help I've received here in the last year.
Cheers!