retrieving autonumber id 1

[TAMSIN]

Hi,
Sorry if this has been asked before, but I can't work it out:
I'm doing an insert into an access table, which has an autonumber id. after the insert, I wantto retrieve the ID which was allocated so that I can query back the new record.

I'm trying to do it like this (only one person will be updating so its ok to go for max(id)):

sQry = "insert into staff ...";
var upd = Server.CreateObject('ADODB.Command');
upd.ActiveConnection = "dsn=DB_Generic";
upd.CommandText = sQry;
upd.Execute();
var rstnewID = Server.CreateObject("ADODB.Recordset");
rstnewID.ActiveConnection = "dsn=DB_Generic";
rstnewID.Source = "SELECT max(staffid) FROM staff";
rstnewID.Open();
var newID = rstnewID.Fields.Item(0).Value;

But it seems to be picking up the max(id)before the record that was just inserted. Am I missing something?
thanks,
tamsin [sig][/sig]

 

[Guest_imported]

From what you've posted not clear whether you have executed
"Insert... " before retrieving max(id) or not. If you haven't - this is the problem.
So, if you could post a little bit more code here, it'd be easier to help.

 

[TAMSIN]

Thats pretty much all the code. Here it is in full:

if (String(Request("bt_insert")) != "undefined") {
	sQry = "insert into staff (name, job, dept, description, phone, email) values ('" + String(Request.Form("name")).replace(/'/g,"''") + "' , '" + String(Request.Form("job")).replace(/'/g,"''") + "', '" +  String(Request.Form("dept")).replace(/'/g,"''") + "', '" +  String(Request.Form("description")).replace(/'/g,"''") + "', '" +  String(Request.Form("phone")).replace(/'/g,"''") + "', '" + String(Request.Form("email")).replace(/'/g,"''") + "')";
}
if (sQry != "") {
  // execute the update
  var upd = Server.CreateObject('ADODB.Command');
  upd.ActiveConnection = "dsn=DB_Generic";
  upd.CommandText = sQry;
	try {
	  upd.Execute();

	} catch (e) {
		msg = "Error "+ e.description;
	}
if (msg == "") 
	{
var rstnewID = Server.CreateObject("ADODB.Recordset");
rstnewID.ActiveConnection = "dsn=DB_Generic";
rstnewID.Source = "SELECT max(staffid) FROM staff";
rstnewID.Open();
sAction = "Staff.asp?staffid=" + rstnewID.Fields.Item(0).Value;
Response.Redirect(sAction);
}

%>

sQry contains a string which is an insert statement, and then upd.Execute() does the insert. The insert is definitely happening because I can see the record once I go to another page and view a list of records.
Thanks,
Tamsin

 

[TAMSIN]

Fixed it now...
Don't really understand ADO, but changed from ADODB Command object to using connection.execute and then closing the connection. Seems to make the maxid available for the next query, so I'm getting the right record now.
Thanks anyway...

sQry = "insert into..."
var upd = Server.CreateObject('ADODB.Connection');
upd.Open("DB_Generic");
upd.Execute(sQry);
upd.Close;
var rstnewID = Server.CreateObject("ADODB.Recordset");
rstnewID.ActiveConnection = "dsn=DB_Generic";
rstnewID.Source = "SELECT max(staffid) FROM staff";
rstnewID.Open();