Solved

VB.net VFPOLEDB ALLTRIM Issue!

Posted on 2014-03-12
4
713 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
  • 2
4 Comments
 
LVL 41

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

777 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