Repost: Values of location field gets truncated in a asp table

[Jack]

Since,
I have not got some desired advise, I am reposting this for some
asnwer/valuable suggestion. Thanks.

THE FOLLOWING IS A PART OF CODE FROM A ASP PAGE

<%
sql01 = "SELECT COUNT(*) AS reccount FROM Equipmenttbl "
sql01 = sql01 & "WHERE Equipmenttbl.GrantID = " & GrantID

'Response.Write sql01 & "<br>"
'Response.End

i = 0
sql01 = "SELECT equipmentTbl.* FROM EquipmentTbl "
sql01 = sql01 & "WHERE EquipmentTbl.GrantID = " & GrantID
response.write "SELECT SQL:" & sql01
'response.end
rstemp.open sql01
'set rstemp=conntemp.execute(sql01)
Do until rstemp.eof
i = i + 1
response.write "<TR>" & vbCRLF
strLink = "<TD WIDTH=1% ><INPUT TYPE='HIDDEN' NAME='EquipmentID_" & i &
"' VALUE=" & rstemp("EquipmentID") & "></TD>" & vbCRLF
response.write strLink & vbCRLF
if session("locked")<>"Y" then
strLink = "<TD WIDTH=1% ><A TITLE='Click here to delete line'
HREF='ConfirmDeleteEquipmentLine.asp?EquipmentID=" & rstemp("EquipmentID") &
"'>Delete</A></TD>" & vbCRLF
response.write strLink & vbCRLF
else
strLink = "<TD WIDTH=1% ></A></TD>" & vbCRLF
response.write strLink & vbCRLF
end if
strLink = "<TD WIDTH=10% ALIGN=LEFT><INPUT TYPE='text' SIZE=10
NAME='serialnum_"& i & "' VALUE='" & rstemp("SerialNumber") & "'></TD>" &
vbCRLF
response.write strLink & vbCRLF

strLink = "<TD WIDTH=40% ALIGN=LEFT><INPUT TYPE='text' SIZE=45
NAME='Desc_"& i & "' VALUE='" & rstemp("Description") & "'></TD>" & vbCRLF
response.write strLink & vbCRLF

strLink = "<TD WIDTH=10% ALIGN=LEFT><INPUT TYPE='text' SIZE=28
NAME='Location_" & i & "' VALUE='" & rstemp("Location") & "'></TD>" & vbCRLF
response.write strLink & vbCRLF


response.write rstemp("Location") & "<br>"

strLink = "<TD WIDTH=10% ALIGN=RIGHT><INPUT TYPE='text' SIZE=12
NAME='Cost_" & i & "' VALUE='" & rstemp("Cost") & "' style='text-align:
right;'></TD>" & vbCRLF
response.write strLink & vbCRLF

strLink = "<TD WIDTH=20% ALIGN=CENTER><INPUT TYPE='text' SIZE=1
NAME='Expmonth_" & i & "'VALUE='" & month(rstemp("DateAcquired")) & "'>/
<INPUT TYPE='text' SIZE=1 NAME='ExpDay_" & i & "'VALUE='" &
day(rstemp("DateAcquired")) & "'>/ <INPUT TYPE='text' SIZE=2 NAME='Expyear_"
& i & "'VALUE='" & year(rstemp("DateAcquired")) & "'></TD>" & vbCRLF
'***************
response.write strLink & vbCRLF

response.write "</TR>" & vbCRLF

totalcost=totalcost+rstemp("Cost")
'save the check box value and Financial Officers name. Should be the
same on
'all records as it will get updated each time the UPDATE button is
clicked.
'Check box and name are redundant but this is the way it was originally
set up
'Decided to go with the flow rather than trying to save these values in
the
'GrantTBL so they are not redundant.
ICertify = rstemp("ICertify")
FinancialOfficer = rstemp("FinancialOfficer")
rstemp.movenext
loop
rstemp.close
%>
</TR>


In the above code which is a input screen (as well as display screen) all
the equipment rows of a particular GrantId is captured
from the above recordset and is displayed in a table format by the above
loop. The problem is that for the values
of location for various rows, if there is a single quote in the row, then
only part of the value is captured in the
location column of this generated table.

I have added a response.write rstemp("Location") & "<br>" statement to see
the values that are retrieved
from the backend table corresponding to the locaion field.In the present
scenario the following was the result i.e. here we can see that the single
quotes
are retrieved as is. The results are the following:

