Inserting Text Into SQL 2005 Database Padded With Spaces

I have a Detailsview with Insert and Update options connected to a SQL 2005 table with templated textboxes for input. The textboxes have maxlength set to the number of characters in each respective field in the SQL 2005 table. When text is inserted it gets padded with spaces if all the field length is not used. When you try to edit the field the cursor does not move because of the padded spaces. The End key must be pressed to find the end of the string and the padded space removed before adding edited text. I am working in VB.net. If I check the field in SQL Studio Express is shows the text I typed plus blank space for the remainder of the field.

My question is how can I add text to the textboxes without the padded spaces being added when the maxlength of the field is not used?

if you look at the datatype of the data columns in the sql database, you will most likely find that it it set tochar.
the char datatype automatically pads with spaces to completely fill the allocated space.

If you prefer not to get these spaces, then you would want to use thevarchar datatype when setting up your table in sql server.
the varchar datatype will not add the padding.

Note: if you change the datatype of a column in an existing database, you will likely need to trim the existing data as it will still contain the padding. however, new data added to a varchar column will not get the padding.

|||

Thank you that worked like a charm. Three questions before I close this out:

1) How does the type nchar work? It comes up always as the default? Which type is the best?

2. Should I expect any suprises in my code where in some places I have embedded Rtrim statements?

3. When binding a variable to the table is there any format { } string that can be used to achieve the no padding result? I saw in a forum some place about the F parameter to reduce spaces but I did not know how to implement it?

|||

1) nchar is for storing fixed length unicode character data.
char will pad the data if created with ansi_padding set on. nchar always pads. http://msdn2.microsoft.com/en-us/library/ms175055.aspx
Which is best depends on your needs. As you've noticed, char/nchar might require to you to trim off whitespace.
For most text columns, I prefer varchar (or nvarchar for data i need to store as unicode).

2) Trimming the data with RTrim is unaffected by your use of char/varchar - other than it becomes unnecessary if your data is not padded. Note that the String type contains a TrimEnd method (among others) which i prefer over the trim methods from the visualbasic library.

3) I'm not aware of a standard format code that would trim the padding.

Here's an interesting thread:http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=7978