retrive record ID on insert

[Russ Green]

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

 

[Poohface]

What you want to do is this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629

Hope this helps,
Norst

 

[Cor Ligthert]

Poohface,

It is the right answer, however I don' like that sample. It is very long
while in fact the only thing that is needed is this part from it.

"SELECT @@IDENTITY"

and than the execute scalar.

I write this to prevent that Russ becomes afraid when he sees all that code.
Nothing wrong with your answer.



Cor

 

[Poohface]

The thing hate I think sticks out in the example and I'm not sure if
its needed is that fact they make a 2nd connection to the database. Is
that needed?

 

[Russ Green]

Thanks for your answers. I'll spend some time looking at that fully tomorrow
but my first thought is that I don't get it. What actually is going on
there? There is an event handler which is created but what is it even
doing?

 

[Cor Ligthert]

Poohface,

The thing hate I think sticks out in the example and I'm not sure if
its needed is that fact they make a 2nd connection to the database. Is
that needed?

No of course not, there is nothing wrong with it, however it is not needed
as well.
In this case it can in some lines of code. That is why I wrote you that this
sample is in my opinion a little bit confusing.

Update and do the executescalar when the update is done without errors.

Cor