First up, I wasn't sure whether this belonged here or in the ASP Forum, so my apologies if this is the wrong place.

I'm calling data from a mysql database to show the top 20 links from my Link Dump here - http://www.milkmansworld.co.uk/guestbook/default.asp

I don't want the whole title of the link to show up, just the first 17 characters as it does here using php - http://www.milkmansworld.co.uk

This is the basic code I am using,



<%
Dim sConnection, objConn , objRS

sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=name; UID=login;PASSWORD=password; OPTION=3"

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open(sConnection)

Set objRS = objConn.Execute("SELECT * FROM database WHERE database.approved=""yes"" ORDER BY clicks DESC LIMIT 0, 20")




While Not objRS.EOF
Response.Write "<a href=""" & objRS.Fields("url") & """ target=_blank"""">" & objRS.Fields("title") & "</a> <br><p>"




objRS.MoveNext
Wend

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

Any help will be much appreciated,

Thanks

dim title as string = objRS.Fields("title")
if len(title) > 17 then title = left(title, 17)

function GetSnippet(strIn,intMaxLen)
' extract a snippet of text from string
dim temp
dim EndPos
temp = left(strIn,intMaxLen)
EndPos = InStrRev(temp," ")
temp = left(temp, EndPos)
GetSnippet = temp
end function

It cuts off at the end of a word rather than splitting words in the middle

I use it for my "Read More ..." links "
Click here to Read More ...

Thanks for the replies.

I've tried both, but I couldn't get either to work,

With chrishirst's solution I take it I should be using the "display code" half way down this page to display the links on my page - http://www.tek-tips.com/viewthread.cfm?qid=882989,

But I get this error message

Variable is undefined: 'strItem'

on this line

strItem = GetSnippet(objRS.fields("title"),17)



and for ForrestCroce's solution I get the message

Expected end of statement

dim title as string = objRS.Fields("title")
----------^

More than likely i'm not using the code in the right place, or for chrishirst's solution i've substituted the wrong words with my field names.

I think ForrestCroce's solution is the one i'd rather use, I don't want the link title to go over 17 characters regardless and i'm happy for a word to be cut in half as that's how it is on my php pages. Plus ForrestCroce's solution is only 2 lines of code, simpler the better as far as i'm concerned.

Thanks

Variable is undefined: 'strItem'

Have you dimensioned the variable (dim strItem) before using it?

Answer is No BTW

Expected end of statement

dim title as string = objRS.Fields("title")
----------^

In asp VBScript all variables are variant types until given a value
so it should be

dim title
title = objRS.Fields("title")

Use whichever code you prefer of course , but the advantage of a function is;
declare it once, use it many times.

Thanks Chris, you are of course a legend, I think you've answered every question I've ever asked on this forum.

Have I dimensioned the variable?
If I knew how or even what this is, I may have done.

I tried,

dim title
title = objRS.Fields("title")

as one of my many trial and error attempts, I get this error message though,

Object required: ''

on this line

title = objRS.Fields("title")

Thanks

I just moved these 2 lines down the page

title = objRS.Fields("title")
if len(title) > 17 then title = left(title, 17)

below,

Set objRS = objConn.Execute("SELECT ..................

I now get no error message but the page still displays the whole title and not just 17 characters.

ok to redo your code a bit, 2 alternate lines of display code

Additions in red for my function
Additions in green for Forrest's code

<%
function GetSnippet(strIn,intMaxLen)
' extract a snippet of text from string
dim temp
dim EndPos
temp = left(strIn,intMaxLen)
EndPos = InStrRev(temp," ")
temp = left(temp, EndPos)
GetSnippet = temp
end function
%>
<% 
Dim sConnection, objConn , objRS 



sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=name; UID=login;PASSWORD=password; OPTION=3" 

Set objConn = Server.CreateObject("ADODB.Connection") 

objConn.Open(sConnection) 

Set objRS = objConn.Execute("SELECT * FROM database WHERE database.approved=""yes"" ORDER BY clicks DESC LIMIT 0, 20")




While Not objRS.EOF
Response.Write "<a href=""" & objRS.Fields("url") & """ target=_blank"""">" & getSnippet(objRS.Fields("title"),17) & "</a> <br><p>"


Response.Write "<a href=""" & objRS.Fields("url") & """ target=_blank"""">" & left(objRS.Fields("title"),17) & "</a> <br><p>"





objRS.MoveNext
Wend 

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

Usually the function would be in a seperate code library and included into any page that needed to use it, so it can be called whenever you wanted any text cutting down.

Nice one, works like a charm, thanks again for that.