retrieving identity

[Joël]

It's better to use SCOPE_INDENTITY( )
@@IDENTITY can give a bad answer in some situations.

"Kevin Spencer" <kevin@SPAMMERSSUCKtakempis.com> a écrit dans le message de
news:uYJX8$0ODHA.3700@tk2msftngp13.phx.gbl...

> Use a Stored Procedure. It performs the INSERT and then does a SELECT
> @@IDENTITY AS 'Identity'. Execute the Stored Procedure using a Command
> object, and returning the result to a DataReader.
>
> HTH,
>
> Kevin Spencer
> Microsoft FrontPage MVP
> Internet Developer
> [url]http://www.takempis.com[/url]
> Big things are made up of
> lots of Little things.
>
> "belgie" <belgie@hns.com> wrote in message
> news:%23LNhn80ODHA.2788@TK2MSFTNGP10.phx.gbl...

> > I am using a SQLCommand to insert a row in a table which has an identity
> > field as the primary key.
> >
> > I haven't come up with a good method of retrieving the new identity

value

> > after the insert is complete. The best I have found is to do the insert

> and

> > then do a select using the values just inserted to retrieve the identity
> > value.
> >
> > Is there a more efficient way to do this?
> >
> > Thanks!
> > Bill
> >
> >
> >

>
>

[Duncan Godwin]

Essentially is the same solution as previously mentioned, use an output
parameter instead.

_com.CommandText = @"INSERT INTO Emp(Name) VALUES(@Name);
DECLARE @ID int
SET @ID = @@Identity
"; // the ; is important after the first query
_com.Parameters.Add("@Name", SqlVarChar.VarChar, 50);
_com.Parameters.Add("@ID", SqlVarChar.Int);
_com.Parameters["@ID"].Direction = ParameterDirection.Output.
_com.Parameters["@ID"].Value = "Me";

nRowsAffected = _com.ExecuteNonQuery();

int ident = (int)_com.Parameters["@ID"].Value;

Something like that should work, but I haven't tested it, it's the same
principle when you
use a stored procedure.

Duncan

"William F. Robertson, Jr." <wfrobertson@kpmg.com> wrote in message
news:#Rxtql1ODHA.2316@TK2MSFTNGP11.phx.gbl...

> Well then maybe someone could help me with mine.
>
> I have an insert query that needs to be run, the RowsAffected is returned
> from the ExecuteNonQuery(), but I also need to get the identity of the

added

> row.
>
> Currently I am using
>
> _com.CommandText = <insert query>;
> nRowsAffected = _com.ExecuteNonQuery();
> _com.CommandText = "Select @@Identity";
> nIdentity = _com.ExecuteScalar();
>
> I tried changing the Select @@Identity to scope_Identity() but that didn't
> work since it is two different commands, but the @@Identity will return

the

> one I am looking for (as long as there are no triggers or anything)

because

> it is in the same session (Connection).
>
> bill
>
>
>
> "Joël" <jdescombes@netcourrier.com> wrote in message
> news:3ef9faee$0$17295$79c14f64@nan-newsreader-03.noos.net...

> > It's better to use SCOPE_INDENTITY( )
> > @@IDENTITY can give a bad answer in some situations.
> >
> > "Kevin Spencer" <kevin@SPAMMERSSUCKtakempis.com> a écrit dans le message

> de

> > news:uYJX8$0ODHA.3700@tk2msftngp13.phx.gbl...

> > > Use a Stored Procedure. It performs the INSERT and then does a SELECT
> > > @@IDENTITY AS 'Identity'. Execute the Stored Procedure using a Command
> > > object, and returning the result to a DataReader.
> > >
> > > HTH,
> > >
> > > Kevin Spencer
> > > Microsoft FrontPage MVP
> > > Internet Developer
> > > [url]http://www.takempis.com[/url]
> > > Big things are made up of
> > > lots of Little things.
> > >
> > > "belgie" <belgie@hns.com> wrote in message
> > > news:%23LNhn80ODHA.2788@TK2MSFTNGP10.phx.gbl...
> > > > I am using a SQLCommand to insert a row in a table which has an

> identity

> > > > field as the primary key.
> > > >
> > > > I haven't come up with a good method of retrieving the new identity

> > value

> > > > after the insert is complete. The best I have found is to do the

> insert

> > > and
> > > > then do a select using the values just inserted to retrieve the

> identity

> > > > value.
> > > >
> > > > Is there a more efficient way to do this?
> > > >
> > > > Thanks!
> > > > Bill
> > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Kevin Spencer]

The reason I recommended using a Stored Procedure is that you need to get
the @@IDENTITY value immediately after doing the INSERT. Doing it in 2
separate operations will yield untrustworthy results. How many other INSERTS
might be performed prior to your second query being executed on your live
site?

HTH,

Kevin Spencer
Microsoft FrontPage MVP
Internet Developer
[url]http://www.takempis.com[/url]
Big things are made up of
lots of Little things.

"William F. Robertson, Jr." <wfrobertson@kpmg.com> wrote in message
news:%23Rxtql1ODHA.2316@TK2MSFTNGP11.phx.gbl...

