I have some code for a report (see example below) that basically queries up a database and displays the results in html. However, I need to display one of the fields as a hyperlink for a drill down.
The link would look like http://host.com/code/tier2.asp?value=x where x would be field4 in the code.

I am really struggling with this, under a deadline and any help would be appreciated.


Dim mySQL
Set ss = Server.CreateObject("ADODB.Recordset")
ss.ActiveConnection = conn
mySQL = "SELECT Field1, Field2, Field3, Field4 From TTable"

ss.Open mySQL

Dim i
Dim j
        %><TABLE BORDER="0"><TR><%
        For i = 0 To ss.Fields.Count - 1
            %><TD width ="22%"><B><%=ss.Fields(i).Name%></B></TD><%
        Next
        %></TR><TR></TR><TR></TR><%
        i = 1
        Do While Not ss.EOF
            %><TR<% if i mod 2="0" Then%>
             >
         <%Else%>
                >
         <%End if
            For j = 0 To ss.Fields.Count-1
                %><TD width ="22%"><%=ss.Fields(j).Value%></TD><%
            Next
            %></TR><%
            ss.MoveNext
            i=i+1
        Loop
ss.close
Set ss = Nothing
%>
</tr>
</TABLE>

This will write the correct value:

http://host.com/code/tier2.asp?value=<%=ss("field4")%>

"The one language all programmers understand is profanity."

I guess I was not clear. Sorry. I need the code to process the fields to make one of them a hyperlink without making other fields a hyperlink. Sorry for the confusion, but you response was appreciated.

You mean inside of a Crystal Drill Down report? Or are you just trying to display this as HTML on a web page?

"The one language all programmers understand is profanity."

HTML on a web page. However, I was able to modify some code posted here from Jonax (http://p2p.wrox.com/topic.asp?TOPIC_ID=4138) that does 95% of what I want. The only thing I need now is to figure out how to provide a delimited list of fields that I used in the select clause to not display in the HTML output, but still be available to use as part of the hyperlink.

Code:



<%
Dim ReportSDate
Dim ReportEDat
ReportSDate = Request.Form("StartDate")
ReportEDate = Request.Form("EndDate")
Session("ReportStartDate") = ReportSDate
Session("ReportEndDate") = ReportEDate
%>


<%
'Procedure to create a table
sub MakeTable(SourceIn, sLinkURL, iIdField, sLinkFields)
dim Field, objField, bLinkIt, t
dim aLinkFields
aLinkFields = split(lcase(sLinkFields),",")

Set oRSmtr = Server.CreateObject("ADODB.Recordset")
oRSmtr.ActiveConnection = conn
oRSmtr.Open SourceIn

response.write "<table border=0><tr>"
For Each Field in oRSmtr.Fields
    response.write "<th>" & Field.name & "</th>"
next
'response.end
Do while not oRSmtr.EOF
    response.write "<tr>"
    For each objField in oRSmtr.fields
        bLinkIt = FALSE
        for t = 0 to ubound(aLinkFields,1)
            if aLinkFields(t) = lcase(objField.name) then
                bLinkIt = TRUE
                exit for
            end if
        next

        if bLinkIt = TRUE then
            response.write "<td><a href='" & sLinkURL & oRSmtr.fields(iIdField) & "'>" & objField.value & "</a>&nbsp;</td>"
        else
            response.write "<td>" & objField.value & "&nbsp;</td>"
        end if
    Next
    oRSmtr.movenext
    response.write "</tr>"
loop
response.write "</table>"
oRSmtr.close
set oRSmtr=nothing
end sub
%> </p>
<%call MakeTable("SELECT field1, field2, field3, field4, field4 FROM table WHERE stuff", "./somepage.asp?ID=", 0, "field1")%>
</p>