Retrieve autonumber value of record when inserted

[russgreen]

I have a application that connects to an access database. I need to insert an item into one of the tables and immeditely retrieve the value from the autonumber field so that I can use that value into a record in another table.

I've tried using a simple INSERT command followed by a querie to search for the record according to 2 known values.

This function generates an error:

Data type mismatch in criteria expression

in this line:

GetDrgIDFromDb = CType(Cmd.ExecuteScalar(), Long)

    Public Function GetDrgIDFromDb(ByVal DrgNo As String, ByVal SetID As Long) As Long
        Try
            'connection stuff
            Dim Conn As New OleDbConnection(sConnString & dbFullPath)
            Dim strSQL As String = "SELECT drg_id FROM tbl_drawings WHERE drg_no = " & DrgNo & " AND set_id = " & SetID
            Dim Cmd As New OleDbCommand(strSQL, Conn)
            Conn.Open()

            'datatype mimatch at this line
            GetDrgIDFromDb = CType(Cmd.ExecuteScalar(), Long)

            'cleanup
            Conn.Dispose()
            Conn = Nothing
            Cmd.Dispose()
            Cmd = Nothing

        Catch ex As Exception
            modErrorLogger.addToLog(ex.StackTrace, ex.Message)
        End Try
    End Function

sColumns = "([set_id], [drg_title], [drg_scale], [drg_scalefactor], [drg_dr], [drg_ch], [drg_date], [drg_path], [drg_status], [drg_no])"
        sValues = "('" & CStr(lDrg_Set) & "','" & sDrg_Title & "','" & sDrg_Scale & "','" & sDrg_ScaleFactor & "','" & sDrg_Drawn & "','" & sDrg_Checked & "','" _
        & sDrg_Date & "','" & sDrg_FilePath & "','" & sDrg_Status & "','" & sDrg_Number & "')"

        sSQL = "INSERT INTO tbl_drawings " & sColumns & " VALUES " & sValues
        SaveRecord(sSQL) 'save the record to the db

        'save a blank revision to attach issue information to
        sColumns = "([rev_mk], [rev_dr], [rev_ch], [rev_note], [rev_date], [drg_id])"
        sValues = "('-','" & sDrg_Drawn & "','" & sDrg_Checked & "','-','" & sDrg_Date & "','" & CStr(GetDrgIDFromDb(sDrg_Number, lDrg_Set)) & "')"

        sSQL = "INSERT INTO tbl_revisions " & sColumns & " VALUES " & sValues
        SaveRecord(sSQL) 'save the record to the db

Can someone see anything wrong with this or suggest a better way to do this? I was looking at stored procedures but I don't understand how they work.

Regards,
Russ

http://www.learnvisualstudio.net?referedby=38523

 

[dalchri]

This might be more reliable:

SELECT @@IDENTITY

It will retrieve the last autonumber insterted into a row.

To determine the return type, I would recommend the following:

Dim obj As Object = new OleDbCommand( _
   "SELECT @@IDENTITY", Conn).ExecuteScalar()

'Enter break mode here and determine the object type
'by using the locals window
GetDrgIDFromDb = CType(obj, Long)