> Well then maybe someone could help me with mine.
>
> I have an insert query that needs to be run, the RowsAffected is returned
> from the ExecuteNonQuery(), but I also need to get the identity of the

added

> row.
>
> Currently I am using
>
> _com.CommandText = <insert query>;
> nRowsAffected = _com.ExecuteNonQuery();
> _com.CommandText = "Select @@Identity";
> nIdentity = _com.ExecuteScalar();
>
> I tried changing the Select @@Identity to scope_Identity() but that didn't
> work since it is two different commands, but the @@Identity will return

the

> one I am looking for (as long as there are no triggers or anything)

because

> it is in the same session (Connection).
>
> bill
>
>
>
> "Joël" <jdescombes@netcourrier.com> wrote in message
> news:3ef9faee$0$17295$79c14f64@nan-newsreader-03.noos.net...

> > It's better to use SCOPE_INDENTITY( )
> > @@IDENTITY can give a bad answer in some situations.
> >
> > "Kevin Spencer" <kevin@SPAMMERSSUCKtakempis.com> a écrit dans le message

> de

> > news:uYJX8$0ODHA.3700@tk2msftngp13.phx.gbl...

> > > Use a Stored Procedure. It performs the INSERT and then does a SELECT
> > > @@IDENTITY AS 'Identity'. Execute the Stored Procedure using a Command
> > > object, and returning the result to a DataReader.
> > >
> > > HTH,
> > >
> > > Kevin Spencer
> > > Microsoft FrontPage MVP
> > > Internet Developer
> > > [url]http://www.takempis.com[/url]
> > > Big things are made up of
> > > lots of Little things.
> > >
> > > "belgie" <belgie@hns.com> wrote in message
> > > news:%23LNhn80ODHA.2788@TK2MSFTNGP10.phx.gbl...
> > > > I am using a SQLCommand to insert a row in a table which has an

> identity

> > > > field as the primary key.
> > > >
> > > > I haven't come up with a good method of retrieving the new identity

> > value

> > > > after the insert is complete. The best I have found is to do the

> insert

> > > and
> > > > then do a select using the values just inserted to retrieve the

> identity

> > > > value.
> > > >
> > > > Is there a more efficient way to do this?
> > > >
> > > > Thanks!
> > > > Bill
> > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[William F. Robertson, Jr.]

The thing is I have a InsertCommand object for my developers to use that
will insert for them, and they can set a bool to determine if the identity
should be captured. So when they call the Execute method, it will run their
query and grab the identity if it is applictiable.

Any other suggestions would be highly appreciated.

Thanks,

bill



"Kevin Spencer" <kevin@SPAMMERSSUCKtakempis.com> wrote in message
news:OfiSqt9ODHA.1072@TK2MSFTNGP10.phx.gbl...

> The reason I recommended using a Stored Procedure is that you need to get
> the @@IDENTITY value immediately after doing the INSERT. Doing it in 2
> separate operations will yield untrustworthy results. How many other

INSERTS

> might be performed prior to your second query being executed on your live
> site?
>
> HTH,
>
> Kevin Spencer
> Microsoft FrontPage MVP
> Internet Developer
> [url]http://www.takempis.com[/url]
> Big things are made up of
> lots of Little things.
>
> "William F. Robertson, Jr." <wfrobertson@kpmg.com> wrote in message
> news:%23Rxtql1ODHA.2316@TK2MSFTNGP11.phx.gbl...

> > Well then maybe someone could help me with mine.
> >
> > I have an insert query that needs to be run, the RowsAffected is

returned

> > from the ExecuteNonQuery(), but I also need to get the identity of the

> added

> > row.
> >
> > Currently I am using
> >
> > _com.CommandText = <insert query>;
> > nRowsAffected = _com.ExecuteNonQuery();
> > _com.CommandText = "Select @@Identity";
> > nIdentity = _com.ExecuteScalar();
> >
> > I tried changing the Select @@Identity to scope_Identity() but that

didn't

> > work since it is two different commands, but the @@Identity will return

> the

> > one I am looking for (as long as there are no triggers or anything)

> because

> > it is in the same session (Connection).
> >
> > bill
> >
> >
> >
> > "Joël" <jdescombes@netcourrier.com> wrote in message
> > news:3ef9faee$0$17295$79c14f64@nan-newsreader-03.noos.net...

> > > It's better to use SCOPE_INDENTITY( )
> > > @@IDENTITY can give a bad answer in some situations.
> > >
> > > "Kevin Spencer" <kevin@SPAMMERSSUCKtakempis.com> a écrit dans le

message

> > de

> > > news:uYJX8$0ODHA.3700@tk2msftngp13.phx.gbl...
> > > > Use a Stored Procedure. It performs the INSERT and then does a

SELECT

> > > > @@IDENTITY AS 'Identity'. Execute the Stored Procedure using a

Command

