ADO returns full length strings....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ADO returns full length strings....

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-12 : 19:59:15

Using ADO like

Firstname = .Fields("Firstname").Value

is returning a string 'Sam' with length 100, blank filled.

QA says the row/column Firstname is of length 3.

Where am I getting the blank filled length of 100 and how do I make corrections??

Sam

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-12 : 20:11:01

Is the field a Char(100) ?
If so, chars are padded with spaces, you need to do a trim() in your code.

Either way, your info is a little vague to make any accurate suggestions

Damian

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-12 : 20:21:05

Strange. QA was returning Len(Firstname) as 3 for 'Sam'

But

SELECT Firstname+'end' from Mytable

would yeild

'Sam...100 spaces...end'

I've updated the columns to remove the blanks that the len function wouldn't find and it's cooking now.

Thanks Merkin,

Sam

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-12 : 20:26:38

Yeah, the len function in TSQL ignores leading and trailling whitespace. Datalength() would have returned a value of 100.

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-13 : 04:35:16

If you look in bol - always a good start if you think something strange is happening.

LEN (T-SQL)
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-13 : 16:34:53

Yes, BOL is great. I was more focused on the problem being embedded in ADO, since LEN was telling me what I wanted to see. Had I any doubt about LEN, BOL would have been the place to go.

Now datalength is interesting. It brought me back to an earlier post
[url]http://sqlteam.com/Forums/topic.asp?TOPIC_ID=21127[/url]

so I ran a QA command to get the field length that I had miscounted in that post.

PRINT DATALENGTH(PWDENCRYPT('HELLO'))
46

So the data type of the undocumented PWDEncrypt is BINARY (46). Nice to know.

Thanks Damian and Nigel.

Sam

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-13 : 18:59:42

Sam, in the future if you run into this you can do two things:

 
Firstname = Trim(.Fields("Firstname").Value)

OR in your stored proc RTrim() the field in question

Michael
(500 posts here I come!)

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources