Trim returned record in Classic ASP

try this:

<%=Left( rs("Comments", 50 ))%>

or better yet, if you want to indicate there is more to the comment after the first 50 chars, you could do:

<%
if Len( rs("comments") ) > 50 then
     Response.Write Left( rs("comments"), 50 ) & "....."
else
     Response.Write rs("comments")
end if

Select allOpen in new window

Hi Big Monty, thanks for the quick response.

When I tried this:
<%=Left( rs("Comments", 50 ))%>
The error message was:
Microsoft VBScript runtime error '800a01c2'
Wrong number of arguments or invalid property assignment: 'rs'

When I tried the if statement, only the ...... was displayed

ooops, slight typo, try this:

<%=Left( rs("Comments"), 50 ) %>

This didn't return anything

then rs("comments") is blank. you can do a test such as below to verify:

<%
if rs("comments") = "" then
     Response.Write "no comment available"
elseif Len( rs("comments") ) > 50 then
     Response.Write Left( rs("comments"), 50 ) & "....."
else
     Response.Write rs("comments")
end if
%>

Select allOpen in new window

The problem with the simple solution BM provided is  you can cut off a word.  

This solution looks for the first word break prior to the 50 char count

comment = left(rs("Comments"),50)
	x = InStrRev(comment," ")
	comment = left(comment,x)

Select allOpen in new window

And this solution goes one step more and checks to see if there is a word break 9 characters after the 50. If there is, then output the full word.  Otherwise, output the word break prior to the 50.

'*** First check to see if there is a word break close to the 50 mark'

nextSpace=instr(50,rs("Comments")," ")
if nextSpace < 60 then
	comment = left(rs("Comments"),nextSpace) 
else   '** otherwise find the first word break prior to the 50 char mark'


	comment = left(rs("Comments"),50)
	x = InStrRev(comment," ")
	comment = left(comment,x)

end if

response.write comment

Select allOpen in new window

Big Monty:
This is my actual code. When I try this it returns only the .........

<%
if rs("ItemDescription") = "" then
     Response.Write "no comment available"
elseif Len( rs("ItemDescription") ) > 50 then
     Response.Write Left( rs("ItemDescription"), 50 ) & "....."
else
     Response.Write rs("ItemDescription")
end if
%>

Padas, I'm trying your solution now; I just have to customize it.

there isn't a "problem" with my code, it works exactly as the OP requested. Please don't imply things unless something about it doesn't work. Regardless if there is a space at the end of the string is irrelevant....

if you just do a simple

Response.Write rs("itemDescription"), do you get text written out to the screen?

Padas:
I tried the following:
<%
nextSpace=instr(50,rs("ItemDescription")," ")
if nextSpace < 60 then
      comment = left(rs("ItemDescription"),nextSpace)
else   '** otherwise find the first word break prior to the 50 char mark'


      comment = left(rs("ItemDescription"),50)
      x = InStrRev(comment," ")
      comment = left(comment,x)

end if

response.write comment
%>

When the record was "Boat for sale" only the the word Boat was displayed.

Big Monty,
When I do Response.Write rs("itemDescription")
the full text of the record is displayed

it may be worth throwing the comment into a variable:

<%
dim itemDesc : itemDesc = rs("itemDescription")

if itemDesc = "" then
     Response.Write "no comment available"
elseif Len( itemDesc ) > 50 then
     Response.Write Left( itemDesc, 50 ) & "....."
else
     Response.Write itemDesc
end if
%>

Select allOpen in new window

I don't mind if the last words are trimmed, because I will place a "Read More" link after the returned record.

did you try this suggestion?

Yes, I'm still playing with it. When I leave it like this:
<%
dim itemDesc : itemDesc = rs("itemDescription")

if itemDesc = "" then
     Response.Write "no comment available"
elseif Len( itemDesc ) > 50 then
     Response.Write Left( itemDesc, 50 ) & "....."
else
     Response.Write itemDesc
end if
%>

It returns only......

When I change 50 to a smaller number it won't display anything

can you post the full code of the page? or at least the part where you open the connection, set the sql, and open the recordset?

'*** First check to see if there is a word break close to the 50 mark'

nextSpace=instr(50,rs("Comments")," ")
if nextSpace < 60 then
	comment = left(rs("Comments"),50) 
