Weird Characters in SQL View in XP not in win2000

[madhatter2002]

Hi Again Tipsters,

I have this quite weird experience that I have encountered using VFP6 and SQL.

First let me tell the history, I had VFP 6 installed previously in Windows 2000, where I used an ODBC connection to linked to SQL to fetch and insert data, works fine and I can read the data, no missing part of the data.

However, When I had my pc upgraded to XP and all applications has been reinstalled, I then recreated the previous setup.

I installed VFP6, created the ODBC link (same procedure as before) only problem now is, when I view the data, I always see this weird character at the end of each record. But if I view the data using excel same odbc connection, i can view the complete data without the missing part. When I say the missing part, lets assume that the data is 10 characters long, using the VFP view with SQL I can only see 9 + the weird character at the end. This thing wasnt there before.

Anybody who has encountered the same?? how did you manage to go around this weirdness?

 

[jimoo]

I am putting on my thinking cap, but if I recall correctly I remember hearing about that before and it turned out to be something silly - like VFP did not handle the SQL varchar correctly. It occurs on some pcs, regardless of the OS, etc. and not others so it probably has something to do with the ODBC.

I think the work around - in the instance I mentioned was to have the field changed to char.

I could be way off on this one, but check to see if it is a varchar. If so, you may consider changing it to char.

If anyone else can provide insight - please do.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[craigsboyd]

Is this perhaps what you are running into:

VFP has a separate issue with nChar fields where the last character is cut off and a CHR(0) appears in its place. The workaround to this one that I have gotten to work is to expand the field, using field mapping within a view, to one more character than the field length. Obviously, you then need to program around this excess, insuring that you are not accepting input you can't save.

From the following:

http://www.tedroche.com/Present/2000/DEV311Doc.htm

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[craigsboyd]

Or perhaps this?

http://support.microsoft.com/default.aspx?scid=kb;EN-US;279135

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[madhatter2002]

Hi Slighthaze, yup! exactly, if I look at it correctly the last character is chr(0). how do you do field mapping within a view??

How about those not in a view but contained in a create remote view command??

 

[Mike Lewis]

madhatter2002,

You might try getting the same data using SQL pass-through (SPT). If the CHR(0) is no longer there, that would confirm Slighthaze's diagnosis. You could then consider switching to SPT rather than using remote views.

How about those not in a view but contained in a create remote view command??

Creating the view with CREATE won't make any difference. The end-product is the same -- it's just the way you build them that is different.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[rgbean]

Also, a "visual" solution is to pick a font that displays chr(0) as a space or null string rather than the 'squared zero'. In fact I've seen cases were it appeared and disappeared on the same system - due to the user constantly changing the Themes setup!

Rick

 

[madhatter2002]

Thanks all, I was able to find the solution. I recall that in my previous installation I did complete all the necessary Service Packs for Visual Studio, and this was the missing part in my current setup.

Everything is ok now after installing the Service Pack 5 for Visual Studio.