VisualFoxPro STRTRAN() Working in a Strange Way

I'm trying to display the users image when they select their netname, however when I attempt to find their image I get an error message saying, as an example;

 Could not find file 'S:\Picture\Chris               .jpg'.

As you can probably tell there is a load of spaces, so I tried this where every space would be replaced in netname with an "a";

 string dbfQuery = "SELECT em_pplid, em_name, STRTRAN(em_netname, ' ', 'a'), em_surname FROM employs WHERE em_netname NOT LIKE ''";

And of course I started getting a new error message like so;

Could not find file 'S:\Picture\Chrisaaaaaaaaaaaaaaa.jpg'.

Immediately you would think that changing STRTRAN(em_netname, ' ', 'a') to STRTRAN(em_netname, ' ', '') would remove all spaces. Whilst this does work to a certain extent, firstly if there is more than one Chris, for example "ChrisB" and "ChrisC", they both now become "Chris".

To add to this some spaces are not removed, for example I still get an error saying;

Could not find file 'S:\Picture\YenT .jpg'.

i.e there is still one space left in some circumstances.

Is there any reason for this? Am I using STRTRAN incorrectly? One thing I thought it could be is that instead of spaces there are other hidden, unprintable characters however wouldn't replacing them with "a" leave a space?

Solution

Do not use StrTran() or any other Functions there. At first sight the appropriate function to use is Trim() or Rtrim(). BUT, VFP doesn't really have support for varying character sizes and that causes the field size to be set to the first record's content it finds (thus if it finds Chris first, then any name longer than Chris is truncated to 5 characters. And any data with less than 5 still have trailing spaces).

And as per strtran(), it wouldn't just replace the trailing space but any space characters in the string.

So instead of using any functions first do a plain select:

string dbfQuery = @"SELECT em_pplid, em_name, em_netname, em_surname 
    FROM employs 
    WHERE em_netname NOT LIKE ''";

Then process the value in your C# code. For example if you are using a reader:

var pictureFileName = string.Format(@"s:\Picture\{0}.jpg", 
  ((string)reader["em_netname"]).Trim());