I have a simple table called "Groups" which has only 2 fields called GroupID (idenity field...increments by 1) and groupname.

It's a simple operation to add a record to the table, but I need to retrieve the value given to the identity field after the insert so I can use it in another related table.

Is there any way to have the Insert statement return the value from the identity field of the added record?

I remember that this was possible in ADO....how about ADO.Net?

Thanks.

This functionality is not a function of ADO/.NET, it's up to the database system. If you are using MSSQL server, you can execute your insert together with a select and get the result:

<insert statement>; SELECT @@IDENTITY;

This will return you the last identity inserted which will correspond with the insert statement at the beginning.

Thanks for the reply. But I'm still a little confused about how exactly I would retrieve the Identity value after the insert.

I'll try and paste my insert code below:

'insert into Groups : UserID, GroupName
            Dim MySQL As String = "Insert into Groups(UserID,GroupName) " & _
            "Values(@userid,@groupname)"

            Cmd = New SqlCommand(MySQL, SqlConnection1)

            With Cmd.Parameters
                .Add(New SqlParameter("@userid", UserID))
                .Add(New SqlParameter("@groupname", TextBox1.Text))
            End With

            SqlConnection1.Open()

            Try
                Cmd.ExecuteNonQuery()
            Catch
            End Try

            SqlConnection1.Close()

Dim MySQL As String = "Insert into Groups(UserID,GroupName) " & _
"Values(@userid,@groupname); SELECT @@ IDENTITY;"

Cmd = New SqlCommand(MySQL, SqlConnection1)

With Cmd.Parameters
    .Add(New SqlParameter("@userid", UserID))
    .Add(New SqlParameter("@groupname", TextBox1.Text))
End With

SqlConnection1.Open()

Try
    newID = Cmd.ExecuteScalar()
Catch
End Try

SqlConnection1.Close()


This runs the insert AND the select to retrieve the last identity. None of this is a function of ADO.NET, but of SQL. As I stated earlier this will work in MS-SQL Server. You will need to use other syntax (if available) for other database engines. If you aren't using MSSQL, then I can't answer the question. This is how I always do this because my inserts are usually made with a straight SQL command execution. There will be other methods when you are using datasets and use the methods of the dataset class to insert data.