I have a table that has a primary key that is generated automatically by the database, so when I write an SQL statement in my program to insert information into the table, I insert values for every field but the primary key field.
The problem here is that in my program I do not know the value of the primary key field. I was wondering if there was any way I could get an INSERT statement to return the value of the primary key field for the record it has just inserted?
The only way I've found of doing this for the moment is by opening the database connection again, once the INSERT statement has been performed, and doing a SELECT query that selects the primary key from any records that match all of the other information I created the record with. Of course if there is two records with exactly the same information for all of the other fields then I'm screwed. In my application that shouldn't happen, but I don't like that the possibility is there.