Conference Room's Cabinet
Server Rooom
Don's Room
Server Room

However, in the asp generated table for the location column the values are
as follows:
Conference Room
Server Rooom
Don
Server Room

Thus all character including and after the single quote(') is getting
truncated.

I have no idea why this truncation is happening when the rows are being
dynamically formed in the asp table
but retrieves the whole value by issusing a response.write statement. And
what is the resolution for this problem so
that the location field in the generated asp table shows data without any
truncation.

Any help is appreciated.

Thanks in advance.

 

[Bob Barrows [MVP]]

Thus all character including and after the single quote(') is getting
truncated.

Use Server.HTMLEncode to encode the location values before writing them to
Response
Bob Barrows

 

[Jack]

Thanks for your advise Bob. I appreciate it. The statment that is creating
problem is:
strLink = "<TD WIDTH=10% ALIGN=LEFT><INPUT TYPE='text' SIZE=28
NAME='Location_" & i & "' VALUE='" & rstemp("Location") & "'></TD>" & vbCRLF
response.write strLink & vbCRLF


In the above statement, when I am writing Server.HTMLEncode(strLink), I am
still getting the truncated value in the location field. I do not know how to
handle it in the above code as it is not a direct
response.write(rstemp("Location")). Any further ideas? Regards.

 

[Paxton]

Thanks for your advise Bob. I appreciate it. The statment that is creating
problem is:
strLink = "<TD WIDTH=10% ALIGN=LEFT><INPUT TYPE='text' SIZE=28
NAME='Location_" & i & "' VALUE='" & rstemp("Location") & "'></TD>" & vbCRLF
response.write strLink & vbCRLF


In the above statement, when I am writing Server.HTMLEncode(strLink), I am
still getting the truncated value in the location field. I do not know how to
handle it in the above code as it is not a direct
response.write(rstemp("Location")). Any further ideas? Regards.

Try VALUE=""" & rstemp("Location") & """

That's 3 sets of double quotes either side of & rstemp("Location") &

/P.

 

[Bob Barrows [MVP]]

Thanks for your advise Bob. I appreciate it. The statment that is
creating problem is:
strLink = "<TD WIDTH=10% ALIGN=LEFT><INPUT TYPE='text' SIZE=28
NAME='Location_" & i & "' VALUE='" & rstemp("Location") & "'></TD>" &
vbCRLF response.write strLink & vbCRLF

My mistake: htmlencode does not do anything about apostrophes (not that you
shouldn't use it anyways). What you need to do is, if you want to use
apostrophes for your value's delimiter, you need to escape the apostrophes
within the value by doubling them up:

strLink = "<TD WIDTH=10% ALIGN=LEFT><INPUT TYPE='text' SIZE=28
NAME='Location_" & i & "' VALUE='" & _
Replace(rstemp("Location"),"'","''") & "'></TD>" & vbCRLF
response.write strLink & vbCRLF

or use real quotes for the value's delimiters:

strLink = "<TD WIDTH=10% ALIGN=LEFT><INPUT TYPE='text' SIZE=28
NAME='Location_" & i & "' VALUE=""" & rstemp("Location") & """></TD>" &
vbCRLF
response.write strLink & vbCRLF

But the latter will have a problem with a Location containing an embedded
quote (") character.

 

[Jack]

Hi Paxton,
I tried putting 3 sets of double quotes. Still it does not work. Still
waiting for some
answer. Thansks anyway.
Regards.

 

[Jack]

Thanks for the advise Bob. However, both the suggestions did not work. I
think there must be something else going on that is preventing the display of
full field value. I am going to look at the whole page tomorrow and give you
the result.
Best regards.

 

[Jack]

Hi Bob,
Sorry to get back so long. I finally had time to get back to my code and
applied the suggestions you have made. What I saw was that the """ quote
approach solved the problem after cleaning my code a bit. However, the
replace approach did not get me the desired result. Here still the truncation
is occurring. Thanks.

 

[Bob Barrows [MVP]]

I can't fix what i can't see ...

Hi Bob,
Sorry to get back so long. I finally had time to get back to my code
and applied the suggestions you have made. What I saw was that the
""" quote approach solved the problem after cleaning my code a bit.
However, the replace approach did not get me the desired result. Here
still the truncation is occurring. Thanks.

 

[Jack]

Hi Bob,
Here is the code for a small prototype where the replace concept is not
workiing.
Hope you would be able to catch the flaw. Since I got my problem resolved by
the alternative method, this is not a stumbling block to me. However, it
would be nice to know where the problem is with regards to trucation even
after using the replace approach. Regards.

Please note that I created a access table and populated it and then wrtoe a
small asp page to test the replace concept.

CREATE TABLE TEST1
(ID COUNTER,
NAME TEXT(50),
STORAGESPACEASSIGNED TEXT(50)
)


INSERT INTO test1 ( Name, StorageSpaceAssigned )
VALUES ("John Doe", "Kitchen Cabinet");

INSERT INTO test1 ( Name, StorageSpaceAssigned )
VALUES ("Jane Doe", "Apartment's Attic");

INSERT INTO test1 ( Name, StorageSpaceAssigned )
VALUES ("Sam Hugh", "Bedroom Cabinet");

INSERT INTO test1 ( Name, StorageSpaceAssigned )
VALUES ("Tom Jones", "Dining Room's Closet");

INSERT INTO test1 ( Name, StorageSpaceAssigned )
VALUES ("Erica James", "None");


ASP CODE TO TEST THAT TRUNCATION IS NOT HAPPENING WHEN WE USE THE REPLACE
METHOD

<% 'The following is code for connecting to the database
myDSN="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.mdb"



set CN=server.createobject("ADODB.Connection")
set rs=server.createobject("ADODB.Recordset")
set rstemp=server.createobject("ADODB.Recordset")

CN.Open myDSN

rstemp.ActiveConnection = CN
rs.ActiveConnection = CN
%>

<%

i = 0

sql01 = "SELECT * from tblTest "

rstemp.open sql01

Response.Write "<TABLE BORDER=1>"
Do while not rstemp.EOF
Response.Write "<TR><TD>" & rstemp("Name") & "&nbsp;</TD>"
Response.Write "<TD>" & rstemp("StorageSpaceAssigned") &
"&nbsp;</TD></TR>"
rstemp.MoveNext
Loop
Response.Write "</TABLE>"
rstemp.Close

'Response.End

rstemp.Open sql01




Do until rstemp.eof
i = i + 1
Response.Write "<TABLE>" & vbcrLf
response.write "<TR>" & vbCRLF
response.write "<TD WIDTH=1% ><INPUT TYPE='HIDDEN' NAME='ID_" & i &
"'VALUE=" & rstemp("ID") & "></TD>" & vbCRLF
response.write "<TD WIDTH=50% ALIGN=LEFT><INPUT TYPE='text' SIZE=20
NAME='name_" & i & "' VALUE='" & rstemp("Name") & "'></TD>" & vbCRLF
response.write "<TD WIDTH=49% ALIGN=LEFT><INPUT TYPE='text' SIZE=20
NAME='storagespaceassigned_" & i & "' VALUE='" &
Replace(rstemp("StorageSpaceAssigned"),"'","''") & "'></TD>" & vbCRLF
response.write "</TR>" & vbCRLF
rstemp.movenext
loop
response.write "</TABLE>" & vbcrlf
rstemp.close
%>

 

[Bob Barrows [MVP]]

Hi Bob,
Here is the code for a small prototype where the replace concept is
not workiing.
Hope you would be able to catch the flaw. Since I got my problem
resolved by the alternative method, this is not a stumbling block to
me. However, it would be nice to know where the problem is with
regards to trucation even after using the replace approach. Regards.

Please note that I created a access table and populated it and then
wrtoe a small asp page to test the replace concept.

Server.HTMLEncode does nothing to apostrophes. In order to encode an
apostrophe in an attribute value delimited (incorrectly) with single quotes,
you need to replace the apostrophe with "'" as in
Replace(rstemp("StorageSpaceAssigned"),"'","'")

You are better off using double-quotes for your attribute value delimiters:
this will enable you to use a single method, HTMLEncode, to handle your
character issues.

Bob Barrows

 

[Jack]

OK, got it. Thanks Bob.

Server.HTMLEncode does nothing to apostrophes. In order to encode an
apostrophe in an attribute value delimited (incorrectly) with single quotes,
you need to replace the apostrophe with "'" as in
Replace(rstemp("StorageSpaceAssigned"),"'","'")

You are better off using double-quotes for your attribute value delimiters:
this will enable you to use a single method, HTMLEncode, to handle your
character issues.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"