How to create a button to view next record in a database?

ct976 · #1 (**permalink**) 08-31-04, 11:10

here is what it looks like now, the source is an excel file acting as a database:

http://10.1.17.90/dl/New%20Folder/data.asp

my code is like this:

Code:

<HTML><BODY>

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DRIVER=Microsoft Excel Driver (*.xls);" _
            & "DBQ=" & Server.MapPath("310.xls")
%>

<%
	Set tempRS = Server.CreateObject("ADODB.RecordSet")
	tempRS.CursorType = adOpenStatic
	tempRS.CursorLocation = 3
	queryNext = "SELECT Listing_ID,Main_listing_TN,Action_Indicator,Exchange_abbr,Primary_use,Listing_type,Letter_Sort,Sort_word,Subsequent_word,Location,Civic_number,Street_address,Community_name,Postal_code,Sub_listing_text,Sublist_class_of_svc,Sublisting_TN,Published_flag,Placement_word,Designation,Title_of_address,Title_of_status FROM [310$]"
 	tempRS.open queryNext, conn
%>


<table border="1">
<TD>

<tr>
  <th><strong>Service Provider ID</strong></th>
  <td><input type="text" name="ServiceID" value = "8377"></td>
</tr>
<tr>
  <th><strong>Listing ID</strong></th>
  <td><input type="text" name="Listing_ID" value = "<%=tempRS("Listing_ID")%>"></td>
</tr>
<tr>
  <th><strong>Action Indicator</strong></th>
  <td><input type="text" name="Action_Indicator" value = "<%=tempRS("Action_Indicator")%>"></td>
</tr>
<tr>
  <th><strong>Listing Type Indicator</strong></th>
  <td><input type="text" name="Listing_type" value = "<%=tempRS("Listing_type")%>"></td>
</tr>
<tr>
  <th><strong>Sort Word</strong></th>
  <td><input type="text" name="Sort_word" value = "<%=tempRS("Sort_word")%>"></td>
</tr>
<tr>
  <th><strong>Subsequent Words</strong></th>
  <td><input type="text" name="Subsequent_word" value = "<%=tempRS("Subsequent_word")%>"></td>
</tr>
<tr>
  <th><strong>Placement Word</strong></th>
  <td><input type="text" name="Placement_word" value = "<%=tempRS("Placement_word")%>"></td>
</tr>
<tr>
  <th><strong>Designation</strong></th>
  <td><input type="text" name="Designation" value = "<%=tempRS("Designation")%>"></td>
</tr>
<tr>
  <th><strong>Civic Number</strong></th>
  <td><input type="text" name="Civic_number" value = "<%=tempRS("Civic_number")%>"></td>
</tr>
<tr>
  <th><strong>Street Address</strong></th>
  <td><input type="text" name="Street_address" value = "<%=tempRS("Street_address")%>"></td>
</tr>
<tr>
  <th><strong>Location</strong></th>
  <td><input type="text" name="Location" value = "<%=tempRS("Location")%>"></td>
</tr>
<tr>
  <th><strong>Community Name</strong></th>
  <td><input type="text" name="Community_name" value = "<%=tempRS("Community_name")%>"></td>
</tr>
<tr>
  <th><strong>NPA</strong></th>
  <td><input type="text" name="NPA" value = "<%=tempRS("Main_listing_TN")%>"></td>
</tr>
<tr>
  <th><strong>Line Number</strong></th>
  <td><input type="text" name="Line_Number" value = "<%=tempRS("Main_listing_TN")%>"></td>
</tr>
<tr>
  <th><strong>B/R/G Indicator</strong></th>
  <td><input type="text" name="BRG_Indicator" value = "N/A"></td>
</tr>
<tr>
  <th><strong>Sublist text</strong></th>
  <td><input type="text" name="Sublist_text" value = "<%=tempRS("Sub_listing_text")%>"></td>
</tr>
<tr>
  <th><strong>Sublist NPA</strong></th>
  <td><input type="text" name="Sublist_NPA" value = "<%=tempRS("Sublisting_TN")%>"></td>
</tr>
<tr>
  <th><strong>Sublist Line Number</strong></th>
  <td><input type="text" name="Sublist_Line_Number" value = "<%=tempRS("Sublisting_TN")%>"></td>
</tr>
<tr>
  <th><strong>Sublist B/R/G Indicator</strong></th>
  <td><input type="text" name="Sublist_BRG_Indicator" value = "N/A"></td>
</tr>

</TD>
</table>



<form action="data.asp" method="get">
<input type="submit" value="Previous Record">

<%
  tempRS.close
  Set tempRS = Nothing
  conn.close
  Set conn = Nothing
%>

</BODY></HTML>

how do i make this button(create the query on the button)onclick it displays the next record in the database on the same table, replacing all the fields?

rokslide · #2 (**permalink**) 08-31-04, 21:30

Basically the easiest way to do this is to add some ordering to you sql statement and then select based on that ordering for example...

Say you have a user table and one of your fields is UserId which is an integer to uniquely identify the user. You could use

Code:

select top 1 username, userdob from user order by userid desc

to get the first record and then to get the next one you could use

Code:

select top 1 username, userdob from user where userid < previoususerid order by userid desc

but it really all depends on the structure of your data etc.

You could also store your recordset in a sessions variable or something and set another session variable that indicates what record you are up to in your recordset.

It really depends on exactly what you are trying to achieve.

ct976 · #3 (**permalink**) 09-01-04, 10:09

select top 1 username, userdob from user where userid < previoususerid order by userid desc

ok i am trying to use something like this now, but that means i have to have some temp variable to store the userid as previoususerid. how would i be able to do that?

userid = previoususerid

select top 1 username, userdob from user where userid< previoususerid order by userid desc

?? that wouldn't work, becuz it wouldnt work for the next next record..

rokslide · #4 (**permalink**) 09-01-04, 19:09

when you click the "next" button you need to send the current userid with the http request, either as a querystring variable or as part of the submission.

you can then request the value from the http request and use that to do your search.

Internet Services

Web Development

Digital Marketing

Consumer Tech