Getting autonumber from MS Access database

[wsexton]

I'm trying to get the autonumber of a record I just inserted into an Access database. Here's the code I'm using. I don't seem to be getting anything back and it doesn't seem to return an error, either.

my $sth1 = $dbh->prepare("select max(REQUESTNUMBER) from request_data");
$sth1->execute;
$error = $sth1->errstr;
my $id = $sth1->fetchrow_hashref();
$error = $sth1->errstr;
my $requestnumberid = $id{'REQUESTNUMBER'};

Requestnumber is the autonumber field. Any suggestions?

 

[PaulTEG]

Another way to get it back, assuming all your data is unique, is to SELECT it based on the data you've just writtent to the DB

SELECT REQUESTNUMBER from requestdata where
  field1='value1' AND
  ...
  ;

I've seen this before in Access, and can't remember what the outcome was, could be worth a google.

Does it work, when run from MS Query?

--Paul

cigless ...

 

[fishiface]

Both of these techniques seem subject to race conditions. Some databases have additional syntax for just this purpose. Informix uses

select dbinfo('sqlca.sqlerrd1') from table

to give the value of the last sequence number inserted in your session but I don't know if there is an equivalent in your database. Can you lock the table reliably?

f

"As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs."
--Maurice Wilkes