In SQL SERVER 2005, how can I get the ID of the record I just insert to table?

In SQL SERVER 2005, how can I get the ID of the record I just insert to table?

I defined a table MyTable, and I insert a record into the table using the SQL below

Insert into MyTable (Name) values ("User Name")

You know the field ID is IDENTITY, so it can not be in Insert SQL, and SQL SERVER will pass a value to it automatically.
How can I know the ID of the record I just insert to table?

CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Take a look at @.@.IDENTITY - it returns the identity value for the row just added.|||Thanks!|||

There are a number of ways, the easiest of which is SELECT SCOPE_IDENTITY().

If you are using a text command, this works well:

Dim conn as new sqlconnection("{Your connection string}")

conn.open

dim cmd as new sqlcommand("SET NOCOUNT ON INSERT INTO MyTable(Name) VALUES (@.Name) SET NOCOUNT OFF SELECT SCOPE_IDENTITY()",conn)

cmd.parameters.add("@.Name",sqldbtype.varchar)

cmd.parameters("@.Name").value={Whatever}

dim MyID as integer=cmd.executescaler

conn.close

Try not to use @.@.IDENTITY unless you fully understand what the difference is between @.@.IDENTITY and SCOPE_IDENTITY(), in which case, you'll almost never want @.@.IDENTITY anyhow.