auto generate a value in database

i have a C#.net 2005 winform application

in my ACCESS db patient table, i have PK patientID and index patientNo. on my formPatient, i have code to create a new patient and update the details to database.

this.patientBindingSource.AddNew();

this.patientDataTableAdapter.Update(patientDataset.patient);

I made the patientNo databound field to be readonly, I want it's value to be generated automatically based on the newly generated patientID.

should i make the process "of generating patientNo based on new patientID" a stored procedure or a database trigger or others and how

please help and be more specific in code as i am quite new to this scenario

.NET Development12

Hi,

why don't you use patientID in the first place. What extra value would the patientNo have

If you need both I would recommend a trigger as that would always execute on inserts. In the case of SP you could only make the advise to use the SP but someone could execute direct INSERT commands and set patientNo to any value.

--
SvenC

I tried to figure out how to create a trigger in MS ACCESS db, but failed, please help more

i asked around and heard that MS ACCESS does not support triggers at all. so what can i do now i guess i'd have to catch the newly created patientID and hardcode the patientNo based on it......... the question is: after i execute this.patientBindingSource.AddNew();, how can i catch the patientID created for the new record

Hi,

sorry - never worked with BindingSource. Doesn't your control get updated with the new row columns so that you can read its value from the bound control

--
SvenC

But it should be used in same connection , here is my program that I can get the result:

//conn is OleDbConnection object , cmd is OleDbCommand object

conn.Open();

cmd = new OleDbCommand(conn);

//some code for insertion

//if inserted

//here result is my new column's pkid

conn.close();

So i think you can try this code:

this.patientBindingSource.AddNew();

if (this.patientDataTableAdapter.Update(patientDataset.patient) > 0)

{

OleDbConnect conn = this.patientDataAdapter.Connection;

OleDbConnect cmd = new OleDbCommand(conn);

}

You can do it in the connection by simply appending the select to the adapter's Insert command SQL. If you don't send it in the insert SQL when the adapter's update, then there is a possibilty that a new record will get inserted at the same moment, thus incrementing the identify before you retrieve it. This will result in the wrong identity being returned.

thx tang, i got around it this way:

use "SELECT Max(staff_id) FROM staff" to retrieve the latest staff_id

format (staff_id +1) to ("000")

this.staffBindingSource.AddNew();

give this value to the databound component staff_noTextBox.text

this.staffTableAdapter.Update(dataset1.staff)

This is the most straightforward way for me atm, i used ACCESS 2003 db for the sake of simplicity because the size of the project is fairly small,

but i found that it's quite different to mssql stuff.

This method is not guaranteed to get you the correct ID. If this is a multiuser database and 2 updates happen at the same time, it is likely that you may query for the ID right after the second update occured, thus returning your the id of the second update instead of the first. The only way to ensure the correct ID will be reuturned, is to add it to the end of the insert SQL. If you do it using the approach you have currently proposed, it will work as long as the database is only updated by a single user.

Update:

The max query method is problematic because if 2 users query the database with this query at the same time, they will end up with the same ID. Then when they both try to update their respective new records with that ID, one of them will fail as it will be a Primary Key violation. Another issue with using this method is that it requires you to manage incrementing keys yourself. This means that if another applicaiton updates against the same table, it will be required to duplicate the key logic in order to keep the data consistent. If however you use an autonumbering field on the table, then you can rest assured that keys will be propertly assigned regardless of which application does the update.

Cheers

Glenn