Hallo,

I am working with Sql server 2005 and Vb.net.
I have a table with 3 fields - pay_id, pay_name and account_num.

pay_id is set as Primary Key and is automatically incremented when a record is added. Now, when you delete a record(s), and add another record the pay_id remembers the last number (even if deleted) and increments to create the new pay_id. For example, I had the records below

pay_id pay_name account_num

2 John 7836434
3 Ali 3473443
4 James 8243245

If the last record is deleted (pay_id = 4), the next new record will have a pay_id of 5 as shown below;

pay_id pay_name account_num
2 John 7836434
3 Ali 3473443
5 Julius 2752457

Notice, pay_id is 5 rather than 4. Therefore I have two problems;

1) I want to amend this so that when a record is deleted then it reads the most present last record and increment the pay_id..
2) My table (tblpay) pay_id's are so random due to deletion of records and creating new records. What will be the easier way of having pay_id's start from 1 onwards in an incremental fashion...

Thanks