Using table with CursorAdapter - how can I get the ID of latest record?

Hi,

I have an application and I use CursorAdapters for all tables.
I have the following problem: I have to create a new record in a table,

find it't id (integer auto-increment) and pass it to another module

Specifically, user create a master record, then (before they save and
exit) they can also create one or more child records in several other
tables. So when they open the forms for the child tables, I havre to
know the ID for the master record, and pass it to the form handling the

child table, to set it as foreign key.

The problem is I have troubles finding the ID. I use a very odd method
right now, which works, but I'm not happy with it.

Can anyone give me so thoughts on how to get this done in an elegant
way?

Thanks,
Bogdan

borisb
Thu Oct 13 06:53:01 CDT 2005

bzamfir@gmail.com wrote:
> Hi,
>
> I have an application and I use CursorAdapters for all tables.
> I have the following problem: I have to create a new record in a table,
>
> find it't id (integer auto-increment) and pass it to another module
>
> Specifically, user create a master record, then (before they save and
> exit) they can also create one or more child records in several other
> tables. So when they open the forms for the child tables, I havre to
> know the ID for the master record, and pass it to the form handling the
>
> child table, to set it as foreign key.
>
> The problem is I have troubles finding the ID. I use a very odd method
> right now, which works, but I'm not happy with it.
>
> Can anyone give me so thoughts on how to get this done in an elegant
> way?
>
> Thanks,
> Bogdan
>
>

Something like that:
Field1 is AutoIncrement field and you put it into the selectcmd property
of the CA.

INSERT INTO MyCA (Field2, Field3...FieldN) VALUES (val1,val2...valN)
IF TableUpdate(1, .t., "MyCA")
MyCursorAdapterObject.CursorRefresh()
*** Find the record you just added (use see, locate, goto etc.)
m.lnNewId = Field1
ENDIF

Gvi2
Thu Oct 13 10:37:21 CDT 2005


"borisb" <borisb@mail.bg> wrote in message news:ugCsry#zFHA.720@TK2MSFTNGP15.phx.gbl...
> bzamfir@gmail.com wrote:
> > Hi,
> >
> > I have an application and I use CursorAdapters for all tables.
> > I have the following problem: I have to create a new record in a table,
> >
> > find it't id (integer auto-increment) and pass it to another module
> >
> > Specifically, user create a master record, then (before they save and
> > exit) they can also create one or more child records in several other
> > tables. So when they open the forms for the child tables, I havre to
> > know the ID for the master record, and pass it to the form handling the
> >
> > child table, to set it as foreign key.
> >
> > The problem is I have troubles finding the ID. I use a very odd method
> > right now, which works, but I'm not happy with it.
> >
> > Can anyone give me so thoughts on how to get this done in an elegant
> > way?
> >
> > Thanks,
> > Bogdan
> >
> >
>
> Something like that:
> Field1 is AutoIncrement field and you put it into the selectcmd property
> of the CA.
>
> INSERT INTO MyCA (Field2, Field3...FieldN) VALUES (val1,val2...valN)
> IF TableUpdate(1, .t., "MyCA")
> MyCursorAdapterObject.CursorRefresh()
> *** Find the record you just added (use see, locate, goto etc.)
> m.lnNewId = Field1
> ENDIF
>

In VFP9, you can get ID value automatically using CA properties
InsertCmdRefreshFieldList (= your_ID_field_name) and InsertCmdRefreshCmd
(I'd like here to be just "SELECT TOP 1 GETAUTOINCVALUE()", but there is a problem with
required FROM clause in Fox's SQL dialect, so I use a dummy one-record table t_ainc
and "SELECT GETAUTOINCVALUE() AS new_id FROM t_ainc".
Anyway, it works - after TABLEUPDATE() I have my ID's updated!)

bzamfir
Thu Oct 13 15:30:09 CDT 2005

Thanks, I'll try it and see how it works.

Regards,
Bogdan