Access "Hyperlink" conversion in ASP 2

[ppedersen1]

I have an asp page which takes data from an Access database. The trouble I am having is where one field in my Access database is a "Hyperlink" field. When I display the data in an ASP page it does not create a hyperlink but displays # signs on each side of the data.

I set the asp page to turn that data into a hyperlink, but with the # on both sides makes the link incorrect.

Therefore, I would like to know, either if there is a way to request the data from access so it doesn't return # signs or otherwise I would like to know if there is a way to edit the data to take out the first and last characters in the returned data.

Any clues?

 

[MoLaker]

You could get fancy with the SQL string by using left and right string functions for the hyperlink field, but that would get clumsey. It's probably easier to massage the data after it has been returned.

Assuming a recordset has been opened;

Dim hype
hype = RS([hyperlink field])

hype = left(hype,len(hype)-1)
hype = right(hype,len(hype)-1)

or

Dim hype
hype = RS([hyperlink field])
hype = mid(hype,2,len(hype)-2)

Either way should return the data with the # signs stripped off in the variable "hype" (or whatever you want to call it).

 

[ppedersen1]

Thank you! This is exactly what I was looking for. I used the second example you gave me. This was very helpful.

 

[ppedersen1]

I am now having a problem where the hyperlink data comes in doubles. For example, if the access database has a hyperlink to "docs/picture.jpg" then the results I am getting in return are "#docs/picture.jpg#docs/picture.jpg#". Why would this be, and how do I fix it?

Incidentally, I have a feeling this has to do with Access. Any clues?

 

[MoLaker]

Can you post your code?

 

[ppedersen1]

Here it is:

Do While Not rsDatabase.EOF

If rsDatabase("Hyperlink") >= "" then

Dim hyperlink

hyperlink = rsDatabase("Hyperlink")
hyperlink = mid(hyperlink,2,len(hyperlink)-2)
End if

Response.Write "<tr><td WIDTH=150 valign=top><font size=3>" & rsDatabase("Person") & "</font></td>"
Response.Write "<td WIDTH=300 valign=top><font size=3>" & rsDatabase("Description") & "</font></td>"
Response.Write "<td WIDTH=100 valign=top><font size=3>" & rsDatabase("Date") & "&#160</font></td>"


'This particular section determines if there is a linked document and if so links to it.

If rsDatabase("Hyperlink") >= "" then
Response.Write "<td WIDTH=150 valign=top><font size=3><a href=""http:\\myserver\" & hyperlink & """>" & rsDatabase("Doc") & "</a></font></td>"
else
Response.Write "<td WIDTH=150 valign=top><font size=3>" & rsDatabase("Doc") & "&#160</font></td>"
end if

rsDatabase.MoveNext

Loop
Response.Write "</table>"
If rsDatabase.BOF Then
Response.Write "<p>Sorry, there currently isn't any data in the database.</p>" & vbNewLine
End If

 

[MoLaker]

Are you saying rsDatabase("Hyperlink") contains the double hyperlink?

Or are you saying rsDatabase("Hyperlink") is OK, but the variable 'hyperlink' is doubled?

 

[DreXor]

could be alot easier to just convert the field type to text with length 255, unless you need in database ability to click the link

 

[ppedersen1]

I am using the variable to create link and this is causing oddball occurence. However, the results are not consistent. Sometimes it works fine. The wrong results are when the link created results in something like this:

1.

http://myserver/ocs/picture.jpg#docs/picture.jpg

instead of
2.

http://myserver/docs/picture.jpg

