How to retrieve a PK from table using cursoradapter

[mHeit]

How do you retrieve a Primary Key from a table that has been automatically generated using auto increment? The auto generated PK is the only field that is unique.

Using a cursor adapter to insert a new record, how can I retrieve the Primary key so that it can be used as a foreign key in another table.?

 

[Mike Lewis]

Assuming that this is a native VFP table, you simply look at the value of the field within the cursor. For example, if the alias of the cursor generated by the cursoradapter is MyCursor, and the name of the PK field is MyPK, you simply reference MyCursor.PK.

If you are working with something other than native VFP, the solution depends on which back end it is.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[mHeit]

Thanks for the quick reply.

Just wanting to know if there is another way to do this. I got vfp 8 using native datasource. I know vfp 9 has a function that automatically returns the pk when you create a new record using the CA. Version 8 doesn't have that.

I start by creating an empty cursor to create a new record. The user fills in the appropriate fields and when done does a TABLEUPDATE(). Still don't know what autogenerated PK is.

To retrieve the PK I do a CA.cursorfill(.t.). I don't use cursorrefresh() because I now need to requiry the table with the order_id in the WHERE clause using the before cursorfill() method. (Don't know what that is till user enters that.)
Now, I can retrieve the autogenerated PK so I can apply that to the other tables.

Just seems like a lot of steps to retrieve the PK. Is that just a failing of the older version that they've now fixed? Or is there a quicker way to do this?

 

[Mike Lewis]

Surely, at the point at which you create the blank record for the cursor, you should see the PK (assuming this is mapped to an AutoInc field). If my understanding is correct, this is available even before you do the TABLEUPDATE().

If that's not the case, I would have to experiment a bit to figure out the answer. But I'm pretty sure you don't need to do another cursorfill.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[mHeit]

This is the info I found on Autoincrement function:

Buffered Tables
Visual FoxPro does not perform any autoincrementing management relative to buffered tables. All appended and inserted records have autoincrementing values generated regardless of whether table or row buffering is active. In the case of buffering, when the TABLEUDPATE( ) function is called, the base table is updated with the previously generated value. If the update does not occur, for example, the TABLEREVERT( ) function is called, any autoincrementing values that are generated are discarded, thus resulting in gaps in the sequence.

When is that value "previously updated" so it can be retrieved before the tableupdate()?

Thanks

 

[Mike Lewis]

When is that value "previously updated" so it can be retrieved before the tableupdate()?

As soon as you add the new record.

Re the passage that you quoted. I understand that to mean merely that if you append a new record, then revert it, there will be a gap in the numbering sequence. It doesn't say that the new PK won't be available as soon as you add the record.

Put it this way. Suppose you do the following:

- Set the table's buffer mode to > 1

- APPEND BLANK

- WAIT WINDOW PrimKey
(where PrimKey is the name of your PK field)

I believe the wait window will show the newly generated PK value. That is the value that you want. If I'm right, that will prove that the key is available as soon as you do the APPEND BLANK. You don't have to wait for the TABLEUPDATE().

Perhaps you could try it to see if it works.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[andyrice]

I am using CursorAdapters against FoxPro tables.
It seems if you edit the table directly, the autoinc value is available after an append blank.
But if you create a CursorAdapter to the table, and edit the cursorAdapter, the autoinc value is not available until you do a TABLEUPDATE().
I use the AfterInsert Method of the CursorAdapter class to capture the autoinc value:

PROCEDURE AfterInsert
    LPARAMETERS tcFldState, tlForce, tcInsertCmd, tlResult
    * You must previously create iIdentity as a Class Property.
   THIS.iIdentity =  yourTable.yourAutoIncField
ENDPROC

In your code:
1. Do the TABLEUPDATE().
2. Check the value of yourCursorClass.iIdentity.

HTH,
Andy Rice

Andy Rice
San Diego, CA

 

[Mike Lewis]

Andy,

if you create a CursorAdapter to the table, and edit the cursorAdapter, the autoinc value is not available until you do a TABLEUPDATE().

That wasn't my understanding, but it looks like you have more experience of this than I do, so I will defer to your superior knowledge.

I use the AfterInsert Method of the CursorAdapter class to capture the autoinc value

That'll be the answer that MHeit wanted.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[andyrice]

Mike:
I have only used Autoinc fields with a CursorAdapter.
I was deferring to YOUR superior knowledge of how they worked on a regular table!

Thanks,


Andy Rice
San Diego, CA

 

[mHeit]

Thanks very much for the help.

That saved me a bunch of code that I can now erase. That was exactly the answer I was looking for.