Refresh Method

Applies To

Description

Forces an immediate update of the dynaset given the current Connect, DatabaseName, and SQL properties.

Forces an immediate update of the dynaset by reexecuting the SQL statement in the SQL statement object.

Usage

oradynaset.Refresh
oradynaset.DbRefresh  
orasqlstmt.Refresh
orasqlstmt.DbRefresh

Remarks

This method cancels all edit operations (Edit and AddNew methods), executes the current contents of the SQL statement buffer, and moves to the first row of the resulting dynaset. Any dynaset objects created before issuing the Refresh method, including bookmarks, record counts, and field collections, are considered invalid. The OraConnection and OraSession objects associated with the previous dynaset remain unchanged.

Performing a refresh operation with this method can be more efficient than refreshing with a data control. This method also lets you execute a modified SQL statement without creating a new dynaset or OraSQLStmt object.

The preferred refresh methods when changing parameter values are oradynaset.Refresh or orasqlstmt.Refresh, because required database operations are minimized (SQL parsing, binding, and so on). This can improve performance when only parameter values have changed.

If you call the Refresh method after assigning an invalid SQL statement to the SQL property of a dynaset or SQL statement object, these objects remain valid. However, a dynaset in this state does not permit any row or field operations. Bound controls also exhibit unusual behaviors similar to those that occur when the standard Visual Basic data control RecordSource is set to an invalid SQL statement at run time and then refreshed.

You can regain the normal dynaset and SQL statement operations by refreshing the object with a valid SQL statement. The Refresh method treats Null or empty SQL statements as invalid.

Examples

Refresh Method Example (OraDynaset)

This example demonstrates the use of parameters, the Refresh method, and the SQL property to restrict selected records. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables 
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDynaset 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 'Create a parameter with an initial value.
 OraDatabase.Parameters.Add "job", "MANAGER", 1
 
 'Create the OraDynaset Object.
 Set OraDynaset =OraDatabase.CreateDynaset("select * from emp where job=:job",0&)
 
 'Notice that the SQL statement is NOT modified.
 MsgBox OraDynaset.SQL
 
 'Currently, OraDynaset only contains employees whose job is MANAGER.
 'Change the value of the job parameter.
 
 OraDatabase.Parameters("job").Value = "SALESMAN"
 
 'Refresh the dynaset.
 OraDynaset.Refresh
 
 'Currently, OraDynaset only contains employees whose job is SALESMAN.
 'Notice that the SQL statement is NOT modified.
 MsgBox OraDynaset.SQL
 
 'Remove the parameter.
 OraDatabase.Parameters.Remove ("job")
 
 End Sub

Refresh Method Example (OraSQLStmt)

This example demonstrates the use of parameters, the Refresh method, and the SQL property for the . object. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraSqlStmt As OraSQLStmt 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 OraDatabase.Parameters.Add "EMPNO", 7369, 1
 OraDatabase.Parameters("EMPNO").ServerType = 2  'ORATYPE_NUMBER  
 OraDatabase.Parameters.Add "ENAME", 0, 2
 OraDatabase.Parameters("ENAME").ServerType = 1  'ORATYPE_VARCHAR2  
 
 Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName (:EMPNO," & _
              ":ENAME); end;", 0&) 
 
 'Notice that the SQL statement is NOT modified.
 MsgBox OraSqlStmt.SQL
 
 'Should display SMITH
 MsgBox OraDatabase.Parameters("ENAME").Value  
 
 'Change the value of the empno parameter.
 OraDatabase.Parameters("EMPNO").Value = 7499
 
 'Refresh the dynaset.
 OraSqlStmt.Refresh
 
 'Should display ALLEN
 MsgBox OraDatabase.Parameters("ENAME").Value   
 
 'Notice that the SQL statement is NOT modified.
 MsgBox OraSqlStmt.SQL 
 
 'Remove the parameter.
 OraDatabase.Parameters.Remove ("job")
 
 End Sub