Easy question: how to know the ID of a new record?

[Rod]

Hi,

I have the following problem.

I am adding a new record in a table.
How can I know the ID provided by the database of the new record?

suppose my table has 3 attributes:
* ID: automatically calculated by the DB server when adding a new record
* Val1
* Val2

I am using the Microsoft SQL server DB and the following code:
$conn=......
$q="insert into t_mytable values ('value1','value2')";
$result=odbc_exec($conn, $q) or die("".odbc_errormsg());

Now I need to send an email with the ID of this record, but I have an error
when I do this:
echo odbc_result($result ,"ID");
here is the error:
Warning: No tuples available at this result index

So I can't get the ID, even if the new record has successfully been added in
the db!!
Actually, I can't get any values of the new field.

Do you have any idea??
Is there another way to know the ID of a new record?

Thanks and best regards

[Jon Kraft]

"Rod" <to**@toto.com> wrote:

I am adding a new record in a table.
How can I know the ID provided by the database of the new record?

I am using the Microsoft SQL server DB and the following code:
$conn=......
$q="insert into t_mytable values ('value1','value2')";
$result=odbc_exec($conn, $q) or die("".odbc_errormsg());

Hi Rod,

In MS SQL you have to do a select right after the insert in order to get
the last inserted ID:

$q="insert into t_mytable values ('value1','value2')";
$result=odbc_exec($conn, $q) or die("".odbc_errormsg());

$q2="select @@identity";
$result=odbc_exec($conn, $q2) or die("".odbc_errormsg());
// ....

Hope this helps;
JOn

[André Næss]

Jon Kraft:

"Rod" <to**@toto.com> wrote:

I am adding a new record in a table.
How can I know the ID provided by the database of the new record?

I am using the Microsoft SQL server DB and the following code:
$conn=......
$q="insert into t_mytable values ('value1','value2')";
$result=odbc_exec($conn, $q) or die("".odbc_errormsg());

Hi Rod,

In MS SQL you have to do a select right after the insert in order to get
the last inserted ID:

$q="insert into t_mytable values ('value1','value2')";
$result=odbc_exec($conn, $q) or die("".odbc_errormsg());

$q2="select @@identity";
$result=odbc_exec($conn, $q2) or die("".odbc_errormsg());
// ....

I've never used MS SQL or ODBC, but to me it looks like you should do those
operations within a transaction to ensure their combined atomicity.
Otherwise you might end up with the wrong id value...

André Næss

[David Mackenzie]

On Wed, 09 Jul 2003 20:05:13 +0000, André Næss
<an*********************@ifi.uio.no> wrote:

Jon Kraft:

"Rod" <to**@toto.com> wrote:

I am adding a new record in a table.
How can I know the ID provided by the database of the new record?

I am using the Microsoft SQL server DB and the following code:
$conn=......
$q="insert into t_mytable values ('value1','value2')";
$result=odbc_exec($conn, $q) or die("".odbc_errormsg());

Hi Rod,

In MS SQL you have to do a select right after the insert in order to get
the last inserted ID:

$q="insert into t_mytable values ('value1','value2')";
$result=odbc_exec($conn, $q) or die("".odbc_errormsg());

$q2="select @@identity";
$result=odbc_exec($conn, $q2) or die("".odbc_errormsg());
// ....

I've never used MS SQL or ODBC, but to me it looks like you should do those
operations within a transaction to ensure their combined atomicity.
Otherwise you might end up with the wrong id value...

@@identity is connection-based, so you'll always get "your" inserted
id value. However, if there is an insert trigger on that table, and it
inserts new data elsewhere into the database you may get the other
insterted id instead.

--
David (please modify address to david@ before replying!)