As you will notice in the above code, I get rid of the first and last characters. If the data is "doubled" then I get the results of 1 above. (notice the "docs" is incomplete, but the # is missing from the end?

If it decides to work fine, I get result 2.

With regards to DreXor's idea, I could do this, but then I loose the database ability to click the link (as you said). I would prefer to keep this function. It seems like I should be able to debug this rather than figure out on alternative.

Let me know what ideas you got. Tks.

 

[MoLaker]

Jeeze! This is getting complicated. You need to fall back and regroup 'cause I've not been leading you exactly where you need to go.

Here's a couple of links that might help.

http://support.microsoft.com/default.aspx?scid=kb;en-us;163706&Product=acc

and

http://support.microsoft.com/default.aspx?scid=kb;en-us;166413&Product=acc

 

[BDC2]

try this:

hyperlink = rsDatabase("Hyperlink")
hyperlink = mid(hyperlink,InStr(hyperlink,"#")+1,len(hyperlink)-2)

BDC.

 

[MoLaker]

What seems to be happening is some of the hyperlink field data contains a Display Text and Address and some contains an Address only. If this is the case, BDC2's solution should work.

However, I thought about using the Instr() function when ppedersen1 reported this last problem. My concern was that the hyperlink data seems not to be consistent. Consequently, using any predetermined number to establish the length of the string might be a problem, even in BDC2's solution. Something like the following might handle a few more variations...

hyperlink = rsDatabase("Hyperlink")

' return all data to right of first # sign
' IF # sign is not last character
If InStr(1,hyperlink,"#",1) > 0 and InStr(1,hyperlink,"#",1) < len(hyperlink)  then
    hyperlink = mid(hyperlink,InStr(1,hyperlink,"#",1)+1)
End If

' remove remaining # sign(s)
hyperlink = Replace(hyperlink,"#","")

This still does not test whether the parsed string is a valid hyperlink. This could probably be done by using server.mapath() and dir() functions before actually writing the hyperlink to HTML. Something like...

If rsDatabase("Hyperlink") > "" [b]and Dir(Server.mappath("\" & hyperlink)) <> ""[/b] then

Response.Write "<td WIDTH=150 valign=top><font size=3><a href=""http:\\myserver\" & hyperlink & """>" & rsDatabase("Doc") & "</a></font></td>"

else

Response.Write "<td WIDTH=150 valign=top><font size=3>" & rsDatabase("Doc") & "&#160</font></td>"

end if

Or something like that.

 

[MoLaker]

Whoops! I'm mixing VBA and VBScript. Forget the last part in validating the hyperlink unless you want to do something like what is discussed in this link.

http://www.4guysfromrolla.com/webtech/092998-3.shtml

 

[DreXor]

With regards to DreXor's idea, I could do this, but then I loose the database ability to click the link (as you said). I would prefer to keep this function. It seems like I should be able to debug this rather than figure out on alternative.

you would only lose this ability IN database, in pages, you have to supply HTML to make it a link ( i.e. <a href=...> )

and if you have the DB open the the web pages wont work because the data will be locked cause the file is in use by an application.


also from what i've gathered from this thread you're getting :

#

http://www.domain.com/blah#

and sometimes :

#

http://www.domain.com/blah#/blah#

if that's the case you could be lazy and just note the pattern, validate that RS("hyperlink") <> "" then
linkarr = split(rs("Hyperlink"), "#")
your link will always be position 1 validate on top of that that u have a Ubound(linkarr) > 0 and you should get squared away

but that's only if i'm understanding this right

 

[MoLaker]

I kept looking for a way to validate your hyperlink. I found this on

http://www.freecodemomma.com.

Private Function File(byVal pathname)
	Dim objFSO
	Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
	File = objFSO.FileExists(pathname)
	Set objFSO = Nothing
End Function

Include this function on the page where you want to test for a valid link and change the Dir() function call to File() function call.

File(Server.mappath("\" & hyperlink))

Note: You will have to adjust the "\" for the actual location of your ASP page.

"\" will take you to the server's

http://wwwroot

directory
"..\" will take you one directory down towards the website root which is probably what you'll need.

 

[ppedersen1]

Thanks for all of your responses. I have tried to keep my coding simple. Your suggestions have been helpful.

The problem was Access was returning the DisplayText data as well as the URL data on occassions.

This was solved by modifying my code from:

If rsDatabase("Hyperlink") >= "" then
Dim hyperlink
hyperlink = rsDatabase("Hyperlink")
hyperlink = mid(hyperlink,2,len(hyperlink)-2)
End If

to:

If rsDatabase("Hyperlink") >= "" then
Dim hyperlink
hyperlink = rsDatabase("Hyperlink")
hyperlink = mid(hyperlink,InStr(hyperlink,"#")+1,len(hyperlink)-2)
hyperlink = Replace(hyperlink,"#","")
End If

Thanks to BDC2 and MoLaker for helpful comments on this.

Drexor, I have to admit, that you are correct. I cannot use the hyperlink functionality in the Access database while also using the database for my ASP purposes at the same time. However, the way I currently have my database set up, I need to keep this feature set up until my ASP site is finalized and into use. This does however bring up a future problem I may have. The sooner I can wrap the site up and have it perfect, then I will probably switch over.

I have not had particular problems in this area, however, I have a concern about the number of visitors that can be using and updating data in the database via ASP at the same time. I am not sure what the future impact of this problem may be and if I will eventually have to switch over to a large database system.

 

[DreXor]

glad everything got worked out for you, and i seem to be the "just in case" guy around here most of the time, hopefully the info will be helpful in the future, or if never, no biggy. I just try like the rest of us here to keep those asking questions informed

happy coding