Discussion:
White Space Problem
(too old to reply)
Carlo Razzeto
2003-08-25 16:05:03 UTC
Permalink
First of all, sorry about cross posting but I wasn't sure which group this
question was more appropriate for...

I am having a problem with Microsoft SQL Server or SqlDataReader (most
likely the former) apparently appending white space to the end of database
results... The code I have looks like:

Dim ConnectionString = "Data Source=(local); User=sa; Password=Oper64Hammer;
Initial Catalog=my_work"
Dim Query = "SELECT UserName, PassWord, UserLevel FROM UserData WHERE
UserName = '" + user + "'"
Dim SQLConnection As New SqlConnection(ConnectionString)
SQLConnection.Open()
Dim Command As New SqlCommand(Query, SQLConnection)
Dim Reader As SqlDataReader = Command.ExecuteReader()

For some reason when I read from the password column of the result set I get
the password with with one black space appended to the end of the string.
This forced me to write store my password in a string then use the
string.trim method to remove the white space so I could accurately compare
the database result to the password entered by the user. What causes this
problem? And is there any way to correct it? I would hate to have to store
all my database results as strings so I can trim them when ever I need to
compare results to user input. Thanks for any help,

Carlo
Marina
2003-08-25 16:41:24 UTC
Permalink
What data type is the column set to in sql server? Is it a char or varchar?
Post by Carlo Razzeto
First of all, sorry about cross posting but I wasn't sure which group this
question was more appropriate for...
I am having a problem with Microsoft SQL Server or SqlDataReader (most
likely the former) apparently appending white space to the end of database
Dim ConnectionString = "Data Source=(local); User=sa;
Password=Oper64Hammer;
Post by Carlo Razzeto
Initial Catalog=my_work"
Dim Query = "SELECT UserName, PassWord, UserLevel FROM UserData WHERE
UserName = '" + user + "'"
Dim SQLConnection As New SqlConnection(ConnectionString)
SQLConnection.Open()
Dim Command As New SqlCommand(Query, SQLConnection)
Dim Reader As SqlDataReader = Command.ExecuteReader()
For some reason when I read from the password column of the result set I get
the password with with one black space appended to the end of the string.
This forced me to write store my password in a string then use the
string.trim method to remove the white space so I could accurately compare
the database result to the password entered by the user. What causes this
problem? And is there any way to correct it? I would hate to have to store
all my database results as strings so I can trim them when ever I need to
compare results to user input. Thanks for any help,
Carlo
Marina
2003-08-25 18:20:32 UTC
Permalink
When you define a columns as char(15), that means there will always be 15
characters in that field. If you put anything smaller in, it will be padded
with spaces such so the field is always 15 in length. So if your string has
12 character, there will be 3 spaces afterwards.

If you do not want this behavior, use a varchar(15) type. This holds up to
15 characters, but does not pad with spaces.
Table is setup as follows
UserData
UserName char(15)
Password char(15)
UserLevel tinyint
Carlo
Post by Marina
What data type is the column set to in sql server? Is it a char or
varchar?
Post by Marina
Post by Carlo Razzeto
First of all, sorry about cross posting but I wasn't sure which group
this
Post by Marina
Post by Carlo Razzeto
question was more appropriate for...
I am having a problem with Microsoft SQL Server or SqlDataReader (most
likely the former) apparently appending white space to the end of
database
Post by Marina
Post by Carlo Razzeto
Dim ConnectionString = "Data Source=(local); User=sa;
Password=Oper64Hammer;
Post by Carlo Razzeto
Initial Catalog=my_work"
Dim Query = "SELECT UserName, PassWord, UserLevel FROM UserData WHERE
UserName = '" + user + "'"
Dim SQLConnection As New SqlConnection(ConnectionString)
SQLConnection.Open()
Dim Command As New SqlCommand(Query, SQLConnection)
Dim Reader As SqlDataReader = Command.ExecuteReader()
For some reason when I read from the password column of the result set
I
Post by Marina
get
Post by Carlo Razzeto
the password with with one black space appended to the end of the
string.
Post by Marina
Post by Carlo Razzeto
This forced me to write store my password in a string then use the
string.trim method to remove the white space so I could accurately
compare
Post by Marina
Post by Carlo Razzeto
the database result to the password entered by the user. What causes
this
Post by Marina
Post by Carlo Razzeto
problem? And is there any way to correct it? I would hate to have to
store
Post by Marina
Post by Carlo Razzeto
all my database results as strings so I can trim them when ever I need
to
Post by Marina
Post by Carlo Razzeto
compare results to user input. Thanks for any help,
Carlo
Robert Taylor
2003-08-25 18:54:02 UTC
Permalink
Carlo,

