Database Update from dataset (how to retrieve primary key assigned by DB?)

[oxbow123]

I have a database Access table "CUSTOMER"
the primary key is an integer AUTO increment.

In my program when I create a customer, I add a row in my dataset with the primary key empty, so access assign itself the key when I use Adpxxx.update(DSCustomer).

But I need to know at that time the CUSTOMER key generated by access for an oher process.

Is there someone who can help me ?

Many thanks

[Lunatic3]

That you need can be done in someway or another, but if you really need that why not produce a value for the field yourself.

Maybe you need that filed to work on another table (linked ones). If thats the case why not creating relationships in datasets?

[hellswraith]

In SQL Server I use this inside a stored proceedure. Maybe there is something similar in access to use to get the identity.

INSERT INTO Topics
(UserName, IPAddress)
VALUES
(@UserName, @IPAddress)

SELECT @PostID = @@IDENTITY

[oxbow123]

I need to display "Customer xxx has been created" just after the DB update.

Any solution ?

[Lunatic3]

You can read that value from the dataset. Because its value is decided at dataset level. So when you add a row to your dataset table the autonumber filed is automatically filled in the row too.
Consider this code:

Dim dr as DataRow
dr=myds.mytb.NewRow
myds.mytb.Rows.Add(dr)
Messagebox.Show(dr("the_Autonumber_field").Tostring)

That should solve your problem.

[hellswraith]

Originally posted by Lunatic3
You can read that value from the dataset. Because its value is decided at dataset level. So when you add a row to your dataset table the autonumber filed is automatically filled in the row too.
Consider this code:

Dim dr as DataRow
dr=myds.mytb.NewRow
myds.mytb.Rows.Add(dr)
Messagebox.Show(dr("the_Autonumber_field").Tostring)

That should solve your problem.

That is weird, I wouldn't think you could do that. What happens if someone else were to update the database before and that number was taken? Then the autonumber would be different...wouldn't it? Or is the DataSet not totally disconnected in this scenerio?

[Lunatic3]

That is weird, I wouldn't think you could do that...

Not weird really. If you believe that dataset table acts somewhat like a real database table then you shouldnt expect it to have its primary key set to Null or equal in all the rows, so it has to define a value for the primarykey which is considered to be an AutoNumber.

What happens if someone else were to update the database before and that number was taken?

I dont know what happens in this case. Maybe if you use optimistic cuncurrency the dataset updates the row which is added by another user, or ... Someone should test it.

[vujjeni]

Doesn't the FillSchema() method of the adapter retrieve the primary key information ?

[EyeTalion]

oxbow123 if you can save a date/time field in your database, store that time in a variable then do a search on that field