VB.NET TrimEnd() not working

Question

I am having an issue that I can't seem to solve! Specifically with the TrimEnd() function.

Background: Using Visual Studio 2010 Express, I'm building a VB.NET application that pulls data from a built-in SQL Database using LINQ to SQL and puts it into a datagrid view. I'm then taking a value from a the datagrid and putting it into a variable somewhere else.

Problem: If a field in the database has a maximum length of say 25 characters, but this one specifically is only 10, I'm getting 15 extra spaces at the end it. So when I pull the cell value into a variable, it's "variable ".

I've been trying to get rid of the spaces at the end. The only method that works is the .Replace method, but I don't want to get rid of any spaces that are in the middle, only the extra at the end. Here's some stuff that I've tried after doing some research:

1.

Dim sChar as String = " "
myVariable = myVariable.TrimEnd(sChar)

2.

myVariable = myVariable.Trim()

3.

Dim charsToTrim As Char() = {" "c}
myVariable = myVariable.TrimEnd(charsToTrim)

None of these have worked. As I said the myVariable = myVariable.Replace(" ", "") works, but I don't want to get rid of ALL the spaces, just the ones on the end and Trim isn't working. If anyone has any ideas I'd like to hear them.

Update Resolution Steps:

So it was suggested to update the columns to nvarchar. This allows me to enter data into a field and SQL not filling up the left over characters with extra spaces. However once I made that change my LINQ to SQL stopped working.

Public Sub LoadClients()
Dim query = From a In db.Clients
        Select a.Account_Number,
        a.Client_Type,
        a.Client_Name,
        a.Client_Address1,
        a.Client_Address2,
        a.Client_City,
        a.Client_State,
        a.Client_ZIP,
        a.Client_Contact,
        a.Client_Phone

    dgvClients.DataSource = query

Getting InvalidCastException was unhandled - Specific cast is not valid.

Also I should note, this is simply a work around to the problem of the extra spaces being put in the SQL Database. I imagine this is because SQL always keeps storage for the maximum number of characters regardless of how many are being used. I don't really know SQL all that well but if there's a way to get rid of the extra spaces directly in SQL rather than doing this work around, I'm open to that as well. — user2783652, Sep 16 '13 at 11:05
If you define the column as VARCHAR(25) you won`t get the extra spaces. — Heslacher, Sep 16 '13 at 11:07
Can you please post a sample of your input data (myVariable) before trimming? Trim() tends to work pretty well. — varocarbas, Sep 16 '13 at 11:07
Maybe you have an invisible (non-space) character at the end of the string. — adrianm, Sep 16 '13 at 11:23
If for the `1.` of your examples the IDE didn`t complain you should set `OPTION STRICT ON` — Heslacher, Sep 16 '13 at 11:23

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

The code you have posted should work, especially this:

myVariable = myVariable.TrimEnd() ' note the missing parameters

It will remove all white-spaces from the end which includes even tab characters or line breaks.

So I assume that the problem is the type of the column. If it's char or nchar it will always have a length of 25 even if it's shorter. Use varchar or nvarchar then.

See Stack Overflow question What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

edited May 23 '17 at 12:30

Community

1
1

answered Sep 16 '13 at 11:15

Tim Schmelter

450,073
74
686
939

So switching them to nvarchar doesn't add the extra spaces in a field, I'll have to reinput all the data back in there but it solves the problem its not a big deal. However I'm now getting an error when I try to load the query into the datagridview. Getting "Specified cast is not valid." `code` Public Sub LoadData() Dim query = From a In db.Client Select a.Account_Number, a.Client_Type, a.Client_Name, a.Client_Address1, a.Client_Address2, dgvClients.DataSource = query – user2783652 Sep 16 '13 at 11:49
@user2783652: So it was really the data-type, what was it? `CHAR` and `NCHAR` are fixed length types which also "consume" all of the space even if it's unused. So you need them only if the size is always the same. According to the new problem. Post the code in your question. Remember to accept an answer if it solves the problem :) – Tim Schmelter Sep 16 '13 at 11:54
I'll post the new code in my question, but I won't really know if it solves the problem until I'm able to get the data to load in the datagrid. – user2783652 Sep 16 '13 at 11:58
@user2783652: So have you refreshed the linq-to-sql model in your project after you've changed the type of the column? – Tim Schmelter Sep 16 '13 at 12:02
I didn't for any of my other tables, and the other tables load just fine and the query is built exactly the same.3 – user2783652 Sep 16 '13 at 12:03
The only difference is I didn't modify any of the actual data in any of the other tables – user2783652 Sep 16 '13 at 12:03
Well for giggles I removed the reference in the datacontext and then put it back for the table I was having trouble with, and now its all there. Thanks very much! – user2783652 Sep 16 '13 at 12:06
Believe I marked your checkmark for solved. Thanks for your help! – user2783652 Sep 16 '13 at 12:08

VB.NET TrimEnd() not working

1 Answers1