White Space Problem

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

What data type is the column set to in sql server? Is it a char or varchar?

"Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
news:%23jkDnKyaDHA.2024@TK2MSFTNGP12.phx.gbl...
> 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
>
>

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.
__________________________________

"Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
news:%23jkDnKyaDHA.2024@TK2MSFTNGP12.phx.gbl...
> 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
>
>

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

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:OUKTRhyaDHA.1816@TK2MSFTNGP09.phx.gbl...
> 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.
> __________________________________
>
> "Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
> news:%23jkDnKyaDHA.2024@TK2MSFTNGP12.phx.gbl...
> > 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
> >
> >
>
>

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.

"Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
news:OA6YO6yaDHA.2328@TK2MSFTNGP09.phx.gbl...
> Table is setup as follows
> UserData
> UserName char(15)
> Password char(15)
> UserLevel tinyint
>
> Carlo
>
> "Marina" <zlatkinam@nospam.hotmail.com> wrote in message
> news:ei%23R6eyaDHA.4028@tk2msftngp13.phx.gbl...
> > What data type is the column set to in sql server? Is it a char or
> varchar?
> >
> > "Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
> > news:%23jkDnKyaDHA.2024@TK2MSFTNGP12.phx.gbl...
> > > 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 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.
__________________________________

"Marina" <zlatkinam@nospam.hotmail.com> wrote in message
news:%231BsTWzaDHA.1816@TK2MSFTNGP09.phx.gbl...
> 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.
>
> "Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
> news:OA6YO6yaDHA.2328@TK2MSFTNGP09.phx.gbl...
> > Table is setup as follows
> > UserData
> > UserName char(15)
> > Password char(15)
> > UserLevel tinyint
> >
> > Carlo
> >
> > "Marina" <zlatkinam@nospam.hotmail.com> wrote in message
> > news:ei%23R6eyaDHA.4028@tk2msftngp13.phx.gbl...
> > > What data type is the column set to in sql server? Is it a char or
> > varchar?
> > >
> > > "Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
> > > news:%23jkDnKyaDHA.2024@TK2MSFTNGP12.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Thanks guys, that really helped!

Carlo

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:eIsko60aDHA.2672@tk2msftngp13.phx.gbl...
> 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.
> __________________________________