Get primary key on insert

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi;

When I do an insert, how do I get the primary key:

Note: I am doing this with a DbCommand and doing an insert using
ExecuteNonQuery. In other words, no DataTable, no DataAdapter, just basic
direct access.

Please answer for all supported databases. I figure this includes Sql
Server, Oracle (OracleClient), ODBC (OdbcClient), OleDb (OleDbClient),
SqlServerExpress if different from SqlServer, and Jet/Access. (info for
non-supported databases would be welcome too.)

I have looked everywhere - msdn, google, etc but all the samples assume a
DataTable and a named column to return the result in. Or they have a select
in a GridView or other control and again a named param that gets put in the
object representing the row. (I am open to the DataTable approach if it means
a common select statement but from what I can tell, even with a DataTable,
the selects are still database dependent.)
 
Hi Dave,

There is no general way to get the newly inserted primary key from all
databases. Each database will have its own way to achieve this. For
example, the SQL server uses SELECT SCOPE_IDENTITY(). You have to design
this seperately.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
David,

The first point has to be if your DataBase Server supports autonumber as an
automatic key generating field. If that is not, than there is in my opinion
no need for returning an created key.

I have seen 3 methods which all three are suported by SQL server.

However the most general and less secure one is AFAIK @@Identity

I hope this helps,

Cor
 
Hello;

I know SqlServer uses scope_identity() but I have not been able to figure
out the exact format of my insert to get this value back. Nothing I have
tried works. And all documented examples either use DataTables or are an
insert="..." in a databound control. I need to know how to do it using a
DbCommand.

The second half of my question is for all MS supported databases, how do I
get this value. Since OracleClient, OleDbClient, and OdbcClient ship with
..NET, I figure they are "supported". Same for Access and SqlServerExpress -
as they are MS products, I figure they are "supported".
 
David said:
Hello;

I know SqlServer uses scope_identity() but I have not been able to figure
out the exact format of my insert to get this value back. Nothing I have
tried works. And all documented examples either use DataTables or are an
insert="..." in a databound control. I need to know how to do it using a
DbCommand.

The second half of my question is for all MS supported databases, how do I
get this value. Since OracleClient, OleDbClient, and OdbcClient ship with
.NET, I figure they are "supported". Same for Access and SqlServerExpress -
as they are MS products, I figure they are "supported".
Click to expand...

You can execute multiple SQL statements with a command object, just
separate them by semicolon in you command string. As to getting the value
back I would try ExecuteScalar instead of ExecuteNonQuery, something like this:

string comString = "insert MyTable (custName) values ('John'); select scope_identity()";
SqlCommand com = new SqlCommand(comString);
int latestCustromerId = (int)com.ExecuteScalar();

I haven't tested it, just an idea.
 
Cor said:
David,

The first point has to be if your DataBase Server supports autonumber as an
automatic key generating field. If that is not, than there is in my opinion
no need for returning an created key.

I have seen 3 methods which all three are suported by SQL server.

However the most general and less secure one is AFAIK @@Identity
Click to expand...

@@IDENTITY can return incorrect value, well at least not what you
expect. For example, when your insert triggers another insert to some
other table with identity column. Then the value generated for the second
table will be returned.
The three methods you mentioned are different, so one needs to choose
carefully.
 
Not sure what that last post says, but you can setup a parameter in the command object to be an output parameter that holds the PK value. Then in your SQL you add:

SELECT <your-pk-variable>=SCOPE_IDENTITY()

to the end of the query.
 
Back
Top