A Beginners Guide to ADO

[Enrico Maria Giordano]

IF oRs:eof
oRs:MoveFirst()

You can't move the record pointer when the recordset is empty. This will result in a runtime error.

EMG

[Enrico Maria Giordano]

I have no problem with the numeric values .. where my string breaks is passing the file_name string. Don't know why .. but there is no ryme or reason for the failure .. never happends on the same record .. so I can not find any specific reasons .. just my gut instinct .. VM gets taxed or there is a memory leak in repeating the UPDATE text ( looping thru thousands of records ) .. and then it just breaks...

Can I have a minimal PRG and an MDB to test here?

EMG

[jlcapel]

Rip,


About method Find of RecordSet (from BOL):

Find Method
Searches a Recordset for the row that satisfies the specified criteria. Optionally, the direction of the search, starting row, and offset from the starting row may be specified. If the criteria is met, the current row position is set on the found record; otherwise, the position is set to the end (or start) of the Recordset.

Syntax
Find (Criteria, SkipRows, SearchDirection, Start)
Parameters
Criteria
A String value that contains a statement specifying the column name, comparison operator, and value to use in the search.
SkipRows
Optional. A Long value, whose default value is zero, that specifies the row offset from the current row or Start bookmark to begin the search. By default, the search will start on the current row.
SearchDirection
Optional. A SearchDirectionEnum value that specifies whether the search should begin on the current row or the next available row in the direction of the search. An unsuccessful search stops at the end of the Recordset if the value is adSearchForward. An unsuccessful search stops at the start of the Recordset if the value is adSearchBackward.
Start
Optional. A Variant bookmark that functions as the starting position for the search.
Remarks
Only a single-column name may be specified in criteria. This method does not support multi-column searches.

The comparison operator in Criteria may be ">" (greater than), "<" (less than), "=" (equal), ">=" (greater than or equal), "<=" (less than or equal), "<>" (not equal), or "like" (pattern matching).

The value in Criteria may be a string, floating-point number, or date. String values are delimited with single quotes or "#" (number sign) marks (for example, "state = 'WA'" or "state = #WA#"). Date values are delimited with "#" (number sign) marks (for example, "start_date > #7/22/97#") and can contain hours, minutes and seconds to indicate time stamps but should not contain milliseconds or errors will occur.

If the comparison operator is "like", the string value may contain an asterisk (*) to find one or more occurrences of any character or substring. For example, "state like 'M*'" matches Maine and Massachusetts. You can also use leading and trailing asterisks to find a substring contained within the values. For example, "state like '*as*'" matches Alaska, Arkansas, and Massachusetts.

Asterisks can be used only at the end of a criteria string, or together at both the beginning and end of a criteria string, as shown above. You cannot use the asterisk as a leading wildcard ('*str'), or embedded wildcard ('s*r'). This will cause an error.

Note An error will occur if a current row position is not set before calling Find. Any method that sets row position, such as MoveFirst, should be called before calling Find.
Note If you call the Find method on a recordset, and the current position in the recordset is at the last record or end of file (EOF), you will not find anything. You need to call the MoveFirst method to set the current position/cursor to the beginning of the recordset.
See Also
Visual Basic Example

Index Property | Optimize Property—Dynamic (ADO) | Seek Method

Applies To: Recordset Object
© 1998-2003 Microsoft Corporation. All rights reserved.

And a suggestion of simulating xBase LOCATE command into a recordset:

Code: Select all  Expand view

METHOD Locate ( cCriteria , lFromStart) CLASS tAdoDbf

       LOCAL oE
       LOCAL lResult
       LOCAL nSearchDi

       ::cCriteria := cCriteria
       ::nSearchDirection := adSearchForward

       DEFAULT lFromStart := .T.

       TRY
           ::oRs:Find(cCriteria,0,::nSearchDirection , IIF( lFromStart, adBookmarkFirst, adBookmarkCurrent ))
           lResult := !::oRs:EOF()
       CATCH oE
           ShowError( oE, Self, ::lShowError )
           lResult := .F.
       END

RETURN lResult

METHOD Continue() CLASS tAdoDbf

       LOCAL oE
       LOCAL lResult

       TRY
           IF !::oRs:EOF()
              ::oRs:Find(::cCriteria,1,::nSearchDirection, adBookmarkCurrent)
              lResult := !::oRs:EOF()
           ELSE
              lResult := .F.
           ENDIF
       CATCH oE
           ShowError( oE, Self, ::lShowError )
           lResult := .F.
       END

RETURN NIL

METHOD EndLocate() CLASS tAdoDbf
       ::cCriteria := ""
       ::nSearchDirection := -1
RETURN NIL


About delimiters (single, double quotes) just tell you that depends on Sql Motor. For example in Sql Server you may change delimiters for your convenience. Normally you'll fight against apostrophes inside strings. So... if you try to send this Sql sentence:

Code: Select all  Expand view

LOCAL a
LOCAL b
a := "America's Cup in Valencia - Spain"
b := "UPDATE mytable Set MyFiled = '"+ a +"';"
oConnection:Execute(b)

For sure you'll have an error. But if you set other type of delimiter to Sql Server you'll get out this problem. For example:

Code: Select all  Expand view

LOCAL a
LOCAL b
a := "America's Cup in Valencia - Spain"
b := [UPDATE mytable Set MyFiled = "]+ a +[";]
oConnection:Execute("SET QUOTED_IDENTIFIER OFF")
oConnection:Execute(b)

In this case we have set delimiters to double quotes (for sql server).

And about how to determine if a recordset is empty or not the only way I was sure that a recordset is empty is :

Code: Select all  Expand view

if ors:bof and ors:eof
  MsgInfo("Empty")
endif

Other ways are typecursor/provider/locationcursor dependant.

Regards,
José Luis Capel

[James Bott]

Rick,

>SQL can only interpret single quotes to pass as values :

Double quotes work for me, at least with Access. This works here:

oRS:Open( [SELECT * FROM Customers WHERE companyname="Eastern Connection"], "Provider=Microsoft.Jet.OleDB.4.0;Data Source=Northwind.mdb" )

James

[dbzap]

Masters

Any idea for compact a mdb, like "Compact & repair" but with SQL instruccion ?

tnks

Spanish ON------------------

Maestros,
Alguna idea de como compactar una base mdb, como "Compactar y reparar" del Access, pero como instruccion SQL ?

Gracias.

[Enrico Maria Giordano]

Code: Select all  Expand view

FUNCTION MAIN()

    LOCAL oJro := CREATEOBJECT( "JRO.JetEngine" )

    LOCAL cConStr1 := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydb.mdb"
    LOCAL cConStr2 := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydb2.mdb"

    oJro:CompactDatabase( cConStr1, cConStr2 )

    RETURN NIL

EMG

[dbzap]

OK, any class to include or "pure & hard code"

Spanish ON

Ok, alguna clase a incluir o "codigo puro y duro" ?


PD: Sql instruction via ODBC, exists ?

[Enrico Maria Giordano]

Pure xHarbour code.

EMG