else   '** otherwise find the first word break prior to the 50 char mark'


	comment = left(rs("Comments"),50)
	x = InStrRev(comment," ")
	comment = left(comment,x)

end if

response.write comment

Select allOpen in new window

this was my test that worked

theTEST = "this is a boatthis this is a boatthis this is a boatthis this is a boatthis this is a boatthis this is a boatthis this is a boatthis this is a boatthis this is a boatthis this is a boatthis this is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boatthis is a boat"
'*** First check to see if there is a word break close to the 50 mark'

nextSpace = instr(50,theTEST," ")
if nextSpace < 60 then
	comment = left(theTEST,50) 
else   '** otherwise find the first word break prior to the 50 char mark'


	comment = left(theTEST,50)
	x = InStrRev(comment," ")
	comment = left(comment,x)

end if

response.write comment

Select allOpen in new window

I have to run to a short meeting now, but here's the code. I'll be back in 20 minutes. Sorry about this....

<%
if request.querystring("sort")<>"" then
   sort=request.querystring("sort")
else
   sort="ItemDate desc"
end if
set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3  ' adUseClient


rs.Open "SELECT * FROM ItemsSFO WHERE Status='A' ORDER BY " & sort & " " & request.querystring("order"), Conn

do until rs.EOF

EmpNum = rs("ItemName")

rs.MoveNext
loop

rs.PageSize = 100
intPageCount = rs.PageCount

Select Case Request("Action")
	case "<<"
		intpage = 1
	case "<"
		intpage = Request("intpage")-1
		if intpage < 1 then intpage = 1
	case ">"
		intpage = Request("intpage")+1
		if intpage > intPageCount then intpage = IntPageCount
	Case ">>"
		intpage = intPageCount
	case else
		intpage = 1
end select
%>

Select allOpen in new window

I don't see anything in the code pertaining to the itemDescription being written out to the screen. do you expect multiple records to be returned from your sql statement? if so, EmpNum is ONLY going to have the very last item name in the recordset.

A few pointers to clean up your code:

1) always specify your column names in your sql statements. using "select *" is highly inefficient
2) don't use "rs.Open" to select data from your database, unless you mean to move around a lot in the recordset and/or update the recordset. Instead, use something like:

       set rs = conn.Execute( sql )

can you please post the entire page? Be sure to take out any sensitive data before posting the code

I'm back....
Most of the code is irrelevant, but this is the whole thing. I made some changes you suggested in your last post. I would really appreciate your comments on how to make the page more effective. I didn't know how to replace the "rs.Open" part.

<!-- #include virtual="/SFO/Community/includes/Authentication.asp" -->
<%
if request.querystring("sort")<>"" then
   sort=request.querystring("sort")
else
   sort="ItemDate desc"
end if
set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3  ' adUseClient


rs.Open "SELECT ID, Price, ItemName, ItemDescription, Emp_Id, ItemDate, First_Name, Last_Name FROM ItemsSFO WHERE Status='A' ORDER BY " & sort & " " & request.querystring("order"), Conn

do until rs.EOF

myRecord = rs("ItemName")

rs.MoveNext
loop

rs.PageSize = 100
intPageCount = rs.PageCount

Select Case Request("Action")
	case "<<"
		intpage = 1
	case "<"
		intpage = Request("intpage")-1
		if intpage < 1 then intpage = 1
	case ">"
		intpage = Request("intpage")+1
		if intpage > intPageCount then intpage = IntPageCount
	Case ">>"
		intpage = intPageCount
	case else
		intpage = 1
end select
%>
<%
' technocurve arc 3 asp vb mv block1/3 start
Dim moColor1, moColor2, moColor
moColor1 = "#fffff0"
moColor2 = "#D6E3E6"
moColor3 = "#EEE2BB"
moColor = moColor1

' technocurve arc 3 asp vb mv block1/3 start
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
  "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Add New Item</title>
<link rel="stylesheet" href="/EWR/Scripts/stilus.css" type="text/css" />

</head>

<body background="/EWR/images/bg.jpg">

