Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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
0
srobia
Asked:
srobia
3 Solutions
 
iboutchkineCommented:
you can do "select YourAutonumber from Table"

where YourAutonumber is the name of the Autonumber field
0
 
EBatistaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now