> > > > object, and returning the result to a DataReader.
> > > >
> > > > HTH,
> > > >
> > > > Kevin Spencer
> > > > Microsoft FrontPage MVP
> > > > Internet Developer
> > > > [url]http://www.takempis.com[/url]
> > > > Big things are made up of
> > > > lots of Little things.
> > > >
> > > > "belgie" <belgie@hns.com> wrote in message
> > > > news:%23LNhn80ODHA.2788@TK2MSFTNGP10.phx.gbl...
> > > > > I am using a SQLCommand to insert a row in a table which has an

> > identity

> > > > > field as the primary key.
> > > > >
> > > > > I haven't come up with a good method of retrieving the new

identity

> > > value
> > > > > after the insert is complete. The best I have found is to do the

> > insert

> > > > and
> > > > > then do a select using the values just inserted to retrieve the

> > identity

> > > > > value.
> > > > >
> > > > > Is there a more efficient way to do this?
> > > > >
> > > > > Thanks!
> > > > > Bill
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[William F. Robertson, Jr.]

Duncan,

You solution, aside from your predestined syntax errors, worked wonderfully.

Thanks,

bill
"William F. Robertson, Jr." <wfrobertson@kpmg.com> wrote in message
news:u4gRfUCPDHA.3408@tk2msftngp13.phx.gbl...

> The thing is I have a InsertCommand object for my developers to use that
> will insert for them, and they can set a bool to determine if the identity
> should be captured. So when they call the Execute method, it will run

their

> query and grab the identity if it is applictiable.
>
> Any other suggestions would be highly appreciated.
>
> Thanks,
>
> bill
>
>
>
> "Kevin Spencer" <kevin@SPAMMERSSUCKtakempis.com> wrote in message
> news:OfiSqt9ODHA.1072@TK2MSFTNGP10.phx.gbl...

> > The reason I recommended using a Stored Procedure is that you need to

get

> > the @@IDENTITY value immediately after doing the INSERT. Doing it in 2
> > separate operations will yield untrustworthy results. How many other

> INSERTS

> > might be performed prior to your second query being executed on your

live

> > site?
> >
> > HTH,
> >
> > Kevin Spencer
> > Microsoft FrontPage MVP
> > Internet Developer
> > [url]http://www.takempis.com[/url]
> > Big things are made up of
> > lots of Little things.
> >
> > "William F. Robertson, Jr." <wfrobertson@kpmg.com> wrote in message
> > news:%23Rxtql1ODHA.2316@TK2MSFTNGP11.phx.gbl...

> > > Well then maybe someone could help me with mine.
> > >
> > > I have an insert query that needs to be run, the RowsAffected is

> returned

> > > from the ExecuteNonQuery(), but I also need to get the identity of the

> > added

> > > row.
> > >
> > > Currently I am using
> > >
> > > _com.CommandText = <insert query>;
> > > nRowsAffected = _com.ExecuteNonQuery();
> > > _com.CommandText = "Select @@Identity";
> > > nIdentity = _com.ExecuteScalar();
> > >
> > > I tried changing the Select @@Identity to scope_Identity() but that

> didn't

> > > work since it is two different commands, but the @@Identity will

return

> > the

> > > one I am looking for (as long as there are no triggers or anything)

> > because

> > > it is in the same session (Connection).
> > >
> > > bill
> > >
> > >
> > >
> > > "Joël" <jdescombes@netcourrier.com> wrote in message
> > > news:3ef9faee$0$17295$79c14f64@nan-newsreader-03.noos.net...
> > > > It's better to use SCOPE_INDENTITY( )
> > > > @@IDENTITY can give a bad answer in some situations.
> > > >
> > > > "Kevin Spencer" <kevin@SPAMMERSSUCKtakempis.com> a écrit dans le

> message

> > > de
> > > > news:uYJX8$0ODHA.3700@tk2msftngp13.phx.gbl...
> > > > > Use a Stored Procedure. It performs the INSERT and then does a

> SELECT

> > > > > @@IDENTITY AS 'Identity'. Execute the Stored Procedure using a

> Command

> > > > > object, and returning the result to a DataReader.
> > > > >
> > > > > HTH,
> > > > >
> > > > > Kevin Spencer
> > > > > Microsoft FrontPage MVP
> > > > > Internet Developer
> > > > > [url]http://www.takempis.com[/url]
> > > > > Big things are made up of
> > > > > lots of Little things.
> > > > >
> > > > > "belgie" <belgie@hns.com> wrote in message
> > > > > news:%23LNhn80ODHA.2788@TK2MSFTNGP10.phx.gbl...
> > > > > > I am using a SQLCommand to insert a row in a table which has an
> > > identity
> > > > > > field as the primary key.
> > > > > >
> > > > > > I haven't come up with a good method of retrieving the new

> identity

> > > > value
> > > > > > after the insert is complete. The best I have found is to do

the

> > > insert
> > > > > and
> > > > > > then do a select using the values just inserted to retrieve the
> > > identity
> > > > > > value.
> > > > > >
> > > > > > Is there a more efficient way to do this?
> > > > > >
> > > > > > Thanks!
> > > > > > Bill
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>