Solved

Hyperlinks to SQL Server data

Posted on 1997-10-04
8
255 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:innovate
  • 3
  • 3
  • 2
8 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 1855252
Could you post your code for the easy bit?
0
 
LVL 2

Author Comment

by:innovate
ID: 1855253
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
 
LVL 2

Author Comment

by:innovate
ID: 1855254
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
 
LVL 84

Expert Comment

by:ozo
ID: 1855255
<a href="nextpage.asp/PARAMERTER">parameter in $ENV{PATH_INFO}</A>
<a href="nextpage.asp?PARAMERTER">parameter in $ENV{QUERY_STRING}</A>
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 84

Expert Comment

by:ozo
ID: 1855256
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
 

Accepted Solution

by:
rpabst earned 150 total points
ID: 1855257
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
 
LVL 2

Author Comment

by:innovate
ID: 1855258
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
 

Expert Comment

by:rpabst
ID: 1855259
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem to be resolved in this article Currently, development of website and web application can be done without writing thousands of lines of programming code by hand. Description This can be done through by using a open source framework such …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now