Professional Web Applications Themes

White Space Problem - Microsoft SQL / MS SQL Server

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

  1. #1

    Default 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


    Carlo Guest

  2. #2

    Default Re: White Space Problem

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

    "Carlo Razzeto" <com> wrote in message
    news:%phx.gbl... 
    Password=Oper64Hammer; 
    get 


    Marina Guest

  3. #3

    Default Re: White Space Problem

    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" <com> wrote in message
    news:%phx.gbl... 
    Password=Oper64Hammer; 
    get 


    William Guest

  4. #4

    Default Re: White Space Problem

    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" <com> wrote in message
    news:phx.gbl... 
    and 
    rights. [/ref]
    this [/ref]
    database 
    > Password=Oper64Hammer; 
    > get [/ref]
    string. [/ref]
    compare [/ref]
    this [/ref]
    store [/ref]
    to 
    >
    >[/ref]


    Carlo Guest

  5. #5

    Default Re: White Space Problem

    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" <com> wrote in message
    news:phx.gbl... 
    > varchar? [/ref]
    > this [/ref]
    > database 
    > > Password=Oper64Hammer; [/ref][/ref]
    I [/ref]
    > string. [/ref]
    > compare [/ref]
    > this [/ref]
    > store [/ref]
    > to 
    > >
    > >[/ref]
    >
    >[/ref]


    Marina Guest

  6. #6

    Default Re: White Space Problem

    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!
    Robert Guest

  7. #7

    Default Re: White Space Problem

    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" <hotmail.com> wrote in message
    news:%phx.gbl... 
    padded 
    has 
    to 
    > > varchar? [/ref][/ref]
    group [/ref][/ref]
    (most 
    > > database [/ref][/ref]
    WHERE [/ref][/ref]
    set 
    > > string. 
    > > compare 
    > > this 
    > > store [/ref][/ref]
    need 
    > >
    > >[/ref]
    >
    >[/ref]


    William Guest

  8. #8

    Default Re: White Space Problem

    Thanks guys, that really helped!

    Carlo

    "William (Bill) Vaughn" <com> wrote in message
    news:phx.gbl... 
    fields 
    made 
    rights. 


    Carlo Guest

Similar Threads

  1. white space
    By ganzor in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: February 24th, 03:49 PM
  2. White Space cfc and css
    By jcrip in forum Macromedia ColdFusion
    Replies: 7
    Last Post: August 8th, 05:30 PM
  3. Getting rid of white space...
    By Marcos Rebelo in forum PERL Beginners
    Replies: 9
    Last Post: September 8th, 06:57 PM
  4. Printing White Space Problem
    By Aaron Mizell in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 2
    Last Post: August 27th, 01:25 PM
  5. problem with white space/border when importing image or movie
    By buzz01 webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 5
    Last Post: July 11th, 04:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139