Solved

VB.net VFPOLEDB ALLTRIM Issue!

Posted on 2014-03-12
4
814 Views
Last Modified: 2014-03-13
OK, so I'm using the VFPOLEDB Provider to connect to a FoxPro database...however when I use my select statement to pull data the ALLTRIM(Field) is NOT triming off the spaces??

conn.Open()
sSQLCommand = "SELECT alltrim(vec_code) AS vec_code, alltrim(tdaf_code) as tdaf_code, alltrim(returndesc) as returndesc  FROM tdbank_return_codes ORDER BY vec_code"
Dim command As New OleDb.OleDbCommand(sSQLCommand, conn)

Why?

Thanks,
Mark
0
Comment
Question by:smithmrk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 39923943
Character strings in DBF structure have fixed length so the trailing spaces are always there in strings shorter than maximum field length...

You have two options:
1) Trim the spaces at VB side - it will work for sure
2) Retrieve data as Varchar string (update the maximum string length from 50 to whatever you need up to 254 chars):

SELECT CAST(alltrim(vec_code) AS varchar(50)) AS vec_code, CAST(alltrim(tdaf_code) AS varchar(50)) as tdaf_code, CAST(alltrim(returndesc) AS varchar(50)) as returndesc  FROM ...

I am just not sure if it will work because I did not test this possibility yet....
0
 

Author Closing Comment

by:smithmrk
ID: 39923983
BINGO!!!

CAST(ALLTRIM(Field)as varchar(50)) WORKED!!!!

Thanks,
Mark
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39925997
Using Select expression AS fieldalias VFP needs to infer the resulting field type for the new field and in cases this is just infered from the result type and length of the first result record, in other cases, like ALLTRIM(), VFP knows the max length can be the original field length and uses that, so ALLTRIM() seems not to work, as spaces are added, instead of resulting in a varchar field.

It's even more complex with expressions working on several fields, therefore CAST is recommended to specify the result field type you want. That's what works best to specify what you get without letting VFP infer or guess wrong.

Bye, Olaf.
0
 

Author Comment

by:smithmrk
ID: 39926200
Thanks Olaf!

That helps explain whats going on...it was driving me crazy for half the day yesterday.
I appreciate it!

Mark
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question