<table align="center" width="1000" border="0" cellpadding="0" cellspacing="0">
<tr><td colspan="3">
<!-- #include virtual="/SFO/includes/SFOheader.inc" -->
</td></tr>
<tr><td width="18" valign="top"><img src="/EWR/images/left_side.png" width="18" height="301" alt="Management" /></td>
<td id="bluegrad" rowspan="2" valign="top">
<br />
<table border="0" align="center"><tr><td width="130" height="320" valign="top">
<!-- #include virtual="/SFO/includes/SFOmenu.inc" -->
</td><td width="830" rowspan="2" valign="top">
<!-- Content Starts Here  -->

<table align="center"><tr>
<td><table cellpadding="0" class="border"><tr style="background: url(/EWR/images/background_silver.jpg)"><td><a href="Inbox.asp"><b>&nbsp;Inbox&nbsp;</b></a></td><td><a href="Inbox.asp"><img src="/EWR/images/Inbox.gif" border="0" alt="Read" /></a></td></tr></table></td>
<td><img src="/EWR/images/blank.gif" width="70" height="1" alt="" /></td>
<td align="center" nowrap>

<center><big><b>All Postings</b></big></center>

</td><td><img src="/EWR/images/blank.gif" width="70" height="1" alt="" /></td>
<td><table cellpadding="0" class="border">
  <tr style="background: url('/EWR/images/background_silver.jpg'); "><td nowrap><a href="SentItems.asp" onmouseover="window.status='Sent Items';return true;" onmouseout="window.status='';return true;"><b>Sent Items</b></a></td><td><a href="SentItems.asp" onmouseover="window.status='Sent Items';return true;" onmouseout="window.status='';return true;"><img src="/EWR/images/SentItems.gif" border="0" alt="Read" /></a></td></tr></table></td>
</tr></table>

<%if myRecord = "" then%><table align="center"><tr><td align="center"><br /><br /><font color="#cc0000" size="+1">No postings have been activated yet.<br />Please allow 24 hours for your posting to become active.</font><br /><br /></td></tr></table><%else%>

<br />
<table align="center" bgcolor="#ffffff" class="border">
<tr>
<td id="silver" align="center">
<%if request.querystring("order") = "DESC" then%>
<a href="All.asp?sort=ItemName&order=ASC" onMouseOver="footnoteShow(this, '1');" onMouseOut="footnoteHide();"><b style="font-size:80%">Category</b></a>
<%else%>
<a href="All.asp?sort=ItemName&order=DESC" onMouseOver="footnoteShow(this, '1a');" onMouseOut="footnoteHide();"><b style="font-size:80%">Category</b></a>
<%end if%>
</td>
<td id="silver" align="center">
<%if request.querystring("order") = "DESC" then%>
<a href="All.asp?sort=ItemName&order=ASC" onMouseOver="footnoteShow(this, '2');" onMouseOut="footnoteHide();"><b style="font-size:80%">Price</b></a>
<%else%>
<a href="All.asp?sort=ItemName&order=DESC" onMouseOver="footnoteShow(this, '2a');" onMouseOut="footnoteHide();"><b style="font-size:80%">Price</b></a>
<%end if%>
</td>
<td id="silver" align="center">
<%if request.querystring("order") = "DESC" then%>
<a href="All.asp?sort=ItemDescription&order=ASC" onMouseOver="footnoteShow(this, '3');" onMouseOut="footnoteHide();"><b style="font-size:80%">Item Description</b></a>
<%else%>
<a href="All.asp?sort=ItemDescription&order=DESC" onMouseOver="footnoteShow(this, '3a');" onMouseOut="footnoteHide();"><b style="font-size:80%">Item Description</b></a>
<%end if%>
</td>
<td id="silver" align="center">
<%if request.querystring("order") = "DESC" then%>
<a href="All.asp?sort=ItemDate&order=ASC" onMouseOver="footnoteShow(this, '4');" onMouseOut="footnoteHide();"><b style="font-size:80%">Post Date</b></a>
<%else%>
<a href="All.asp?sort=ItemDate&order=DESC" onMouseOver="footnoteShow(this, '4a');" onMouseOut="footnoteHide();"><b style="font-size:80%">Post Date</b></a>
<%end if%>
</td>
<td id="silver" align="center">
<%if request.querystring("order") = "DESC" then%>
<a href="All.asp?sort=ItemDate&order=ASC" onMouseOver="footnoteShow(this, '5');" onMouseOut="footnoteHide();"><b style="font-size:80%">Posted by</b></a>
<%else%>
<a href="All.asp?sort=ItemDate&order=DESC" onMouseOver="footnoteShow(this, '5a');" onMouseOut="footnoteHide();"><b style="font-size:80%">Posted by</b></a>
<%end if%>
</td>
<td bgcolor="#b0c4de" align="center"><b style="font-size:80%">View</b></td>

