Solved

Hyperlinks to SQL Server data

Posted on 1997-10-04
8
260 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Master Content Pages, WebForms, and Session Variables 1 43
tomcat startup error 5 59
applying error reporting code to see paypal error messages 13 51
Help with query 3 23
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

776 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