>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,
>
>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" <poohface@sh*tyerpantsoff.crap> wrote in message
news:rin2615cs5olhm309sa1b6flljp64l11k2@4ax.com...
> 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?
>
> On Sat, 16 Apr 2005 19:10:42 +0200, "Cor Ligthert"
> <notmyfirstn***@planet.nl> wrote:
>
>>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
>>
>
>