Retrieving value of Identity column after insert?

[aviles1973]

I have recently created a topic about retrieving the value of an Identity column that is an autonumber primary key field in SQLServer2005.

Thanks to all for the posts. Even though I do understand the SQL Language and the insert command. I still am stuck on this topic because I am only using SQL to fill my DataAdapter as in:

m_daDataAdapter = New SqlClient.SqlDataAdapter("Select * From Contacts", m_cnADONetConnection)

Then I'm using this approach to add new records:

Dim drNewRow As DataRow = m_dtContacts.NewRow()

FNameNEW.Text = StrConv(FNameNEW.Text, VbStrConv.ProperCase)
LNameNEW.Text = StrConv(LNameNEW.Text, VbStrConv.ProperCase)

drNewRow("FirstName") = FNameNEW.Text
drNewRow("LastName") = LNameNEW.Text
drNewRow("PhoneNumber") = PhoneNumNEW.Text

m_dtContacts.Rows.Add(drNewRow)

m_daDataAdapter.Update(m_dtContacts)

'
m_rowPosition = m_dtContacts.Rows.Count - 1

Me.ShowCurrentRecord()

So where in the above code and what code should I use to retrieve the identity of the newly inserted ContactID column without resetting and refilling the DataTable?

STUMPED!!!!

 

[Rotor1]

One method would be to update stored procedure to include this as the last line:

SELECT @@IDENTITY FROM Contacts

Then modify your app to to handle the update statement more-so like a select statement.

 

[aviles1973]

Thanks to everyone who has helped! Now I can move on with programming.

After I inserted the row and updated the Dataset and moved my RowPosition to point to the last row in the Table I used this:

'Update AutoNumber Fields
Dim cmdGetIdentity As New SqlClient.SqlCommand("SELECT @@IDENTITY", m_cnADONetConnection)
m_dtContacts.Rows(m_rowPosition)("EmployeeID") = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString())

Additional Resource:

http://www.vbforums.com/printthread.php?t=398295