SQL Insert Question

Yes. There are two ways.

The first way (preferred) is to use the getGeneratedKeys method of Statement. (Please note that PreparedStatement extends Statement so the same method is in all three Statement types). This method returns a ResultSet of all the keys that were generated.

The second way and not preferred but if the database/driver does not support the first one what else are you going to do is to select it yourself.

Assuming the key is numeric then something like.

SELECT MAX(yourkeycolumnname) FROM yourtablename

jes1789a at 2007-7-13 6:19:18 >

> Thanks

> Im using Access and getGeneratedKeys() doesnt seem to

> work, even when using executeUpdate( query,

> Statement.RETURN_GENERATED_KEYS )

>

> but the 2nd option works fine,

>

> Thanks

The other related idea is if the auto generated key is a surrogate key then select the "real key" and get the generated key from that.

Example

tblCustomer

id autonumber

firstname varchar

lastname varchar

customersince datetimestamp

So there is a data key formed out of the last three fields but you have a surrogate key using the first field for conveinence elsewhere.. you can of course select based on a WHERE of the last three columns to get the id.

Personally I think this is an appropriate use of a surrogate key... if your data is not unique in some other way I personally feel that there is something wrong with your design. This comment will likely incite debate as it usually does but in my opinion is is still an accurate statement.

jes1789a at 2007-7-13 6:19:18 >