FindFirst, FindLast, FindNext, and FindPrevious Methods

Applies To

OraDynaset Object

Description

Find the indicated rows in the dynaset that matches the FindClause. The FindClause can be any valid WHERE clause without the WHERE. If the current FindClause matches the last clause from the previous find operation, then the current FindClause is not parsed again.

These methods move the current row directly to a matched row without calling any advisories except when the matched row is reached. If a matching row cannot be found, the NoMatch property is set to True, and the current row remains the same.

Usage

oradynaset.FindFirst FindClause  
oradynaset.FindLast FindClause  
oradynaset.FindNext FindClause 
oradynaset.FindPrevious FindClause  

Remarks

The following types of expressions can be used in the FindClause:

• Simple queries, such as "deptno = 20"

• Queries involving complex expressions, such as "sal + 100 > 1000".

• SQL function calls, such as "UPPER(ename) = 'SCOTT' " or "NVL(comm, 0) = 0".

• Subqueries, such as "deptno in (select deptno from dept)".

The SQL LIKE operator does not work in multiple byte languages. Table or synonym DUAL is required in the user's schema. Date values are retrieved and compared in Visual Basic format, which is the format specified in the Control Panel. Therefore, date comparisons fail if any other format such as the default Oracle format, DD-MON-YYYY is used.

The SQL function TO_CHAR (date, fmt) cannot be used because the first argument must be a date value in native Oracle format, and OO4O only handles 'string dates'.

The SQL function TO_DATE converts a string to a date, but OO4O converts it back to a string in Visual Basic format, as previously described, and the comparison may still fail.

The FindPrevious and FindLast methods in a NO_CACHE dynaset do not work; NoMatch is set to True.

Note: To avoid raising an error, check for EOF or BOF before calling a Find method.

Examples

This example demonstrates the use of the FindFirst, FindNext, FindPrevious methods. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()     
 
  Dim OraSession As OraSession 
  Dim OraDatabase As OraDatabase 
  Dim OraDynaset As OraDynaset 
  Dim OraFields As OraFields
  Dim FindClause As String 
 
  Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
  Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "SCOTT/TIGER", 0&) 
  Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where empno" & _
                 ">= 7654 and empno <= 7844 ", ORADYN_NO_BLANKSTRIP) 
  
  Set OraFields = OraDynaset.Fields 
 
  OraDynaset.MoveFirst 
  
  'FindClause for job as MANAGER
  FindClause = "job LIKE '%GER'" 
 
  OraDynaset.FindFirst FindClause 
 
  'NoMatch property set to true , if no rows found
  If OraDynaset.NoMatch Then 
    MsgBox "Couldn't find rows " 
  else
    MsgBox OraFields("ename").Value  ' Should display BLAKE 
 
    OraDynaset.FindNext FindClause 
    MsgBox OraFields("ename").Value  ' Should display CLARK 
 
    OraDynaset.FindPrevious FindClause 
    MsgBox OraFields("ename").Value  ' Should display BLAKE
 
   endif
 
End Sub