ASP.NET/ ADO.NET Access 97 Autonumber

Hi,

I have an access 97 database that I am writing data to through .NET.  The table I'm inserting data into has an autonumber column for the primary key.  I need to have that primary key return to the .NET layer so I can do other things with it.  I have done my insert through a straight SQL call, and through a DataAdapter -- they both work fine.  However, I cannot get the Primary Key returned.  I found out that you cannot do a "Select @@IDENTITY" because it is Access 97.  How can I get the primary key for an newly inserted row effectively?

Thanks
srobiaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
iboutchkineConnect With a Mentor Commented:
you can do "select YourAutonumber from Table"

where YourAutonumber is the name of the Autonumber field
0
 
EBatistaConnect With a Mentor Commented:
use the RowUpdated event from the OleDbDataAdapter:
(the code below is from MSDN)
...........................
<code to set up and adding data to dataset>
' Include an event to fill in the Autonumber value.

AddHandler catDA.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)

catDA.Update(catDS, "Categories")
.........................

the event handler:
Private Shared Sub OnRowUpdated(sender As Object, args As OleDbRowUpdatedEventArgs)
    ' Include a variable and a command to retrieve the identity value from the Access database.
    Dim newID As Integer = 0
    Dim idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", nwindConn)

    If args.StatementType = StatementType.Insert
      ' Retrieve the identity value and store it in the CategoryID column.
      newID = CInt(idCMD.ExecuteScalar())
      args.Row("CategoryID") = newID
    End If
End Sub

hope this help
0
All Courses

From novice to tech pro — start learning today.