</tr>
<%
rs.AbsolutePage = intPage
For intRecord = 1 To rs.PageSize
%>
<tr <%
' technocurve arc 3 asp vb mv block2/3 start
Response.Write(" style='background-color:" & moColor & "' onMouseOver='this.style.backgroundColor=" & chr(34) & moColor3 & chr(34) & "' onMouseOut='this.style.backgroundColor=" & chr(34) & moColor & chr(34) & "'")
' technocurve arc 3 asp vb mv block2/3 start
%>>
<td align="left" nowrap class="border" valign="top">
<%If (rs("Emp_Id")) = Emp_Id Then%><a href='Edit.asp?ID=<%=rs("ID")%>'><%Else%><a href='Details.asp?ID=<%=rs("ID")%>'><%End If%>
<%=rs("ItemName")%></a>

</td>
<td align="right" nowrap class="border" valign="top"><%If (rs("Emp_Id")) = Emp_Id Then%><a href='Edit.asp?ID=<%=rs("ID")%>'><%Else%><a href='Details.asp?ID=<%=rs("ID")%>'><%End If%><%=rs("Price")%></a></td>
<td align="left" class="border"><%If (rs("Emp_Id")) = Emp_Id Then%><a href='Edit.asp?ID=<%=rs("ID")%>'><%Else%><a href='Details.asp?ID=<%=rs("ID")%>'><%End If%>

<%=rs("ItemDescription")%>


</a></td>
<td align="center" nowrap class="border" valign="top"><%If (rs("Emp_Id")) = Emp_Id Then%><a href='Edit.asp?ID=<%=rs("ID")%>'><%Else%><a href='Details.asp?ID=<%=rs("ID")%>'><%End If%><%=rs("ItemDate")%></a></td>
<td align="left" class="border" valign="top"><%If (rs("Emp_Id")) = Emp_Id Then%><a href='Edit.asp?ID=<%=rs("ID")%>'><%Else%><a href='Details.asp?ID=<%=rs("ID")%>'><%End If%><%=rs("First_Name")%>&nbsp;<%=rs("Last_Name")%></a></td>
<td align="center" class="border" valign="top"><%If (rs("Emp_Id")) = Emp_Id Then%><a href='Edit.asp?ID=<%=rs("ID")%>'><%Else%><a href='Details.asp?ID=<%=rs("ID")%>'><%End If%><img src="images/view_icon.gif" border="0" alt="View Details"></a></td>




</tr>
<%
 ' technocurve arc 3 asp vb mv block3/3 start
 if moColor = moColor1 then
 	moColor = moColor2
 else
 	moColor = moColor1
 end if
 ' technocurve arc 3 asp vb mv block3/3 start
%>
<%
	rs.MoveNext
If rs.EOF Then Exit For

Next

rs.Close
set rs = Nothing

%>
</table>



<center>
<form name="MovePage" action="" method="post">
<input type="hidden" name="intpage" value="<%=intpage%>">
<input type="submit" name="action" value="<<">
<input type="submit" name="action" value="<">
<input type="submit" name="action" value=">">
<input type="submit" name="action" value=">>">
Page: <%=Intpage & " of " & intpagecount%>
</form>
</center>
<%end if%>


<br />

<table align="center" class="border" cellspacing="0"><tr>
<td align="center" width="140" class="kint" onmouseover="this.className='bent'" onmouseout="this.className='kint'" onClick="window.location='Post.asp'" style="cursor:hand;"><a href="Post.asp"><b style="font-size:80%">Post Your Ad</b></a></td>
<td align="center" width="140" class="kint" onmouseover="this.className='bent'" onmouseout="this.className='kint'" onClick="window.location='AllCategories.asp'" style="cursor:hand;"><a href="AllCategories.asp"><b style="font-size:80%">View All Categories</b></a></td>
<td align="center" width="140" class="kint" onmouseover="this.className='bent'" onmouseout="this.className='kint'" onClick="window.location='MyPostings.asp'" style="cursor:hand;"><a href="MyPostings.asp"><b style="font-size:80%">View My Postings</b></a></td>
<td align="center" width="140" class="kint" onmouseover="this.className='bent'" onmouseout="this.className='kint'" onClick="window.location='/SFO/Community/Admin/AddUser.asp'" style="cursor:hand;"><a href="/SFO/Community/Admin/AddUser.asp"><b style="font-size:80%">Administration</b></a></td>
</tr></table>


