Update Top in Visual FoxPro

Question

I'm accessing a Visual FoxPro Table (DBF File) via VFP OleDB Provider in a C# Application. Is there an equivalent of UPDATE TOP (MS SQL) in VFP?

This is my current Query:

UPDATE HM_LIST
SET 
  HM_DATE=DATE(2014,5,22) , 
  HM_STATION="CM_PC" , 
  HM_TIME="17:06" , 
  HM_USER="TEST" 
WHERE 
  HM_STATION='' 
  AND HM_TIME='' 
  AND HM_USER=''

The problem is, all rows are matching to my parameters. But I want to update only one of those matching rows.

There is no Primary_Key. I can't use INSERT.

Table

Answer 1

With the hint of Oleg I found a workaround for the missing primary key.

But it needs two Querys

First select the Record Number alias RECNO of the matching rows.

SELECT RECNO() FROM table_name WHERE foo=''

Now read the first row of the Result (this is the "id" of the row)

Save it as a variable (int row_id) and put after WHERE Statment of the UPDATE Query only following line : "RECNO() ="+row_id

Example :

var MyOleDBCommand = MyOleDBConnection.CreateCommand();
MyOleDBCommand.CommandText = "SELECT RECNO() FROM table_name WHERE foo=''";

int row_id = -1;

/** Search for some matching rows **/
using(var reader = MyOleDBCommand.ExecuteReader()){
 // Check if something was found
 if(reader.HasRows){
   reader.Read(); // Read only the first row (or use a for-loop if you need more then 1)
   row_id = (int)reader.GetDecimal(0);
 }
}
/** If a matching row was found **/
if(row_id > -1){
  MyOleDBCommand.CommandText = "UPDATE table_name SET foo='bar' WHERE RECNO()="+row_id;
  if(MyOleDBCommand.ExecuteNonQuery()>0){
    //Successfully Updatet
  }}
}

Remarks: RECNO has the Type Decimal, so you have to use GetDecimal(0) (see sample code)