Obtaining SCOPE_IDENTITY()

[webwired]

Hey everyone, I know there are literally thousands of questions and answers on the internet for getting the SCOPE_IDENTITY(), but I haven't found a single one that relates to my particular situation... So here we go...

I've decided to use VS .NET's built in Data Components, which in my case doing things the mainstream easy way always turns out to be the hard way for me... So anyway, here's my VB .NET code... Notice the line that is commented, that's where I need to get the SCOPE_IDENTITY().

Private Sub SaveChangesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveChangesToolStripMenuItem.Click

        If Integer.Parse(contactId) = 0 Then
            Try
                Me.Validate()
                Me.CRM_ContactsBindingSource.AddNew()
                Me.TableAdapterManager.UpdateAll(Me.DB_6b3044DataSet)
                ' contactID = ''' Some how need to get the Scope_Identity '''
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
        Else
            Try
                Me.Validate()
                Me.CRM_ContactsBindingSource.EndEdit()
                Me.TableAdapterManager.UpdateAll(Me.DB_6b3044DataSet)
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
        End If

        RefreshContactsDataGridWithContactID()
        CRM_ContactsDataGridView.Visible = True
        editDetailsViewPanel.Visible = False
        detailsViewPanel.Visible = True
        SaveChangesToolStripMenuItem.Visible = False
        CancelChangesToolStripMenuItem.Visible = False
        AddContactToolStripMenuItem.Visible = True

    End Sub

[sknake]

You don't get it with a table adapter :) If you use a typed dataset it will:
Insert the record (using your insert query)
Get the scope identity
Select your record where id = scope_id() (using your select query)

I find it rather harder to use the built in datasets for the most part. There are a few exceptions like when I need to see the data at design time, but other than that I don't use it.

[TomW]

Retrieving Identity or Autonumber Values

A bit of a pain to do with using a dataadapter to execute the queries but the above link will show you an example of doing so.