SQL way of getting last recID

[Ken Ray]

> It seems like such a common problem surely someone has a 
> solution that I am just missing, or a different way of approaching 
> this?

The way to do it (or the way that works for me) is to use cursors. It's not
a one-liner like INSERT, but it *does* let you get the RecID back. Here's an
example (I'm using MetaCard so you'll have to translate to your dev
environment, and watch the line wraps):

-- Assumes a simple table called People that has FirstName,
-- MiddleName and LastName fields. myDBRef holds the reference
-- to the database 

put "" & tab & "Ken" & tab & "E" & tab & "Ray" into tRecData
put Valentina("SQLSelect",myDBRef,"SELECT RecID,* FROM People WHERE
RecID=0") into tCurs
get Valentina("Cursor_AddRecord",tCurs,tRecData)
put Valentina("Cursor_GetField",tCurs,"RecID") into tNewRecID
get Valentina("Cursor_Remove",tCurs)


Basically, the SELECT statement includes all the fields in the People table,
INCLUDING the RecID field at the beginning. So the cursor has 4 fields in
it: RecID, FirstName, MiddleName, LastName. So the record that needs to be
added has to have 4 fields as well, so the first field is sent into the
Cursor_AddRecord as empty. Valentina will automatically assign it the proper
record ID number, so the next step is to ask for it with Cursor_GetField.
Finally, remove the cursor.

Hope this helps,

Ken Ray
Sons of Thunder Software
Email: kray at sonsothunder.com
Web Site: http://www.sonsothunder.com/