scope_identity() in jet

  • Thread starter Thread starter Miro
  • Start date Start date
M

Miro

I am trying to get my ID added from a people table in an access table
"MyContacts.mdb" i just added.
Its basically a contact file i created in access:

PK Person_ID - Unique - autoincrement
PersonName varchar(30)

so after I edit the screen on 'add' mode, i call this cod here:

PeopleBindingSource.EndEdit()
PeopleTableAdapter.Update(Me.MyDataDS.People)
'Do SCOPE_IDENTITY() SOMEHOW HERE
Me.MyDataDataDS.AcceptChanges()

I cannot figure out how to somehow add a new query to the
"PeopleTableAdapter" to return the ID.

SQL express seems to use the "Refresh the data table" option under advanced
options but this is not the case for jet. It does not have that option
available.

I know my other option is to re-fill the whole dataset ( there are not a lot
of contacts ) but I am trying to see how to use a scope_identity for an
access table - as this is all done behind the scenes for me in an sql
express table.

Thanks,

Miro
 
I think my easy solution is to add a DateTimeStamp column...set it to a
value,
and then run 1 more query against the database that returns the ID_KEY value

Then upate the row in the database before accept changes is run.

-thats my current solution I am working on.
 
Miro said:
I am trying to get my ID added from a people table in an access table
"MyContacts.mdb" i just added.
Its basically a contact file i created in access:

PK Person_ID - Unique - autoincrement
PersonName varchar(30)

so after I edit the screen on 'add' mode, i call this cod here:

PeopleBindingSource.EndEdit()
PeopleTableAdapter.Update(Me.MyDataDS.People)
'Do SCOPE_IDENTITY() SOMEHOW HERE
Me.MyDataDataDS.AcceptChanges()

I cannot figure out how to somehow add a new query to the
"PeopleTableAdapter" to return the ID.

SQL express seems to use the "Refresh the data table" option under
advanced options but this is not the case for jet. It does not have
that option available.

I know my other option is to re-fill the whole dataset ( there are not a
lot of contacts ) but I am trying to see how to use a scope_identity for
an access table - as this is all done behind the scenes for me in an sql
express table.

Thanks,

Miro
Click to expand...

The MS Access database doesn't have any scope_identity() function. You
have to use the @@identity variable.

The identity has session scope, so you need to use the same database
connection as for the insert.
 
Göran Andersson said:
The MS Access database doesn't have any scope_identity() function. You
have to use the @@identity variable.

The identity has session scope, so you need to use the same database
connection as for the insert.
Click to expand...




Yes I did find some googling about @@identity
I did find this line - looks like a full example on the bottom:
http://www.codeguru.com/forum/archive/index.php/t-392246.html


But I could not find exactly how to create the query in the dataset
designer.

I went to the Dataset Designer, then to the People Table, right clicked,
add new query.
Then I picked the second one from the list "SELECT which returns a single
value",
Then for the query I did try:
Select @@Identity
or
Select @@Identity from people
and a couple others.

Basically I do know that with jet I have to use the '?' as a place holder
for a parameter in, but am unsure of how to write the query here to return
the int. The query builder complains about the @ symbol.

Or is it that I CANNOT do the @@Identity as part of the dataset designer and
must create a sub -and somehow add a handler to my tableadapter update that
I am calling from my dataset

My datetimestamp idea did the trick but I know that is not the 'proper'
solution.

Thanks,

Miro
 
Back
Top