ASP rsObj doing unwanted trimming from SQL Server

Hello, this is strange and has all of us here scratching our heads. I
am doing a simple test to return the value of a "Name" comumn in a SQL
Server 2000 table:
<%
' Open Connection to the database
set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=SQLOLEDB; Data Source=SQL; Initial
Catalog=TEST;"
strConnection = strConnection & "Persist Security Info=True; User
Id=sa; Password=xxxxxxxx"
objConn.Open strConnection

' Open record
strsql = "SELECT Name FROM tblEmailAddress2_NOTUSED"
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, objConn, 1, 2
If rs.recordcount > 0 then
rs.movefirst
Do While Not rs.EOF
response.write rs("Name")
rs.movenext
Loop
rs.close
Set rs = Nothing
objConn.Close
Set objConn = Nothing
%>

Let's say the name in the table is "John Smith", it still returns
to my ASP as "John Smith". Is there some default behaviour or something
to do with the way the recordset object is handling this, or is there
some IIS setting or....? Any help would be appreciated. I need to NOT
have this value trimmed.

Thanks,
KP

Re: ASP rsObj doing unwanted trimming from SQL Server

The browser will only display a single space no matter how many are in the
HTML code.

Try

Response.contenttype="text/plain"

or

response.write Replace(rs("Name"), " ", "&nbsp;")
--
--Mark Schupp


"Kermit Piper" <socalbruce@hotmail.com> wrote in message
news:1130437970.500905.277530@g49g2000cwa.googlegroups.com.. .
> Hello, this is strange and has all of us here scratching our heads. I
> am doing a simple test to return the value of a "Name" comumn in a SQL
> Server 2000 table:
> <%
> ' Open Connection to the database
> set objConn = Server.CreateObject("ADODB.Connection")
> strConnection = "Provider=SQLOLEDB; Data Source=SQL; Initial
> Catalog=TEST;"
> strConnection = strConnection & "Persist Security Info=True; User
> Id=sa; Password=xxxxxxxx"
> objConn.Open strConnection
>
> ' Open record
> strsql = "SELECT Name FROM tblEmailAddress2_NOTUSED"
> set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open strsql, objConn, 1, 2
> If rs.recordcount > 0 then
> rs.movefirst
> Do While Not rs.EOF
> response.write rs("Name")
> rs.movenext
> Loop
> rs.close
> Set rs = Nothing
> objConn.Close
> Set objConn = Nothing
> %>
>
> Let's say the name in the table is "John Smith", it still returns
> to my ASP as "John Smith". Is there some default behaviour or something
> to do with the way the recordset object is handling this, or is there
> some IIS setting or....? Any help would be appreciated. I need to NOT
> have this value trimmed.
>
> Thanks,
> KP
>

Re: ASP rsObj doing unwanted trimming from SQL Server

Kermit Piper wrote:
> Hello, this is strange and has all of us here scratching our heads. I
> am doing a simple test to return the value of a "Name" comumn in a SQL
> Server 2000 table:
> <%
> ' Open Connection to the database
> set objConn = Server.CreateObject("ADODB.Connection")
> strConnection = "Provider=SQLOLEDB; Data Source=SQL; Initial
> Catalog=TEST;"
> strConnection = strConnection & "Persist Security Info=True; User
> Id=sa;

Ooh ... don't use the sa account for your application code. Create a
limited-permissions account and use that instead. Guard that sa account as
if your job depended on its security. It probably does ...

> Password=xxxxxxxx"

Man, I'm hoping this is a faked password ....

> objConn.Open strConnection
>
> ' Open record
> strsql = "SELECT Name FROM tblEmailAddress2_NOTUSED"

"Name" is a reserved keyword. You would probably be better off changing the
name of the field to something more descriptive (EmailName?). If you can't,
then you should be safe and enclose the word in brackets [] when using it in
your sql statements. It doesn't seem to hurt anything right now, but in a
future version of sql server, it might.

> set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open strsql, objConn, 1, 2
> If rs.recordcount > 0 then
> rs.movefirst

Why bother with recordcount and movefirst? Just use an efficient forwardonly
cursor:

set rs=objConn.Execute(strsql,,1)
> Do While Not rs.EOF
> response.write rs("Name")
> rs.movenext
> Loop
> rs.close
> Set rs = Nothing
> objConn.Close
> Set objConn = Nothing
> %>
>
> Let's say the name in the table is "John Smith", it still
> returns to my ASP as "John Smith". Is there some default behaviour or
> something to do with the way the recordset object is handling this,
> or is there some IIS setting or....? Any help would be appreciated. I
> need to NOT have this value trimmed.
>

Well, you could enclose it in a PRE tag:
response.write "<PRE>" & rs(0) & "</PRE>"

Or you could replace the spaces with non-breaking spaces (&nbsp;):
response.write replace(rs(0)," ","&nbsp;")

AFAIK, there is no global setting to modify this behavior at the server
level: this behavior is being done by the browser when it renders the html.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: ASP rsObj doing unwanted trimming from SQL Server

It's known as &nbsp;, or lack of in this case (a space is a space is a space
as far as the HTML processor, unless the PRE tag, or &nbsp; is used to tell
it otherwise)

In the case of your example, your expecting;

John Smith

Which the browser is seeing as;

John<space * 7>Smith

Instead of;

John<&nbsp; * 7>Smith

To correct this, you can replace the space character with &nbsp; or wrap the
response.write on the field, in the <pre> and </pre> tag

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Kermit Piper" <socalbruce@hotmail.com> wrote in message
news:1130437970.500905.277530@g49g2000cwa.googlegroups.com.. .
> Hello, this is strange and has all of us here scratching our heads. I
> am doing a simple test to return the value of a "Name" comumn in a SQL
> Server 2000 table:
> <%
> ' Open Connection to the database
> set objConn = Server.CreateObject("ADODB.Connection")
> strConnection = "Provider=SQLOLEDB; Data Source=SQL; Initial
> Catalog=TEST;"
> strConnection = strConnection & "Persist Security Info=True; User
> Id=sa; Password=xxxxxxxx"
> objConn.Open strConnection
>
> ' Open record
> strsql = "SELECT Name FROM tblEmailAddress2_NOTUSED"
> set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open strsql, objConn, 1, 2
> If rs.recordcount > 0 then
> rs.movefirst
> Do While Not rs.EOF
> response.write rs("Name")
> rs.movenext
> Loop
> rs.close
> Set rs = Nothing
> objConn.Close
> Set objConn = Nothing
> %>
>
> Let's say the name in the table is "John Smith", it still returns
> to my ASP as "John Smith". Is there some default behaviour or something
> to do with the way the recordset object is handling this, or is there
> some IIS setting or....? Any help would be appreciated. I need to NOT
> have this value trimmed.
>
> Thanks,
> KP
>