Move Method Example (VBScript)

Microsoft ActiveX Data Objects (ADO)

Move Method Example (VBScript)

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

Use the following example in an Active Server Page (ASP). To view this fully functional example, you must have the data source AdvWorks.mdb (installed with the SDK) located at C:\mssdk\samples\dataaccess\rds. This is a Microsoft Access database file.

Use Find to 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 browser.

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

<%@ Language=VBScript %>
<!-- #Include file="ADOVBS.INC" -->
<HTML>
<HEAD>
<TITLE>ADO Move Methods</TITLE>
<STYLE>
<!--
BODY {
    font-family: "MS SANS SERIF",sans-serif;
     }
.thead1 {
    background-color: #008080; 
    font-family: 'Arial Narrow','Arial',sans-serif; 
    font-size: x-small;
    color: white;
    }
.tbody { 
    text-align: center;
    background-color: #f7efde;
    font-family: 'Arial Narrow','Arial',sans-serif; 
    font-size: x-small;
     }
-->
</STYLE>
</HEAD>
<BODY> 
<H3>ADO Move Methods</H3>
<%
src = "C:\mssdk\samples\dataaccess\rds\advworks.mdb"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src

'Create and Open Connection Object
Set OBJdbConn = Server.CreateObject("ADODB.Connection") 
OBJdbConn.Open  sConnStr

'Create and Open Recordset Object
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConn
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>


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

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

<TR CLASS=thead1>
    <TD>Company Name</TD>
    <TD>Contact Name</TD>
    <TD>Phone Number</TD>
    <TD>City</TD>
    <TD>State/Province</TD>
</TR>

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

<TR CLASS=tbody>
  <TD> <%= RSCustomerList("CompanyName")%> </TD>
  <TD> <%= RScustomerList("ContactLastName") & ", " %> 
       <%= RScustomerList("ContactFirstName") %> </TD>
  <TD> <%= RScustomerList("PhoneNumber")%> </TD>
  <TD> <%= RScustomerList("City")%> </TD>
  <TD> <%= RScustomerList("StateOrProvince")%> </TD>
</TR> 
</TABLE>

<HR>
<Input Type=Button Name=cmdDown  Value="&lt;  ">
<Input Type=Button Name=cmdUp Value=" &gt;">
<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>

</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>