[02/03] Autonumber field problem

[ahassan99]

Hi guys,

I hope somebody can help me with this.

I am developping a software with an Access 2003 database. I am trying to have an SNO field which should be an autonumber. Access 2003, as you probably know, has got limitation with Autonumber as it doesn't reuse the numbers. So I wrote code to do the check the total number of records before adding a new one, then increment it by one and add the new record against that. The problem I am facing is:

When deleting record, I cannot get the the software to re-number the records as it was. I am showing the records in a datagrid.

Example: If I have records: 1,2,3,4 & 5. Then I delete record 3 and add a new record, the new record will have the SNO 5 which is already exist. I wrote the following in a button click event. I can successfully re-number the recrods but I cannot save them in the database. If I click the button more than once, it throws the following erorr:

Concurrency Violation, the Update command affected 0 records.

DaCount is my Data Adapter
DSCont1 is my Dataset

Any help will be vey much appeciated !

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

maxrows = DScont1.Tables("Container").Rows.Count
Dim x As Integer = 1
Dim c As Integer
'Ds1.Tables("CBMDB").Rows(inc).Item(0) = txtitemcode.Text
Dim cb As New OleDb.OleDbCommandBuilder(dacont)

Try
While x <= maxrows

DScont1.Tables("Container").Rows(c).Item(4) = x
cb.RefreshSchema()


x += 1
c += 1

End While
dacont.Update(DScont1, "Container")
cb.RefreshSchema()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

[jmcilhinney]

Why would you want to do that? If you delete a record then just forget about that ID. Why would you even bother trying to reuse it? Is each ID really so precious?

[MaximilianMayrhofer]

You should never specify ID number when you're using an autonumber field. It will fill itself in when a new record is added. That's kind of the point, you see..

[ahassan99]

Sorry for the confusion guys..

First of all, the field is not an autonumber anymore. It is just of data type number. When I used the data type Autonumber, I figured out that MS Access has got this problem that it does not reuse the numbers. So it doesn't give me what I need you see

Secondly, my taget is to get a Serial Number column within my database whose values are given automatically by the system.

I hope you can understand me better now

Thanks for the prompt replies ... and waiting for more

[Shaggy Hiker]

So you switched the autonumber to just a number, but it still has to be unique, right?

When you get the next record ID value, do you want to pick one of the gaps, or do you want to renumber the existing items such that there are no gaps and the next record gets the next largest number?

That second option is not going to be the fastest thing you've ever written, so the first option would be the best if it is acceptable to you.

[MaximilianMayrhofer]

Actually ahassan, I know what you're talking about, and Access can be told to reuse the numbers. If you go under Access Options > Current Database you can select an option to "Compact on close". What this will do, among other things, is remove gaps in the autonumbers.

For example, if you have 1,2,3,5,6,9 after deleting some records. When the database is closed, the autonumber fields will rename themselves to 1,2,3,4,5,6. Since you won't be opening the database physically, you can also make the database compact programmatically, which achieves the same result.

[ahassan99]

So you switched the autonumber to just a number, but it still has to be unique, right?

When you get the next record ID value, do you want to pick one of the gaps, or do you want to renumber the existing items such that there are no gaps and the next record gets the next largest number?

That second option is not going to be the fastest thing you've ever written, so the first option would be the best if it is acceptable to you.

I didn't understand actually what did you mean in the first option. What did you mean about it will pick up one of the gaps ?. But anyway, I think what I am looking for is the second option. That it should re-number the records !!

[ahassan99]

Actually ahassan, I know what you're talking about, and Access can be told to reuse the numbers. If you go under Access Options > Current Database you can select an option to "Compact on close". What this will do, among other things, is remove gaps in the autonumbers.

For example, if you have 1,2,3,5,6,9 after deleting some records. When the database is closed, the autonumber fields will rename themselves to 1,2,3,4,5,6. Since you won't be opening the database physically, you can also make the database compact programmatically, which achieves the same result.

Thanks MaximilianMayhofer,

I appreciate your idea very much. I would appreciate if you let me know how to do this programmatically, as I need this to be done during runtime immediately after a Delete operation.

Appreciate your help guys and prompt response

[ahassan99]

Thanks Guys for the reply. Please bare with me as I am new to all what I am doing.

In fact, last night I thought of an idea different that what I am doing.

To understand better, The records I am talking about are for containers. The user should select a container or add a new one. Then start adding items into another table against the selected container.

I will not enable the user to delete the record, but to just "Disable" it some how if possible. Means he clicks on a button or something and the selected container gets closed in the sense that he cannot add items to it anymore.

As I am showing the data in a datagrid, any ideas on how to do this ????
If possible to show the numbers in this format e.g. 1/2007,2/2007,3/2007...etc


What do you think ?!!

[ahassan99]

One more thing guys, I forgot to mention that the SNO field is not an autonumber datatype anymore, I converted it to number