Retriving the ID of the last record inserted

I would appreciate help with retriving the ID of the last record inserted. Have spent considerable time in forums and google but can't find anything that works for me.

Here is my VB Code

Dim queryString As String = "INSERT INTO [DUALML] ([UseriD], [Company]) VALUES (@.UseriD, @.Company)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_useriD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_useriD.ParameterName = "@.UseriD"
dbParam_useriD.Value = useriD
dbParam_useriD.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_useriD)
Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_company.ParameterName = "@.Company"
dbParam_company.Value = company
dbParam_company.DbType = System.Data.DbType.[String]
dbCommand.Parameters.Add(dbParam_company)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function

I know from SQL side you can get the inserted row in the insert/update trigger. So maybe you can put the inserted row into a table in trigger, and then get it immediately after the insert command? For example, you define a trigger like this:

declare trigger trg_DUALML on DUALML for insert
as
BEGIN
if object_id('tbl_temp')is not null
drop table tbl_temp
select * into tbl_temp from inserted
end

Then you can query the tbl_temp table to get the ID of the row:

select UserID from tbl_temp

|||

Use StordProcedure.

@.UseerId int,

@.Company Varchar(150)

@.Id (Your column for the new Id) int OUTPUT

INSERT INTO DUALML(UserId,Compnay) VALUES(@.UserId,Company)

SELECT @.id = SCOPE_IDENTITY()

Then, in your application code, put an output parameter to get the new inserted id.

|||Lori_Jay Said:
>>>
declare trigger trg_DUALML on DUALML for insert
as
BEGIN
if object_id('tbl_temp')is not null
drop table tbl_temp
select * into tbl_temp from inserted
end
>>
I hate to say it, but this is actually a VERY bad move. One of the worst I've seen in quite a while - it's the proverbial sledgehammer to kill the nail. (Only this sledgehammer is actually a bulldozer.)

It IS creative, and shows problem solving, etc - but will un-necessarily gum up the DB with artifacts/bloat, is less performant, and can potentially lead to major problems down the road.

SQL Server (and all other RDBMS' out there) provide explicit ways to get this info.

Check in to @.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT() - they all have their own subtle differences/scopes and provide the functionality needed.

Che3358's example is good.|||

thanks for that

I will give it a go

sql