Microsoft ActiveX Data ObjectsMicrosoft ActiveX Data Objects*
*Contents  *Index  *Topic Contents

MoveFirst, MoveLast, MoveNext, and MovePrevious Methods Example

This example uses the MoveFirst, MoveLast, MoveNext, and MovePrevious methods to move the record pointer of a Recordset based on the supplied command. The MoveAny procedure is required for this procedure to run. 

Public Sub MoveFirstX()

	Dim rstAuthors As ADODB.Recordset
	Dim strCnn As String
	Dim strMessage As String
	Dim intCommand As Integer

	' Open recordset from Authors table.
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set rstAuthors = New ADODB.Recordset
	rstAuthors.CursorType = adOpenStatic
	' Use client cursor to enable AbsolutePosition property.
	rstAuthors.CursorLocation = adUseClient
	rstAuthors.Open "authors", strCnn, , , adCmdTable

	' Show current record information and get user's method choice.
	Do While True

		strMessage = "Name: " & rstAuthors!au_fName & " " & _
			rstAuthors!au_lName & vbCr & "Record " & _
			rstAuthors.AbsolutePosition & " of " & _
			rstAuthors.RecordCount & vbCr & vbCr & _
			"[1 - MoveFirst, 2 - MoveLast, " & vbCr & _
			"3 - MoveNext, 4 - MovePrevious]"
		intCommand = Val(Left(InputBox(strMessage), 1))
		If intCommand < 1 Or intCommand > 4 Then Exit Do

		' Call method based on user's input.
		MoveAny intCommand, rstAuthors
	Loop
	rstAuthors.Close

End Sub

Public Sub MoveAny(intChoice As Integer, _
	rstTemp As Recordset)

	' Use specified method, trapping for BOF and EOF.
	Select Case intChoice
		Case 1
			rstTemp.MoveFirst
		Case 2
			rstTemp.MoveLast
		Case 3
			rstTemp.MoveNext
			If rstTemp.EOF Then
				MsgBox "Already at end of recordset!"
				rstTemp.MoveLast
			End If
		Case 4
			rstTemp.MovePrevious
			If rstTemp.BOF Then
				MsgBox "Already at beginning of recordset!"
				rstTemp.MoveFirst
			End If
	End Select

End Sub

VBScript Version

The following is the same example written in VBScript to be used in an Active Server Page (ASP). To view this example, you need to create a system Data Source Name (DSN) called AdvWorks for the Microsoft Access database file AdvWorks.mdb, which is installed with Microsoft Internet Information Server and located at C:\InetPub\ASPSamp\AdvWorks. Locate the file adovbs.inc and place it in the directory you plan to use. Cut and paste the following code to Notepad or another text editor and save it as MoveOne.asp. You can view the result in any client browser.

Try moving beyond the upper or lower limits of the recordset to see error handling work. 

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>
<HTML><HEAD>
<TITLE>ADO 1.5 MoveNext MovePrevious MoveLast MoveFirst Methods</TITLE></HEAD>
<BODY> 
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center>
<H3>ADO Methods<BR>MoveNext MovePrevious MoveLast MoveFirst</H3>
<!-- Create Connection and Recordset Objects on Server -->
<%
 'Create and Open Connection Object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open "AdvWorks" 
'Create and Open Recordset Object
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"

RsCustomerList.Open

' Check Request.Form collection to see if any moves are recorded

If Not IsEmpty(Request.Form("MoveAmount")) Then
'Keep track of the number and direction of moves this session
	
	Session("Moves") = Session("Moves") + Request.Form("MoveAmount")
	
	Clicks = Session("Moves")
'Move to last known position
	RsCustomerList.Move CInt(Clicks)
'Check if move is + or - and do error checking
		If CInt(Request.Form("MoveAmount")) = 1 Then
		
			If RsCustomerList.EOF Then
				Session("Moves") = RsCustomerList.RecordCount
				RsCustomerList.MoveLast
			End If	

			RsCustomerList.MoveNext
		End If

		If Request.Form("MoveAmount") < 1 Then
			
			RsCustomerList.MovePrevious
		End If
'Check if First Record or Last Record Command Buttons Clicked
		If Request.Form("MoveLast") = 3 Then
			RsCustomerList.MoveLast
			Session("Moves") = RsCustomerList.RecordCount
		End If
		If Request.Form("MoveFirst") = 2 Then
			RsCustomerList.MoveFirst
			Session("Moves") = 1
		End If
	
	
End If
' Do Error checking for combination of Move Button clicks
		If RsCustomerList.EOF Then
			Session("Moves") = RsCustomerList.RecordCount
			RsCustomerList.MoveLast
			Response.Write "This is the Last Record"
			End If	
	
		If RsCustomerList.BOF Then
			Session("Moves") = 1
			RsCustomerList.MoveFirst
			Response.Write "This is the First Record"
		End If
	

%>

<H3>Current Record Number is <BR>
<!-- Display Current Record Number and Recordset Size -->
<% If IsEmpty(Session("Moves"))  Then 
Session("Moves") = 1
End If
%>

<%Response.Write(Session("Moves") )%> of <%=RsCustomerList.RecordCount%></H3>
<HR>


<Center><TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>
</TD></TR>

<!--Display ADO Data from Customer Table-->

  <TR>
  <TD BGCOLOR="f7efde" ALIGN=CENTER> 
    <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RSCustomerList("CompanyName")%> 
    </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
    <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RScustomerList("ContactLastName") & ", " %> 
      <%= RScustomerList("ContactFirstName") %> 
    </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
    <FONT STYLE="ARIAL NARROW" SIZE=1>
     
      <%= RScustomerList("PhoneNumber")%> 
   </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
    <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RScustomerList("City")%> 
    </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
    <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RScustomerList("StateOrProvince")%> 
    </FONT></TD>
  </TR> </Table></FONT>


<HR>
<Input Type = Button Name = cmdDown  Value = "<    ">
<Input Type = Button Name = cmdUp Value = "    >">
<BR>
<Input Type = Button Name = cmdFirst Value = "First Record">

<Input Type = Button Name = cmdLast Value = "Last Record">
<H5>Click Direction Arrows to Use MovePrevious or MoveNext 
<BR> </H5>

<!-- Use Hidden Form Fields to send values to Server -->

<Form Method = Post Action="MoveOne.asp" Name=Form>
<Input Type="Hidden" Size="4" Name="MoveAmount" Value = 0>
<Input Type="Hidden" Size="4" Name="MoveLast" Value = 0>
<Input Type="Hidden" Size="4" Name="MoveFirst" Value = 0>
</Form></BODY>

<Script Language = "VBScript">

Sub cmdDown_OnClick
'Set Values in Form Input Boxes and Submit Form
	Document.Form.MoveAmount.Value = -1
	Document.Form.Submit
End Sub

Sub cmdUp_OnClick

	Document.Form.MoveAmount.Value = 1
	Document.Form.Submit

End Sub

Sub cmdFirst_OnClick

	Document.Form.MoveFirst.Value = 2
	Document.Form.Submit

End Sub

Sub cmdLast_OnClick

	Document.Form.MoveLast.Value = 3
	Document.Form.Submit

End Sub
</Script></HTML>

Up Top of Page
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.