@@Identitiy in vfp

[Mark Vroom]

I need some help on inserting records from vb.net into Visual FoxPro. In
VFP8 we (finally) got primary key type fields. That solved some issues with
the oledb driver, the driver is now able to determine which field the
primary key field is. Unfortunately I still cannot find a property like (for
example) @@identify like in sql server to determine the primary key value of
a new inserted record.

Off course I can get the complete dataset again in vb.net, but when
inserting a few records it's not good for performance and bandwith reasons
to retrieve the complete dataset again. When connecting from vb.net to a sql
server, the sql server is able to return the values of the inserted records
by adding ;select @@identity to the InsertCommand. Does anyone know if this
is possible now in some way while inserting into vfp?

The older solution to this problem was to make a separate table in vfp in
which all primary key values of all tables in the database were stored. Just
ask this table by a vfp stored procedure for a new primary key, add the
value to the vb.net dataset and then execute the insert function to vfp. As
a matter of fact, by this way the primary key field is 'dictated' by vb.net.
FoxPro 9 has made some progression by the new primary key field type, but we
can't supply a value in this field type from vb.net anymore. So the old
solution does not work anymore when using the new field type. (Some you win,
some you loose ;-)

Greets,
Mark Vroom.

 

[Cindy Winegarden]

Hi Mark,

Visual FoxPro has always had Primary and Candidate indexes that limit values
to being unique. What was new in VFP8 was not a "primary key" data type but
an auto-incrementing data type that is useful as a primary key. It's
possible to have more than one AutoInc field in a table. I wonder what the
OLE DB data provider does with that when determining which is the primary
key - that is, the value you use to match up parent and child records.

Unfortunately, there is no way to determine the PK of a newly added record
other than requerying the data. Because of this, many VFP developers have
continued to use stored procedures to generate unique values for their PK
fields.

You say that it's not good for performance to requery the "complete"
dataset. How many records are you retrieving? In any client-server situation
it's always wise to get only the minimum number of records that you actually
need.