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

Use WHERE clause as follows:

WHERE RECNO()=1

Answer 2

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)

Answer 3

If you run the above query in Foxpro, every row will be updated as you state because of the WHERE condition that you are using.

When you specify " Where column1 = '' ", then every row will be effected. Try specifying a value in the condition such as " Where column1 = 'somevalue' " or " Where EMPTY(column1) "

Answer 4

Generally speaking, this is what primary keys are for. Whether its indexed or not, there should be a single field that uniquely identifies each of your records, and allows you to target an update to that particular record and not the entire set.

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

If the field doesn't have a primary key, it's strike #2 on the this was horribly designed and should be abandoned, right after you can't insert new rows. Unless this is a theoretical exercise, there are far better tools to accomplish whatever it is that you're after.

---

That said, for the particular example this is one of those rare instances where mucking about with SQL actually makes your life harder.

FoxPro at its heart, is not a set-based language like SQL. Rather, it's a specialized language focusing on data operations, using the DBF format. For operations where you don't want to deal with entire sets, and for some reason are still programming in FoxPro, you can most easily accomplish this by embracing the xBase roots and running with it.

SELECT HM_LIST
LOCATE FOR FOR HM_STATION='' AND HM_TIME='' AND HM_USER=''

IF FOUND()
  REPLACE IN HM_LIST                ;
      HM_DATE WITH DATE(2014,5,22)  ;
    , HM_STATION WITH "CM_PC"       ;
    , HM_TIME WITH "17:06"          ;
    , HM_USER WITH "TEST"       
ENDIF