<!-- Content Ends Here  -->
</td></tr>
<tr><td valign="top" align="center">

<a href="javascript: history.go(-1)"><img src="/EWR/images/back.gif" alt="Back to Previous Page" width="50" border="0" /></a>



</td></tr>
</table>

<img src="/EWR/images/blank.gif" width="963" height="1" alt="" />
</td>
<td width="19" valign="top"><img src="/EWR/images/right_side.png" width="19" height="301" alt="Management" /></td></tr>

<tr>
<td valign="top"><img src="/EWR/images/left_side_scalable.png" alt="Management" width="18" height="650" /></td>
<td width="19" valign="top"><img src="/EWR/images/right_side_scalable.png" alt="Management" width="19" height="650" /></td>
</tr>
<tr><td colspan="3" align="center" valign="top"><img src="/EWR/images/bottom_line.png" width="977" height="11" alt="Management" /></td></tr>
</table>
<br /><center><a href="/EWR/LoggedInUser.asp">You are logged in as <%If ISNULL(Pref_First_Name) OR TRIM(Pref_First_Name) = "" Then Response.Write(First_Name) Else Response.Write (Pref_First_Name) End If%>&nbsp;<%=Last_Name%>&nbsp;(<%=Request.ServerVariables("Logon_User")%>)</a></center>
<br />
<font color="ffffff">
<span id="footnote-1">Sort by Item Name Ascending</span>
<span id="footnote-1a">Sort by Item Name Descending</span>
<span id="footnote-2">Sort by Price Ascending</span>
<span id="footnote-2a">Sort by Price Descending</span>
<span id="footnote-3">Sort by Item Description Ascending</span>
<span id="footnote-3a">Sort by Item Description Descending</span>
<span id="footnote-4">Sort by Timestamp Ascending</span>
<span id="footnote-4a">Sort by Timestamp Descending</span>
<span id="footnote-5">Sort by Posted By Ascending</span>
<span id="footnote-5a">Sort by Posted By Descending</span>
</font>
</body>
</html>

Select allOpen in new window

since you're using paging, leave in the rs.Open

try replacing the following code:

do until rs.EOF

myRecord = rs("ItemName")

rs.MoveNext
loop

with

if not rs.BOF and not rs.EOF then
      myRecord = rs("ItemName")
else
       Response.Write "record not found"
       Response.End
end if

i think your loop here is causing the recordset to skip all of the data, thus not displaying any data

I've replaced it, and it's working fine

so everything is working as you want it then?

I figured out what the problem is. When a record is submitted the HTML tags are also sent to the database.
I've logged in to SQL Server and copied a record. This is what is sent to the database when a user posts the "Stroller for sale" message.

<!DOCTYPE html>
<html>
<head>
</head>
<body>
Stroller for sale
</body>
</html>

Select allOpen in new window

My problem is not solved yet. I still don't know how to have only the first 50 characters displayed.

that'll do it :)

here's a function (source) that'll strip out your html tags:

FUNCTION stripHTML(strHTML)
  Dim objRegExp, strOutput, tempStr
  Set objRegExp = New Regexp
  objRegExp.IgnoreCase = True
  objRegExp.Global = True
  objRegExp.Pattern = "<(.|n)+?>"
  'Replace all HTML tag matches with the empty string
  strOutput = objRegExp.Replace(strHTML, "")
  'Replace all < and > with &lt; and &gt;
  strOutput = Replace(strOutput, "<", "&lt;")
  strOutput = Replace(strOutput, ">", "&gt;")
  stripHTML = strOutput    'Return the value of strOutput
  Set objRegExp = Nothing
END FUNCTION

Select allOpen in new window

and to use it, you would do something like:

<%=stripHTML( rs("ItemDescription") )%>

Select allOpen in new window

PERFECT!!!!
Thank you very much for your help.

my pleasure