Re: ID of a newly created record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: 2 (2_at_2.2)
Date: 03/03/04 

Date: Wed, 3 Mar 2004 19:47:44 +1100

However you choose to implement this you should be aware of the difference
between @@identity and scope_identity()...

@@identity is not limited in scope whereas scope_identity() will return the
id of the last inserted record in the current scope... in other words if
lots of inserts are happening on the database server you may get the wrong
identity returned using @@identity... so generally you'd use scope_identity

Look it up in SQL Books Online...

Cheers

"Glyn Williams" <orange_email@yahoo.co.uk> wrote in message
news:%23hJkdIJAEHA.2040@TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm trying to get the ID of a newly created record into a variable, so I
can
> display it in a Label or something after being created. I know you can use
> @@identity in the sql statement, but I just can't seem to get it to go.
>
> Any help appreciated,
> Glyn Williams
>
> Here is my code:-
> Public Sub addBike(Sender As Object, E As EventArgs)
>
> Dim myCommand As new SqlCommand()
> Dim myConnection As New SqlConnection
> ("server=Tron;database=sellmybike;Integrated Security=SSPI")
>
> myCommand.Connection = myConnection
> 'Define insert command
> myCommand.CommandText="INSERT INTO Details
>
(PostDate,Name,Town,County,Seller,Email,Tel,Make,Model,ManYear,Mileage,Price
> ,Details) VALUES (
>
@PostDate,@Name,@Town,@County,@Seller,@Email,@Tel,@Make,@Model,@ManYear,@Mil
> eage,@Price,@Details)"
>
> myCommand.Parameters.Add ("@PostDate", Now())
> myCommand.Parameters.Add ("@Name", msgName.Value)
> myCommand.Parameters.Add ("@Town", msgTown.Value)
> myCommand.Parameters.Add ("@County", msgCounty.Value)
> myCommand.Parameters.Add ("@Seller", msgSeller.Value)
> myCommand.Parameters.Add ("@Email", msgEmail.Value)
> myCommand.Parameters.Add ("@Tel", msgTel.Value)
> myCommand.Parameters.Add ("@Make", msgMake.Value)
> myCommand.Parameters.Add ("@Model", msgModel.Value)
> myCommand.Parameters.Add ("@ManYear", msgManYear.Value)
> myCommand.Parameters.Add ("@Mileage", msgMileage.Value)
> myCommand.Parameters.Add ("@Price", msgPrice.Value)
> myCommand.Parameters.Add ("@Details", msgDetails.Value)
>
> myCommand.Connection.Open()
> 'update the tape
> myCommand.ExecuteNonQuery()
>
> 'Im sure some code must go here and in the sql statement
>
> myCommand.Connection.Close()
> End Sub
>
>

Relevant Pages

  • Re: ID of a newly created record
    ... > @@identity in the sql statement, but I just can't seem to get it to go. ... > Public Sub addBike ... > Dim myCommand As new SqlCommand ... > Dim myConnection As New SqlConnection ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Functions
    ... the sql statement has access to variables that are ... :> private but within scope. ... package private function which he attempted to call from outside of the ...
    (comp.databases.oracle.misc)
  • Re: Functions
    ... :> private but within scope. ... package private function which he attempted to call from outside of the ... I noted that the sql statement he is building would have access to any ... and indeed even though pl/sql itself has at least one ...
    (comp.databases.oracle.misc)
  • Re: Functions
    ... :> I noted that the sql statement he is building would have access to any ... engine automatically creates and binds for PL/SQL variables in scope. ... :> scope is a limitation of the implementation of the pl/sql language. ...
    (comp.databases.oracle.misc)
  • Re: Microsoft Windows 2000 WebDAV Buffer Overflow Vulnerability
    ... scopeI wonder how many other ways are there to call an SQL statement ... Is there another vuln function like scope is? ... "strcat" on a wchar string]. ... is the patch correct ...
    (Pen-Test)