sqldatareader padding varchar fields with extra white space

[dchristjohn]

I am currently developing a small windows application using Visual
Basic via Visual Studio 2005. My database resides on a SQL 2000 server.

I have a table with three fields:

id (int, Not Null)
lname (varchar(30), Not Null)
fname (varchar(30), Not Null)

I have one record in the table as follows:

1 Smith Joe

When I run the following query in query analyzer I see that the length
of the fname field is 3:

select *, len(fname) from tblmytable

I created a form in Visual Studio 2005 using Visual Basic with 1 button
on it and a text field for a last name. That button runs the query
using an SQL data reader and then popups up a few message boxes. The
code is as follows:

' Create, open connection to database.
Dim cnFalcon As New SqlConnection(gconSqlConnection)
cnFalcon.Open()

' Define query, assign to strSql.
Dim strSql As String = "Select fname, len(fname) as flen,lname from
tblUsers where username like '" & txtLastName.Text & "'"

' Create new action command.
Dim cmGetUser As New SqlCommand(strSql, cnFalcon)

Dim dr As SqlDataReader =
cmGetUser.ExecuteReaderCommandBehavior.SingleRow)

Do While dr.Read()
MessageBox.Show(Len(dr.Item("fname")).ToString)
MessageBox.Show(dr.Item("flen"))
Loop

dr.Close()

cnFalcon.Close()

When I run my application and provide a last name I see two message
boxes.

The first message box shows the length of the raw fname field as being
30, which is incorrect.

The second message box shows the length of the flen alias as being 3,
which is correct.

Does anyone have some insight as to why the sqldatareader is padding
the data that is returned from my varchar fields with extra spaces?

Best regards,

Locker

P.S. I know I can use RTRIM in the query or TrimEnd in the VB code to
work around this but I feel that I should not have to do this. The
Sqldatareader query should return the data exactly as it's returned if
I run the query directly via Query Analyzer.

 

[Izzy]

Try this:

Do While dr.Read()
MsgBox(dr("fname").ToString.Length, MsgBoxStyle.OkOnly)
Loop

Also is more efficient if you reference the column by ordinal rather
then name, like this:

Do While dr.Read()
MsgBox(dr(0).ToString.Length, MsgBoxStyle.OkOnly)
Loop

 

[dchristjohn]

I added a third message box using your format. This new message box
also returns 30 as the length.

Just to give a bit of additional information, it's not just that the
length function returns 30. There are actually 27 spaces being added to
the field so if I display the fname + lname from the query you can
physically see a ton of extra whitespaces on the end of the first name.

Thanks,

Dan

 

[Izzy]

Oh wait I just reread your original post.

If you can use RTRIM in the SQL query and it resolves the problem then
the spaces are in the table itself.

I always use DataReaders and never experience this behavior.