Retrieving Identity :(

[hsingh1981]

Hi all i've just migrated from access 2003 to sql server 2k.
I'm having problem retrieveing the StaffID after i inserted the record. Is there an easy way to retrieve the staffid? And can some1 show me quick example?

my code is this

    Private Sub AddStaff()

        Dim sql, sql2 As String
        Dim conn As New ADODB.Connection()
        Dim rs As New ADODB.Recordset
        Dim intID As Integer

        sql = "SELECT staff.* FROM Staff"

        staffaddform.Cursor = System.Windows.Forms.Cursors.WaitCursor

        Try
            conn.Open(connectionstring)
        Catch ex As Exception
            staffaddform.Cursor = System.Windows.Forms.Cursors.Default
            MsgBox("ERROR: Database cannot be opened. See system administrator. MESSAGE: " + ex.Message.ToString, MsgBoxStyle.Critical, "APPLICATION ERROR")
            Exit Sub
        End Try

        Try
            rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
        Catch ex As Exception
            staffaddform.Cursor = System.Windows.Forms.Cursors.Default
            MsgBox("ERROR: Table STAFF cannot be opened. See system administrator. MESSAGE: " + ex.Message.ToString, MsgBoxStyle.Critical, "APPLICATION ERROR")
            conn.Close()
            conn = Nothing
            Exit Sub
        End Try

        rs.AddNew()
        newstaffid = rs.Fields("StaffID").Value


        rs.Fields("Title").Value = "" & cboTitle.Text
        rs.Fields("Firstname").Value = "" & txtFirstname.Text
        rs.Fields("Middlename").Value = "" & txtMiddlename.Text
        rs.Fields("Surname").Value = "" & txtSurname.Text
        rs.Fields("Status").Value = "" & cboStatus.Text
        rs.Fields("Area").Value = "" & cboArea.SelectedValue
        rs.Fields("StaffType").Value = "" & cboStaffType.SelectedValue
        rs.Fields("Sex").Value = "<>"
        rs.Fields("Grade").Value = "<>"
        rs.Fields("PTorFT").Value = "<>"
        rs.Fields("TeamName").Value = "<>"
        rs.Fields("Mentor").Value = "<>"
        rs.Fields("CreatedDate").Value = Format(Now(), "dd/MM/yyyy")
        rs.Fields("CreatedBy").Value = username
        rs.Fields("UpdatedDate").Value = Format(Now(), "dd/MM/yyyy")
        rs.Fields("UpdatedBy").Value = username



        If chkEquipTraining.Checked = True Then
            rs.Fields("EquipTraining").Value = 1
        Else
            rs.Fields("EquipTraining").Value = 0
        End If
        rs.Update()

        rs.Close()
        rs = Nothing




        conn.Close()
        conn = Nothing

    End Sub

many thanks

 

[Craig0201]

The reason you're finding this hard is because your premise is wrong.

Use stored procedures and the most approptiate variant as listed here

http://msdn.microsoft.com/en-us/library/ms187342.aspx

C

 

[RiverGuy]

Put this line newstaffid = rs.Fields("StaffID").Value after you call your .Update method.

 

[RiverGuy]

By the way, consider moving your ADO code to ADO.Net if at all possible.