ID of a newly created record

G

Glyn Williams

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
 
S

SB

Hi Glyn,

Look in the documentation for ExecuteScalar. You will get the answer there.
Good luck.
Selim
 
W

William Ryan eMVP

Glyn:
Glyn Williams said:
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
Click to expand...
You are on the right track ;-)


If you use the DataAdapterConfiguration Wizard when you gen your
DataAdapter, you'll see an option "Refresh the DataSet" (I'm not suggesting
this approach but it's instructive). What this does is generate the update
logic like you would expect it to, but it tacks on another select statment
Select FieldName where Whatever = @@ScopeIdentity . This is essentially
what you will want to do. Bill Vaughn has a great article that walks you
through the process available here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp

HTH,

Bill
 
G

Guest

You have to have the statement "select scope_identity()" after your insert statement. Then, use ExecuteScalar instead of ExecuteNonQuery to retrieve the id of the last inserted record.

Tu-Thach
www.ongtech.com

----- Glyn Williams wrote: -----

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
 
2

2

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
 
G

Guest

Here's your 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); SELECT @@IDENTITY"

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
dim myID as integer = myCommand.ExecuteScalar

myCommand.Connection.Close()

End Sub

Have fun!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting Unique ID from new record 1
finding out the id of a recently created record 3

Top