Solved

Hyperlinks to SQL Server data

Posted on 1997-10-04
8
250 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Read about why website design really matters in today's demanding market.
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

746 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

12 Experts available now in Live!

Get 1:1 Help Now