How to retrieve last inserted ID(Auto-Number) then perform another Insert Statement?

Hi All,

I hope you could help me in retrieving the last inserted ID(Auto-Number) then perform another Insert Statement?

I would really much appreciate it. I am coding in VB and am uisng

Visual Web Developer 2005 Express Edition and Microsoft SQL Sever

Management Studio Express.

Thanks alot.

-- Sam

My Codes:

Dim SQLStr5 As String = "INSERT INTO

NotesDetails(Notes_Level,Notes_Subject,Notes_Type,Notes_Year,Notes_Desc)

VALUES ('" & ddl_level.SelectedValue & "','" &

ddl_sub.SelectedValue & "','" & rbl_type.SelectedValue &

"','" & ddl_year.SelectedValue & "','" & tb_desc.Text &

"')"

Dim con5 As New SqlConnection(connstring)

con5.Open()

Dim cmd5 As New SqlCommand(SQLStr5, con5)

cmd5.ExecuteNonQuery()

con5.Close()

'' Need to get last inserted ID to Insert into the next Statement. (Notes_ID)

Dim SQLStr5a As String = "INSERT INTO

NotesComments(Notes_ID,Notes_Comments) VALUES ('" &

ddl_level.SelectedValue & "','" & tb_comments.Text & "')"

Dim con5a As New SqlConnection(connstring)

con5a.Open()

Dim cmd5a As New SqlCommand(SQLStr5a, con5a)

cmd5a.ExecuteNonQuery()

con5a.Close()

The last identity can be fetched using the SCOPE_IDENTITY() function, be aware that you have to do that within the same scope. As an additional new feature of SQL Server 2005 you could use the OUTPUT clause which give you the availbility to return values within the same DML statement. See more informations and samples in the BOL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de