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

> 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
>
>
>
> dchristj***@gmail.com wrote:
> > 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.

> 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 wrote:
> > 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
> >
> >
> >
> > dchristj***@gmail.com wrote:
> > > 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.