get primary key on insert

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

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.
 
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."
 
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
 
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."
Click to expand...
There is no scope_identity() function in Oracle, which is another vendor.

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
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."
 
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
 
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).
Click to expand...
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
 
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.
Click to expand...
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.
Click to expand...
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
 
Back
Top