Hyperlinks to SQL Server data

I'm using active server pages and SQL Server.

I wish for a user (remote web client) to enter a search string and have the filtered data returned.  THAT IS THE EASY BIT.  
The question is...

I want the returned records to each be a HYPERLINK to the subordinate records in a related table.

eg.

Enter a person name "Smith"

The "Smiths" are returned like

A Smith
B Smith
C Smith  etc etc

Clicking on "A Smith" returns all the "A Smith" records in say the Sales table.

Like

Sale 1 02/02/97 $45
Sale 2 03/03/97 $104  etc etc

This could be repeated to return items but an example of the single level hyperlink would be nice.

The REQUIREMENTS ARE.

MUST be ASP on NT 4 SP3, IIS 3
No browser specific stuff.
No add-ons to ASP used apart from the native/vanilla stuff.
Some example please.

Some JavaScript on the browser side is no problem.
LVL 2
innovateAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozoCommented:
Could you post your code for the easy bit?
0
innovateAuthor Commented:
Hi ozo, as requested

The code is not yet written for this particular page but it will be along the lines of ....
' Get Name
 lookSName = Request.Form("LookSName")

'Replace any single quotes (O'Reily) with double singles
lookSName = Trim(replace(lookSName,"'","''"))
'Add wild card to search start of field
lookSName = lookSName & "%"

'Create SQL
SQLQuery = "SELECT * FROM EmpDetailsDepart WHERE _Surname Like '" & LookSName & "'"

' Create and open connection NOTE strConnect is set in a 'Server side #Include
Session("ConnectString") = strConnect
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open Session("ConnectString")
Set RSCustomerList = Server.CreateObject("ADODB.Recordset")

RSCustomerList.Open SQLQuery, OBJdbConnection, 3

'END OF EXAMPLE
At this point I will loop through the recordset and create the rows of HTML hyperlinks with something like the customer ID to re-call the related data and this is what I don't know how to do.

As mentioned I want each returned row to create a hyperlink so a user may click on it and automatically re-interrogate the database for the related records.  

The actual code will be far more complex as there are 19 search criteria and scrolling page code for the returned data but this should give you the gist.
0
innovateAuthor Commented:
Ok to make it simpler.

Can anyone tell me how to call a hyperlink to an ASP page where the hyperlink passes a parameter.

Something like

<a href="nextpage.asp PARAMERTER">PassParam/a>

so the page "nextpage.asp" can read PARAMETER into a variable.

Any ideas
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

ozoCommented:
<a href="nextpage.asp/PARAMERTER">parameter in $ENV{PATH_INFO}</A>
<a href="nextpage.asp?PARAMERTER">parameter in $ENV{QUERY_STRING}</A>
0
ozoCommented:
Sorry, $ENV{QUERY_STRING} is where you might find the ?parameters in Perl
But I see from
http://www.activeserverpages.com/tutorial/http.asp
that you probably want to use Request.QueryString to get the parameters
0
rpabstCommented:
Hi Innovate,
That's some active server you got there. Well ok. Listen carefully, i will tell this only once.

You say: The resultset that's returned by the SQLServer has to
be showed as hyperlinks. Well, the easy way to do it is to
hyperlink to a different asp. Let's say that one of the fields in your resultset has a CUSTOMER_ID field. You'll be needing that
ID to get more information from the SQLServer. So you'll be
building code like this:

<b>the server returned the following results</b><br>
<table>
<% do while not RS.EOF%>
    <tr>
    <td><%=RS("NAME")%></td>
    <td><%=RS("CUSTOMER_ID")%></td>
    <%
    'Now set up the hyperlink data. Free tip: You can also insert
    'nice pictures that match the status of an order this way.
   
    hLink = "getmemoreinfo.asp?customer=" & rs("CUSTOMER_ID")
    'Ready. now show it, by inserting it between the href quotes.
    %>
    <td><A href="<%=hLink %>">Click here for more info</a></td>
    </tr>
    <% RS.MoveNext %>
</table>
------------------------------------------etc.
//Getmemoreinfo.asp
'Now the hyperlink refers to this asp file.
'Since there's no form involved now, the Request.form object
'doesn't work. But we still have to get that customer id from
'the command-line, and that's not difficult. It goes like this.
<%
SQL = "SELECT * FROM sales where CUSTOMER_ID =" & _  
      request("customer_id")
%>
'Now you can take it from there. Just send the query to the
'Server and table-out the results.

Greetings,
Robert Pabst
Webmaster@edn.nl








 






0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
innovateAuthor Commented:
Thank you for your assistance, seems to work fine.

But just to be pedantic

Where you have Request("Customer_ID") I presume you mean Request("Customer") since the a Href line is
hLink = "getmemoreinfo.asp?customer=" & rs("CUSTOMER_ID")

The parameter name being Customer and value being the CUSTOMER_ID fields value

This works for me, As I said I'm being a bit pedantic.  But thanks again.
0
rpabstCommented:
I just KNEW I made some errors there because I had to write the examples from memory. Didn't have a IIS webserver nearby to syntaxcheck it. But, obviously, you're right and I'm sorry.
Anyway, I'm glad you got it working.




0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.