get primary key on insert

[Guest]

Hi;

If I have the primary key set to be created in the database, how can I get
it's value when I insert a new record in the database? Preferably a method
that works regardless of the vendor.

 

[Kevin Yu [MSFT]]

Hi Dave,

Is the primary key an autoincrement one? If so, you can add a SELECT
SCOPE_IDENTITY() statement in the insert command so that the value in the
DataTable can get refreshed. If you're using the DataAdapter wizard to
generate the SQL statements, you can check Refresh DataSet in the Advanced
options to achieve this.

You can check the following link for more information about SCOPT_IDENTITY()

http://msdn2.microsoft.com/en-us/library/ms190315.aspx

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

 

[Cor Ligthert [MVP]]

David,

Preferably a method that works regardless of the vendor.

AFAIK is that not there, even between Access (OleDB), SQLserver 2005
(SQLClient), SQLServer 2000 (SQLClient) are in this case differences while
the methods for SQLServer 2000 works as well on SQLServer 2005.

I hope this helps,

Cor

 

[Guest]

yuck. Is there a list of how to do this for each DbConnection type somewhere?

 

[Otis Mukinfus]

Hi Dave,

Is the primary key an autoincrement one? If so, you can add a SELECT
SCOPE_IDENTITY() statement in the insert command so that the value in the
DataTable can get refreshed. If you're using the DataAdapter wizard to
generate the SQL statements, you can check Refresh DataSet in the Advanced
options to achieve this.

You can check the following link for more information about SCOPT_IDENTITY()

http://msdn2.microsoft.com/en-us/library/ms190315.aspx

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

There is no scope_identity() function in Oracle, which is another vendor.

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Kevin Yu [MSFT]]

Hi dave,

I didn't find such method currently. This has a lot of dependencies on the
requirements and database. Let's wait and see if any community member can
shed some light on it.

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

 

[Mary Chipman [MSFT]]

Unfortunately there is no way to write truly provider-independent code
in ADO.NET 2.0 (or earlier). Another common roadblock is parameter
naming and placeholders. There is always going to be provider-specific
syntax that requires you to branch in your code or to write your own
framework (which some have done in order to support multiple
back-ends).

--Mary

 

[Guest]

ok thanks - that's what I am doing. (Basically I'm implementing the JDBC API
on top of ADO.NET).

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

 

[Martin Aupperle]

Unfortunately there is no way to write truly provider-independent code
in ADO.NET 2.0 (or earlier). Another common roadblock is parameter
naming and placeholders. There is always going to be provider-specific
syntax that requires you to branch in your code or to write your own
framework (which some have done in order to support multiple
back-ends).

OK, if we cannot write truly provider independent code for this, it
might be possible to show some code for the comonly used databases? In
this thread I learned how to do it with SQL-Server. Maybe someone can
show me how to do it with Oracle and Access? Can we use stored
procedure to build an abstraction?

Greetings - Martin

 

[Mary Chipman [MSFT]]

Here's the link to the docs for writing provider-independent code in
ADO.NET 2.0:
http://msdn2.microsoft.com/en-us/library/t9f29wbk(VS.80).aspx.

If you have additional questions, I'd recommend starting a new thread
so that you get the widest possible range of responses.

--Mary

 

[Martin Aupperle]

Here's the link to the docs for writing provider-independent code in
ADO.NET 2.0:
http://msdn2.microsoft.com/en-us/library/t9f29wbk(VS.80).aspx.

I know this already, but thanks for the link anyway.
Unfortunately I do not see how this can help me with the problem.

The problem was: How can I get back to my program an auto generated
id-value when inserting a new record. Code for SQL-Server was given,
for Oracle and Access is still needed.

If you have additional questions, I'd recommend starting a new thread
so that you get the widest possible range of responses.

yes, thanks. But at the moment no additional questions. I would be
happy if someone can help me with the one I asked.

Greetings - Martin

 

[Mary Chipman [MSFT]]

See http://msdn2.microsoft.com/en-us/library/ks9f57t0.aspx
("Retrieving Identity or Autonumber Values").

--Mary