how to drill down on a table for more info

[ciberperson]

My user will enter a partnumber on a form (page 1) and this calls an asp file which opens the database and returns a record set in table form (page 2). This part works fine. Now I want to let the user click on a hyperlink within the returned data and "drill down" for more info on the particular record (to display on page 3). Do I open the connection again? How do I pass the variable info from the record in this dynamic table to the third page? I see this done everyday but can't figure it out. Thanks for any help or ideas! [sig][/sig]

 

[mhenley]

You could handle this a couple ways. I would recommend calling page3 with a form instead of href. You could use hidden form variables to pass a record count. You would then use this record count on page three to do your search again, this time giving the full data. Something like this...

<%
'call recordset
RecordCount = 0
Do until rsRecordSet.eof
RecordCount = RecordCount + 1
%>
<form method=&quot;post&quot; action=&quot;

http://PageName&quot;>

'display basic info here
<input type=&quot;hidden&quot; name=&quot;RecordCount&quot; value=&quot;<%=RecordCount%>&quot;>
<input type=&quot;submit&quot; value=&quot;Get More Data&quot;>
<%
rsRecordSet.movenext
loop
%>

Then on page3 you would just do:

<%
'call recordset again
RecordCount = request.form(&quot;RecordCount&quot
RecordNum = 1
DO until RecordNum = RecordCount
RecordNum = RecordNum + 1
rsRecordSet.movenext
loop
%>

When you exit the loop you will be at the right record to display your data.

Hope this helps!
mhenley


[sig][/sig]

 

[RobSchultz]

I have implemented this on my company's intranet for Systems Management Server. I use stored procedures for the queries and a single table holds the definitions for all the drilldown structures. I use hidden fields and a single recursive asp page to query down as deep as the drilldown structure allows the user to go. It works extremely well and adding new drilldowns is fairly easy and doesn't necessitate a change in the html/script to implement.

Here is the table def for the drilldown...

Relationship - int - The family of the drilldown
ZOrder - int - The order of the query within the drilldown
Name - varchar(128) - The name of the drilldown query
DescriptionCurrent - varchar(512) - The description of the query
DescriptionNext - varchar(512) - The description of the next query
SQLQuery - varchar(1024) - The query itself. In my table this starts with SP:, SQL:, SCR: to define a stored procedure, SQL Query, or Script function to execute.

Example of SQLQuery is SP:ddsp_AllUsers x,2. In my routine the x tells the asp script to query for whatever the user clicked on and the 2 allows the stored procedure to filter it with whatever the user clicked on 2 queries ago. The stored procedures make use of variables to accomplish this and my asp page keeps a history of the queries the user makes.

If this all sounds very confusing, it is. It took me awhile to create this routine but it does work very well.

You don't have to open the connection again but you do have to close the recordset before you re-use it.

Hope this helps, [sig]<p>Rob<br><a href=mailto:robschultz@yahoo.com>robschultz@yahoo.com</a><br>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br>
"Focus on the solution to the problem,<br>
not the obstacles in the way."<br>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~[/sig]