Retrieve the ID of the record just added

I would like to retreive the identity field value of a record that was just added to the table.

In other words, I have a form and on submission, if it is a new record, I would like to know the identity value that the SQL Server has assigned it.

This may be overkill, but here is my code to process the form:

Protected Sub processForm(ByVal thisID As String, ByVal myAction As String)
Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
sqlConn.Open()
Dim sql As String
Select Case myAction
Case "save"
If thisID > 0 Then
sql = "update INCIDENT set " & _
"RegionID = @.RegionID, " & _
"DistrictID = @.DistrictID, " & _
"DateReported = @.DateReported, " & _
..CODE...
"WHERE IncidentID = " & myIncidentID
Else
sql = "insert into INCIDENT(" & _
"RegionID, " & _
"DistrictID, " & _
"DateReported, " & _
...CODE...
") " & _
"values(" & _
"@.RegionID, " & _
"@.DistrictID, " & _
"@.DateReported, " & _
...CODE...
")"
End If
Case "delete"
sql = "delete from INCIDENT where IncidentID = " & myIncidentID
Case Else
End Select

Dim sqlComm As New SqlCommand(sql, sqlConn)
sqlComm.Parameters.Add(New SqlParameter("@.RegionID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@.DistrictID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@.DateReported", SqlDbType.NVarChar))
...CODE...

sqlComm.Parameters.Item("@.RegionID").Value = ddRegionID.SelectedValue
sqlComm.Parameters.Item("@.DistrictID").Value = ddDistrictID.SelectedValue
sqlComm.Parameters.Item("@.DateReported").Value = db.handleDate(txtDateReported.SelectedDate)
...CODE...

Dim myError As Int16 = sqlComm.ExecuteNonQuery
'Response.Redirect("incident.aspx?id=" & )
End Sub

The response.redirect at the end of the sub is where I would like to put the identity field value.

This has been a popular question over the past 2 days.Check out these posts, you should find what you need:415207 and416141

____________________________________________________________________

Updated hyperlinks on January 9, 2006

|||Thanks! That did it.|||

Hi

I have the same problem but those posts are no longer there. Are you able to help me out?

I couldn't work it out so I created a date/time field called StartDate for when each record is entered. I was trying to use that to grab the particular eventID.

Dim EventID =""

tbEventIDTest.Text =""

Dim EventDataSource1AsNew SqlDataSource()

EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString

EventDataSource1.SelectCommandType = SqlDataSourceCommandType.Text

EventDataSource1.SelectCommand ="SELECT EventID FROM Event "WHERE ([StartDate] = @.StartDate)"

EventID = EventDataSource1.SelectParameters.Item(EventID)

tbEventIDTest.Text = EventID

Thanks, any help will be appreciated.

|||

Hi

Call SCOPE_IDENTITY() to retrieve Identity.

Take a look atRetrieving Identity or Autonumber Valuesfor sample code.

|||thanks alot|||

gevans:

I have the same problem but those posts are no longer there.

Sorry I'm so late to reply. I've now updated that post so that the hyperlinks work; the old links only worked with the older version of the software used here.