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

Move Method Example

This example uses the Move method to position the record pointer based on user input. 

Public Sub MoveX()

	Dim rstAuthors As ADODB.Recordset
	Dim strCnn As String
	Dim varBookmark As Variant
	Dim strCommand As String
	Dim lngMove As Long

	' 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 allow use of 
	' AbsolutePosition property.
	rstAuthors.CursorLocation = adUseClient
	rstAuthors.Open "SELECT au_id, au_fname, au_lname, city, state " & _
		"FROM Authors ORDER BY au_lname", strCnn, , , adCmdText
		

	rstAuthors.MoveFirst

	Do While True
		' Display information about current record and
		' ask how many records to move.

		strCommand = InputBox( _
			"Record " & rstAuthors.AbsolutePosition & _
			" of " & rstAuthors.RecordCount & vbCr & _
			"Author: " & rstAuthors!au_fname & _
			" " & rstAuthors!au_lname & vbCr & _
			"Location: " & rstAuthors!City & _
			", " & rstAuthors!State & vbCr & vbCr & _
			"Enter number of records to Move " & _
			"(positive or negative).")

		If strCommand = "" Then Exit Do

		' Store bookmark in case the Move goes too far
		' forward or backward.
		varBookmark = rstAuthors.Bookmark

		' Move method requires parameter of data type Long.
		lngMove = CLng(strCommand)
		rstAuthors.Move lngMove

		' Trap for BOF or EOF.
		If rstAuthors.BOF Then
			MsgBox "Too far backward! " & _
				"Returning to current record."
			rstAuthors.Bookmark = varBookmark
		End If
		If rstAuthors.EOF Then
			MsgBox "Too far forward! " & _
				"Returning to current record."
			rstAuthors.Bookmark = varBookmark
		End If
	Loop
	rstAuthors.Close

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 Move.asp. You can view the result in any client browser.

Try entering a letter or non integer to see the error handling work. 

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>
<HTML><HEAD>
<TITLE>ADO 1.5 Move Methods</TITLE></HEAD>
<BODY> 
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center>
<H3>ADO Move Methods</H3>

<%
 '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 number of user moves this session
'Increment by amount in Form
Session("Clicks") = Session("Clicks") + Request.Form("MoveAmount")
Clicks = Session("Clicks")
'Move to last known recordset position plus amount passed
'by Form Post method
RsCustomerList.Move CInt(Clicks)

'Error Handling
	If RsCustomerList.EOF Then
			Session("Clicks") = RsCustomerList.RecordCount
			Response.Write "This is the Last Record"
			RsCustomerList.MoveLast
		Else If RsCustomerList.BOF Then
			Session("Clicks") = 1
			RsCustomerList.MoveFirst
			Response.Write "This is the First Record"
		End If
	End If

%>

<H3>Current Record Number is <BR>
<% If Session("Clicks") = 0 Then 
Session("Clicks") = 1
End If
Response.Write(Session("Clicks") )%> 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 = "   >">
<H5>Click Direction Arrows for Previous or Next Record
<BR> Click Move Amount to use Move Method
Enter Number of Records to Move + or - </H5>

<Table>

<Form Method = Post Action="Move.asp" Name=Form>

<TR><TD><Input Type="Button" Name = Move Value="Move Amount "></TD><TD></TD><TD>
<Input Type="Text" Size="4" Name="MoveAmount" Value = 0></TD><TR>
</Form></Table></Center>

</BODY>

<Script Language = "VBScript">

Sub Move_OnClick
' Make sure move value entered is an integer
If IsNumeric(Document.Form.MoveAmount.Value)Then
	Document.Form.MoveAmount.Value = CInt(Document.Form.MoveAmount.Value)
	Document.Form.Submit
Else
	MsgBox "You Must Enter a Number", ,"ADO-ASP Example"
	Document.Form.MoveAmount.Value = 0
End If

End Sub

Sub cmdDown_OnClick

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

End Sub

Sub cmdUp_OnClick

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

End Sub

</Script>
</HTML>

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