SQL way of getting last recID

[James Kleinschmidt]

Thanks a lot. This will work just fine.

Jim
On Mar 22, 2004, at 3:07 PM, Ken Ray wrote:

>> 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/