Monday, October 03, 2005 9:15 PM
I remembered today, with Ted's help, a technique which I really dig for retrieving the just-created textbase record when using ODBC Insert. 'Cause you don't get back any textbase-produced stuff like an autonumber RecordID or DateTimeEntered or anything, when using Inmagic ODBC to insert a record. There's just this kinda whistly sound the little integer-that-could makes as it tumbles its tumbleweedy way back from the database. "1" for "ayuh, I reckon we got ourselves a new record", and "not 1" for "they saw us comin' and Li'l Joe's been shot!" Or. Yeah. And that is the lamest wordplay ever in a title.
Okay so, I have used things like SELECT MAX(ID) FROM [tb] WHERE Title = ? to fetch what I just inserted, not yet knowing the ID, since it's produced in the BLACK BOX down there where the database is. And that's worked because it's unlikely that a field like Title, even when not unique, is going to repeat often, and since each new ID is an integer based on last-produced-integer +1, it's very very likely I'm going to get the record back that I just inserted.
Ted and Nathan have used unique fields or field combinations that have a statistically high unlikelihood of repeating. But still, it's bothered me that the method is not airtight. And today I remembered something Nathan had come up with: produce a GUID, stick it into the textbase along with the rest of the record, fetch record by GUID. So simple and effective. One needs to add a new field for it, but so what? And creating a string GUID in VB.NET is dead-easy: System.Guid.NewGuid.ToString().
Ahhh. Relax... the Andornot Way.