How to determine primary key field for a new row

[ghall426]

I am using the following code to add a single record to a database. (And the code is working fine):

        myDA = New SqlDataAdapter("SELECT * FROM Rescue", myCon)
        myCB = New SqlCommandBuilder(myDA)
        myDS = New DataSet
        myDA.Fill(myDS, "Rescue")
        Dim myRow As DataRow = myDS.Tables("Rescue").NewRow
        myRow.Item("CallNum") = Me.TxtCallNum.Text
        ....other fields added here.....
        myDS.Tables("Rescue").Rows.Add(myRow)
        myDA.Update(myDS, "Rescue")

The table has a primary key "ID" that is set as an identity and is auto-incremented. I want to obtain the newly assigned ID field to use as a foreign key in another table. Both the myrow.item("ID") is NULL and the last row in the myDS dataset is NULL.

Only if I execute the sqldataadapter SELECT again to repopulate the table will it show, but by this time it's too late. I no longer know which is the newly added record. I don't think I should just assume it's the highest 'ID' number.

How can I obtain the auto-generated ID field when adding a new row?

Greg

[Ecniv]

From the sounds of it your new record hasn't actually be added to the table - therefore no id is available.

If you are storing the user who added it you can query the table after the storage to get the greatest ID for that user.

In access it is there immediately, but I think in .Net/other databases you need to find a good way to get it back.

Can you use a Trigger perhaps?

[Al42]

If this is a SQL Server database (it works in Access too) you execute "SELECT @@IDENTITY FROM Rescue", the only field returned will be the value in the autonumber field of the record you just added.

[ghall426]

If this is a SQL Server database (it works in Access too) you execute "SELECT @@IDENTITY FROM Rescue", the only field returned will be the value in the autonumber field of the record you just added.

This does indeed just return 1 field but it returns all rows, not just the most recently added row.

This has to be a common issue. Primary and foreign keys are everywhere. Am I doing something wrong?

[si_the_geek]

Try just "SELECT @@IDENTITY" (without a table name), or better still "SELECT @@SCOPE_IDENTITY", which restricts it to the last Identity value created for the current user (rather than any user).