Why is my string value accumulating empty spaces when inserting into SQL database?

Question

I have an extensive form inserting into a SQL database and then from an admin page I request all data and have it insert back into the form on the admin page. During the original insertion into the database, this specific dropdownlist (just like the others) is inserting the selected data. But when I go to the database, just this particular cell is getting 3 to 4 empty white spaces after the selected value is inserted. This is causing the boolean comparison to respond with false when attempting to insert back into the admin's form. i.e. ("Primary" == "Primary ") = false. So the dropdownlist is not updated.

What would be causing the SQL row to add these empty spaces? The code is identical for all the HTML dropdownlists with its code behind. And the structure of the SQL database seems identical all the way through as well.

p.s. To undo the problem I'm attempting to use .Trim() in the code behind. But this is not making a difference. It seems the SQL database adding the white space upon insertion.

what the code? what's the type of the column? what's the SQL? — Rune FS
– Rune FS, Commented Sep 8, 2011 at 20:50
"It seems the SQL database adding the white space upon insertion." Test that by executing correct SQL directly through your dbms interface instead of passing the query through code. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Sep 9, 2011 at 3:59
WHAT database and which version?? SQL is just the Structured Query Language - a language used by many database systems - SQL is NOT a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using.... — marc_s
– marc_s, Commented Sep 9, 2011 at 5:12

smoak · Accepted Answer · 2011-09-08 20:42:32Z

33

Does your column thats adding whitespace happen to be a CHAR or NCHAR instead of VARCHAR or NVARCHAR? For example if your columns datatype is CHAR(10) and you insert a "Hello" into it, there will always be 10 characters in that column so 5 whitespace characters will get added after "Hello". However, if you use VARCHAR(10), this won't happen.

edited Sep 8, 2011 at 20:42

answered Sep 8, 2011 at 20:40

smoak

15.1k6 gold badges35 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Oded Over a year ago

Or NCHAR instead of NVARCHAR.

iDevJunkie Over a year ago

Umm, @smoak that's the interesting thing. It's all done as varchar(50)..I noticed that I did at least one as varchar(100). So none of them should be adding the extra chars... But you know, that's funny. Cause it IS adding chars up to 10. If 'Primary' is selected, it inserts as 'Primary ' (with 3 empty spaces) if 'Spouse' is selected it adds 4 empty spaces...all adding to 10. I wonder if somewhere along the way I had it as char and now the database is just not updating properly..

iDevJunkie Over a year ago

Okie. Thanks for your help, Bro. The deal was that my LINQ to SQL thingy wasn't updated with what the SQL database field was set to. I'm assuming that I originally, accidentally, set it to char 10 and then changed it. But LINQ wasn't updated. It's fixed now. :)

Collectives™ on Stack Overflow

Why is my string value accumulating empty spaces when inserting into SQL database?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related