Autonumber field

[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

 

[giadich]

Question: if you are managing the "autonumber" column in code, why use "autonumber" then? if you continue this path, why not get rid of "autonumber" and add the logic for deleting/renumber rows?

 

[ahassan99]

In fact, that is what I was trying to do in the above code.

But now, to solve the problem some how, I thought of something else.

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 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 ????

 

[ahassan99]

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