Peter Hiemenz
|
| Posted: 12/26/2002, 6:30 PM |
|
What is the recommended way to get the Identity value after Inserting a
record (using .NET / C#/ MS SQL Server).
In "DataProvider.cs" the following code is executed:
result=ExecuteInsert();
}catch(Exception e){
E=e;}
finally{
//End Record VORGANG Build insert
So I thought, easily take the connection of the Insert Object and make the
the query like
// read Identity column
try{
Object o = Insert.Dao.ExecuteScalar("SELECT @@IDENTITY");
int nId = (int )o;
...
But ... no way. The ExecuteInsert() function opens connection, executes the
statement and closes the connection again ... voila ... and @@IDENTITY is
also gone.
--
TIA,
PH
|
|
|
 |
Shawn Mason
|
| Posted: 12/27/2002, 9:37 AM |
|
Depending on how much activity you have you can use Ident_Current (look at
BOL). This gives you the last Identity for a particular table. If you have a
very busy environment, then I suggest using a stored procedure that returns
the identity. An sp is the only guaranteed way I know of since web
technololgy is disconnected.
From BOL the different methods are:
a.. @@IDENTITY returns the last identity value generated for any table in
the current session, across all scopes.
a.. IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
a.. SCOPE_IDENTITY returns the last identity value generated for any table
in the current session and the current scope.
--
Kindest Regards,
Shawn Mason,CCD,MCP
I.S. Software Design Associates
"Peter Hiemenz" <peter@hiemenz.org> wrote in message
news:augdvv$97q$1@news.codecharge.com...
> What is the recommended way to get the Identity value after Inserting a
> record (using .NET / C#/ MS SQL Server).
>
>
> In "DataProvider.cs" the following code is executed:
>
> result=ExecuteInsert();
> }catch(Exception e){
> E=e;}
> finally{
> //End Record VORGANG Build insert
>
>
> So I thought, easily take the connection of the Insert Object and make the
> the query like
>
> // read Identity column
> try{
> Object o = Insert.Dao.ExecuteScalar("SELECT @@IDENTITY");
> int nId = (int )o;
> ...
>
> But ... no way. The ExecuteInsert() function opens connection, executes
the
> statement and closes the connection again ... voila ... and @@IDENTITY is
> also gone.
>
>
> --
> TIA,
>
> PH
>
>
|
|
|
|
Peter Hiemenz
|
| Posted: 12/27/2002, 4:56 PM |
|
Ident_Current is available only with MS SQL Server 2000, I think. And it's
dangerous as it's not 4 sure that I get the right ID even in low activity
systems. Maybe happen only once in 2 years or never but this kind of bugs
may be nearly impossible to find.
Yes, the best way is to use a stored procedure to save the data - but with
Code Charge I have to write this myself (I do not know a way it does it) ,
whereas if I use update command I let CCS handle the code and thus it's less
work for me. It might be nice, if CCS could use SPs for using AND also
create the necessary code (at least for the main DBMSs like Oracle, MS SQL
.... )
Kindest Regards,
Peter Hiemenz
----------------------------------------------------------------------------
-------------------
Homepage Office: http://hiemenz.org/hms/
Homepage Privat: http://hiemenz.org/
----------------------------------------------------------------------------
-------------------
Fon Office: +49-(0)203-758190
----- Original Message -----
From: "Shawn Mason" <shawn@issda.com>
Newsgroups: codecharge.discussion
Sent: Friday, December 27, 2002 6:38 PM
Subject: Re: .NET/MS SQL Server - how to get Identity column??
> Depending on how much activity you have you can use Ident_Current (look at
> BOL). This gives you the last Identity for a particular table. If you have
a
> very busy environment, then I suggest using a stored procedure that
returns
> the identity. An sp is the only guaranteed way I know of since web
> technololgy is disconnected.
>
> From BOL the different methods are:
> a.. @@IDENTITY returns the last identity value generated for any table in
> the current session, across all scopes.
> a.. IDENT_CURRENT returns the last identity value generated for a specific
> table in any session and any scope.
> a.. SCOPE_IDENTITY returns the last identity value generated for any table
> in the current session and the current scope.
> --
> Kindest Regards,
>
> Shawn Mason,CCD,MCP
> I.S. Software Design Associates
>
> "Peter Hiemenz" <peter@hiemenz.org> wrote in message
>news:augdvv$97q$1@news.codecharge.com...
> > What is the recommended way to get the Identity value after Inserting a
> > record (using .NET / C#/ MS SQL Server).
> >
> >
> > In "DataProvider.cs" the following code is executed:
> >
> > result=ExecuteInsert();
> > }catch(Exception e){
> > E=e;}
> > finally{
> > //End Record VORGANG Build insert
> >
> >
> > So I thought, easily take the connection of the Insert Object and make
the
> > the query like
> >
> > // read Identity column
> > try{
> > Object o = Insert.Dao.ExecuteScalar("SELECT @@IDENTITY");
> > int nId = (int )o;
> > ...
> >
> > But ... no way. The ExecuteInsert() function opens connection, executes
> the
> > statement and closes the connection again ... voila ... and @@IDENTITY
is
> > also gone.
> >
> >
> > --
> > TIA,
> >
> > PH
> >
> >
>
>
|
|
|
|
|
