couter field # of last inserted record

Anon

How do you know the generated Number of the inserted record in ODBC, SQL server, Access, ... (In ASP is easier)

(I know there is a function in MySQL to know this, but I want tio know how to have the same result in MSSQL or ODBC)

supposing there is a database with 2 fields:

ID int,couter
and NAME varchar

$sql="insert into test (name) values ('Joe')"
.
execute
.

$newID = ???????

How can I do this?

Anon

I want replies to anything in this thread!

Is there a way to do this?

I had a similar problem with PostgreSQL - there's no pg_insertid like mysql_insertid!

I eventually found that I could get the last oid (pg_getlastoid) from the connection, then use that to query the database I just inserted into where OID matches, and then get the serial key # from the record.

Clumsy at best, and not very portable!

-Ben

Anon

SQL Server can be done easily, access less easily (you need to do a recordset insert)

If you tell me the mySQL method...

"SET NOCOUNT ON;
INSERT tbl (fields)
VALUES (values);SELECT @@Identity as NewID"

recordset("newID") will then hold the newly generated ID

how to do this in PHP mySQL though?

Anon

mysql_insert_id (PHP3 , PHP4 )

Get the id generated from the previous INSERT operation

int mysql_insert_id ([int link_identifier])

mysql_insert_id() returns the ID generated for an AUTO_INCREMENTED field. It will return the
auto-generated ID returned by the last INSERT query performed using the given link_identifier. If
link_identifier isn’t specified, the last opened link is assumed.

Anon

This SQL statement does NOT work for MS SQL & PHP!
At least NOT for version MS SQL 6.5. It responds with "syntax error near ';'" message. When you exclude ;s commads work in MS SQL console, but from PHP it return only number '1', the same what will return INSERT command ALONE.
Does somebody know what's wrong? THIS IS VERY SERIOUS PROBLEM FOR ME.
Thanx.