• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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.
0
innovate
Asked:
innovate
  • 3
  • 3
  • 2
1 Solution
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now