Hi,

I have a frustrating problem. My code inserts a new record into an Access DB via ADO.Net. I need access to the autoincremented ID number of the new record.

When the new record is inserted, I open the connection, execute the procedure and close the connection again. If I check the DB in Access the new record is there, with the correct ID number.

I then run the following code:
        dim lngID as Long
        lngID=da.ExecuteScalar("SELECT MAX(custID) FROM Customers")

where da is a DataAccess DLL with the following code for the method used:
        Function ExecuteScalar(byval SQL as string) as object
            Dim c as new oledbConnection(connString)
            Dim cmd as oledbCommand(SQL,c)
            Dim newValue as Object
            c.Open
            newValue=cmd.ExecuteScalar
            'tidy up
            c.close
            return newValue
        End Function

This all works fine, BUT the number returned is 1 smaller than the ID assigned to the newly inputted record! i.e. the last value added.
Anyone know what's going on??

Thanks

Dave

hi Dave

have you tried ExecuteReader() instead of ExecuteScalar()?

Anubhav Kumar

 Try
'insert here
    Try
        lngID=da.ExecuteScalar("SELECT MAX(custID) FROM Customers")
    Catch
    End Try
Catch
End Try

The problem with this concept is what if 2 people insert one right after the other and you were the first one to insert you won't get the right id. SQL Server offers stored procedures where you can return the autonumber(Identity) to the calling function or procedure.

Thanks for the replies guys. I'll try the datareader approach, though I don't see what difference that will make. I am only after one value, and the ExecuteScalar method is designed to give exactly that!

I also appreciate the potential concurrency problems, however the behaviour I'm trying to understand occurs on my development machine and I am definitely the only user! As a by-the-by I thought that "SELECT @@Identity" when executed against an AccessXP db would return the last autoincremented value for the last table so updated. I can't get this to work either.

Dave

hi fren..

use this piece of code..i am sure it will give u the desired result..

ur code..
.....
 lngID=da.ExecuteScalar("SELECT MAX(custID) FROM Customers select @@identity ")
.....

happy coding..
suneeta

I have the same problem, but my insert/update method is different.

  bld = New SqlClient.SqlCommandBuilder(daMyDataAdapter)
  Me.bsMyBindingSource.EndEdit()
  Me.daMyDataAdapter.Update(Me.dsMyDataset, "tblPAYMENT")

Then I'm using
  Me.bsMyBindingSource.Current("PaymentID").ToString

Which is obviously wrong because the BS position just moves to the last one I was on before adding the new record and the newly inserted record is not in the BS untill I call .Fill on the DA again.

I didn't really understand the solutions earlier in this thread. Can anyone dumb it down for me?

Ta,
Kia