Marina is correct, in that CHAR always pads for the unused spaces.
VARCHAR does not. VARCHAR takes up a little bit more space (I believe
16 byes if I'm not mistaken), but sure makes a lot of string work
easier.

If you have to keep the CHAR datatype, then use RTRIM in your SQL
statement, i.e., "select rtrim(username) as username,rtrim(password) as
password, rtrim(userlevel) as userlevel from userData where
rtrim(username)='search_value'". Depending upon what you are doing with
the results and the front end application, you may need to trim each of
your recordset columns and criteria as well, because the padding may
impact your displayed results.

If you need more information, check out BOL for 'SET ANSI_PADDING'

Robert


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
William (Bill) Vaughn
2003-08-25 21:22:01 UTC
Permalink
Marina is right. We rarely use Char anymore for anything except those fields
where we know the length will ALWAYS be a fixed number of characters. This
is handy for part numbers or somesuch but anytime you're working with a
field that varies in length, use a VarChar. In the "olden days", we often
looked for small ways to help reduce the size of the DB and using CHAR made
more sense back then. Nowadays it's more trouble than it's worth to use
CHAR.

hth
--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Post by Marina
When you define a columns as char(15), that means there will always be 15
characters in that field. If you put anything smaller in, it will be padded
with spaces such so the field is always 15 in length. So if your string has
12 character, there will be 3 spaces afterwards.
If you do not want this behavior, use a varchar(15) type. This holds up to
15 characters, but does not pad with spaces.
Table is setup as follows
UserData
UserName char(15)
Password char(15)
UserLevel tinyint
Carlo
Post by Marina
What data type is the column set to in sql server? Is it a char or
varchar?
Post by Marina
Post by Carlo Razzeto
First of all, sorry about cross posting but I wasn't sure which group
this
Post by Marina
Post by Carlo Razzeto
question was more appropriate for...
I am having a problem with Microsoft SQL Server or SqlDataReader (most
likely the former) apparently appending white space to the end of
database
Post by Marina
Post by Carlo Razzeto
Dim ConnectionString = "Data Source=(local); User=sa;
Password=Oper64Hammer;
Post by Carlo Razzeto
Initial Catalog=my_work"
Dim Query = "SELECT UserName, PassWord, UserLevel FROM UserData WHERE
UserName = '" + user + "'"
Dim SQLConnection As New SqlConnection(ConnectionString)
SQLConnection.Open()
Dim Command As New SqlCommand(Query, SQLConnection)
Dim Reader As SqlDataReader = Command.ExecuteReader()
For some reason when I read from the password column of the result set
I
Post by Marina
get
Post by Carlo Razzeto
the password with with one black space appended to the end of the
string.
Post by Marina
Post by Carlo Razzeto
This forced me to write store my password in a string then use the
string.trim method to remove the white space so I could accurately
compare
Post by Marina
Post by Carlo Razzeto
the database result to the password entered by the user. What causes
this
Post by Marina
Post by Carlo Razzeto
problem? And is there any way to correct it? I would hate to have to
store
Post by Marina
Post by Carlo Razzeto
all my database results as strings so I can trim them when ever I need
to
Post by Marina
Post by Carlo Razzeto
compare results to user input. Thanks for any help,
Carlo
Carlo Razzeto
2003-08-25 23:17:30 UTC
Permalink
Thanks guys, that really helped!

Carlo
Post by William (Bill) Vaughn
Marina is right. We rarely use Char anymore for anything except those fields
where we know the length will ALWAYS be a fixed number of characters. This
is handy for part numbers or somesuch but anytime you're working with a
field that varies in length, use a VarChar. In the "olden days", we often
looked for small ways to help reduce the size of the DB and using CHAR made
more sense back then. Nowadays it's more trouble than it's worth to use
CHAR.
hth
--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Damien
2003-10-16 17:39:08 UTC
Permalink
OK, lets say you have a table with a field that is
VARCHAR, but for some reason (probably the page that adds
data to that field) some of the fields in that column
still contain trailing spaces. How do you trim the
trailing spaces when doing a select so that all you get is
the non-space data?

Damien
-----Original Message-----
Marina is right. We rarely use Char anymore for anything
except those fields
where we know the length will ALWAYS be a fixed number of
characters. This
is handy for part numbers or somesuch but anytime you're
working with a
field that varies in length, use a VarChar. In the "olden
days", we often
looked for small ways to help reduce the size of the DB
and using CHAR made
more sense back then. Nowadays it's more trouble than
it's worth to use
CHAR.
hth
--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can
benefit.
This posting is provided "AS IS" with no warranties, and
confers no rights.
__________________________________
Post by Marina
When you define a columns as char(15), that means there
will always be 15
Post by Marina
characters in that field. If you put anything smaller
in, it will be
padded
Post by Marina
with spaces such so the field is always 15 in length.
So if your string
has
Post by Marina
12 character, there will be 3 spaces afterwards.
If you do not want this behavior, use a varchar(15)
type. This holds up
to
Post by Marina
15 characters, but does not pad with spaces.
Table is setup as follows
UserData
UserName char(15)
Password char(15)
UserLevel tinyint
Carlo
Post by Marina
What data type is the column set to in sql server?
Is it a char or
Post by Marina
varchar?
Post by Marina
Post by Carlo Razzeto
First of all, sorry about cross posting but I
wasn't sure which
group
Post by Marina
this
Post by Marina
Post by Carlo Razzeto
question was more appropriate for...
I am having a problem with Microsoft SQL Server
or SqlDataReader
(most
Post by Marina
Post by Marina
Post by Carlo Razzeto
likely the former) apparently appending white
space to the end of
Post by Marina
database
Post by Marina
Post by Carlo Razzeto
Dim ConnectionString = "Data Source=(local);
User=sa;
Post by Marina
Post by Marina
Password=Oper64Hammer;
Post by Carlo Razzeto
Initial Catalog=my_work"
Dim Query = "SELECT UserName, PassWord, UserLevel
FROM UserData
WHERE
Post by Marina
Post by Marina
Post by Carlo Razzeto
UserName = '" + user + "'"
Dim SQLConnection As New SqlConnection
(ConnectionString)
Post by Marina
Post by Marina
Post by Carlo Razzeto
SQLConnection.Open()
Dim Command As New SqlCommand(Query,
SQLConnection)
Post by Marina
Post by Marina
Post by Carlo Razzeto
Dim Reader As SqlDataReader =
Command.ExecuteReader()
Post by Marina
Post by Marina
Post by Carlo Razzeto
For some reason when I read from the password
column of the result
set
Post by Marina
I
Post by Marina
get
Post by Carlo Razzeto
the password with with one black space appended
to the end of the
Post by Marina
string.
Post by Marina
Post by Carlo Razzeto
This forced me to write store my password in a
string then use the
Post by Marina
Post by Marina
Post by Carlo Razzeto
string.trim method to remove the white space so I
could accurately
Post by Marina
compare
Post by Marina
Post by Carlo Razzeto
the database result to the password entered by
the user. What causes
Post by Marina
this
Post by Marina
Post by Carlo Razzeto
problem? And is there any way to correct it? I
would hate to have to
Post by Marina
store
Post by Marina
Post by Carlo Razzeto
all my database results as strings so I can trim
them when ever I
need
Post by Marina
to
Post by Marina
Post by Carlo Razzeto
compare results to user input. Thanks for any
help,
Post by Marina
Post by Marina
Post by Carlo Razzeto
Carlo
.
Robert Taylor
2003-10-16 18:02:58 UTC
Permalink
Take a look at Books Online for RTRIM and LTRIM.

HTH,

Robert

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

William (Bill) Vaughn
2003-08-25 16:47:33 UTC
Permalink
How are the columns defined in your UserData table?
Now that we know the SA password, all we need to know is ...
As a rule you should rarely (if ever) connect with the SA login. Keep the
password to yourself and create roles/permissions/logins to develop with and
use permissions to grant access.
--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Post by Carlo Razzeto
First of all, sorry about cross posting but I wasn't sure which group this
question was more appropriate for...
I am having a problem with Microsoft SQL Server or SqlDataReader (most
likely the former) apparently appending white space to the end of database
Dim ConnectionString = "Data Source=(local); User=sa;
Password=Oper64Hammer;
Post by Carlo Razzeto
Initial Catalog=my_work"
Dim Query = "SELECT UserName, PassWord, UserLevel FROM UserData WHERE
UserName = '" + user + "'"
Dim SQLConnection As New SqlConnection(ConnectionString)
SQLConnection.Open()
Dim Command As New SqlCommand(Query, SQLConnection)
Dim Reader As SqlDataReader = Command.ExecuteReader()
For some reason when I read from the password column of the result set I get
the password with with one black space appended to the end of the string.
This forced me to write store my password in a string then use the
string.trim method to remove the white space so I could accurately compare
the database result to the password entered by the user. What causes this
problem? And is there any way to correct it? I would hate to have to store
all my database results as strings so I can trim them when ever I need to
compare results to user input. Thanks for any help,
Carlo
Carlo Razzeto
2003-08-25 17:32:08 UTC
Permalink
Thanks for the advice... This is actually not an internet accessable site
right now, it's behind my broadband routers firewall... I would be a little
more careful if this was going to be a "real" web site on the internet but
since I'm just getting familiar with .Net (in this case VB of course) I was
just being... well... lazy...

Carlo
Post by William (Bill) Vaughn
How are the columns defined in your UserData table?
Now that we know the SA password, all we need to know is ...
As a rule you should rarely (if ever) connect with the SA login. Keep the
password to yourself and create roles/permissions/logins to develop with and
use permissions to grant access.
--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Post by Carlo Razzeto
First of all, sorry about cross posting but I wasn't sure which group this
question was more appropriate for...
I am having a problem with Microsoft SQL Server or SqlDataReader (most
likely the former) apparently appending white space to the end of database
Dim ConnectionString = "Data Source=(local); User=sa;
Password=Oper64Hammer;
Post by Carlo Razzeto
Initial Catalog=my_work"
Dim Query = "SELECT UserName, PassWord, UserLevel FROM UserData WHERE
UserName = '" + user + "'"
Dim SQLConnection As New SqlConnection(ConnectionString)
SQLConnection.Open()
Dim Command As New SqlCommand(Query, SQLConnection)
Dim Reader As SqlDataReader = Command.ExecuteReader()
For some reason when I read from the password column of the result set I
get
Post by Carlo Razzeto
the password with with one black space appended to the end of the string.
This forced me to write store my password in a string then use the
string.trim method to remove the white space so I could accurately compare
the database result to the password entered by the user. What causes this
problem? And is there any way to correct it? I would hate to have to store
all my database results as strings so I can trim them when ever I need to
compare results to user input. Thanks for any help,
Carlo
Loading...