Reset row numbers with query everytime i delete a record in my App?

[Rashidfahim]

As the title say it all. I have only one table and the data in the table being stored temporarily and get calculated amounts based on that data. I need id field as sequence.
https://support.microsoft.com/en-us/...alue-in-access
This link tells us how to do that in Access but i need to do it in my app using sql query everytime i delete a record.

[jmcilhinney]

As the title say it all.

I've seen that posted many, many times and it has never once been true.

The ID for a record should be set when that record is inserted and that ID should never change. The two main reasons that people use surrogate keys (e.g. AutoNumber in Access) are simplicity and so they know the data won't change. Why exactly do you think you need the IDs of records to change when a record is deleted? Is it just because you don't like to see gaps or don't think you should? If so, you should think again. If there's some other reason, I'd be interested to hear what it is because, in situations like this, I'm yet to hear one that seems to actually represent a need at all.

If you want a sequence number for your records that may change then you can certainly achieve that but it should not be considered the ID for the records.

[Rashidfahim]

I want to make them sequence just for simplicity because my application will be used by an average computer user and user will insert some data into it then do some calculation based on that data and print the report. Thats all. No permanant records and no table relation in my case.

[Rashidfahim]

If it is not advised to reset number then please tell me how to create a column named EntryNumber and use it every time to insert record. Is it practical or not?

[wes4dbt]

I want to make them sequence just for simplicity because my application will be used by an average computer user and user will insert some data into it then do some calculation based on that data and print the report. Thats all. No permanant records and no table relation in my case.

Not sure why you need a database table or an ID field if you don't store the data. If all you want the ID for making a list of things look orderly, then don't make the field an AutoNumber and then enter the ID number right before creating the list.

[wes4dbt]

If it is not advised to reset number then please tell me how to create a column named EntryNumber and use it every time to insert record. Is it practical or not?

Obviously you know how to create a column, right. How you add a line counter when inserting will depend on HOW you are adding data to the database table. You need to post the relevant code.

[kpmc]

You should consider having the SQL server handle your INT column identity requirement. Then load your schema

                YourDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                YourDataAdapter.FillSchema(dt, SchemaType.Source) 'dt=your datatable

[Rashidfahim]

Obviously you know how to create a column, right. How you add a line counter when inserting will depend on HOW you are adding data to the database table. You need to post the relevant code.

This is my code to save data. Can you edit it so i can add a number to each record with this code which will be sequential? Thanx

 Public Sub SaveData()

        Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Access Database Projects\Database8.accdb"

        Dim query As String = "INSERT INTO BETTABLE (CustomerName, Rate, Amount, FavTeam, BetType, CalculatedAmount) VALUES (@Name, @Rate, @Amount, @Team, @Type, @cAmount)"

        Using conn As New OleDbConnection(conString)

            Using cmd As New OleDbCommand(query, conn)

                conn.Open()

                cmd.Parameters.AddWithValue("@Name", cbCustomers.Text)
                cmd.Parameters.AddWithValue("@Rate", txtRate.Text)
                cmd.Parameters.AddWithValue("@Amount", CInt(txtAmount.Text) * 1000)
                cmd.Parameters.AddWithValue("@Team", cbTeam.Text)
                cmd.Parameters.AddWithValue("@Type", cbType.Text)
                cmd.Parameters.AddWithValue("@cAmount", Val(CInt(txtAmount.Text) * 1000) * Val(txtRate.Text))

                cmd.ExecuteNonQuery()

            End Using

        End Using

    End Sub

[kpmc]

Can you use a GUID instead? Otherwise you should really consider having the SQL server handle your sequential number as I have mentioned.

[Rashidfahim]

Can you use a GUID instead? Otherwise you should really consider having the SQL server handle your sequential number as I have mentioned.

I am using Access Database and this application is for offline usage.

[wes4dbt]

There's more than one way to do this but here's an example,

 Public Sub SaveData()

         Static cnt as integer

        Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Access Database Projects\Database8.accdb"

        Dim query As String = "INSERT INTO BETTABLE (EntryNum, CustomerName, Rate, Amount, FavTeam, BetType, CalculatedAmount) VALUES (@EntryNum, @Name, @Rate, @Amount, @Team, @Type, @cAmount)"

        Using conn As New OleDbConnection(conString)

            Using cmd As New OleDbCommand(query, conn)

                conn.Open()
          
                cnt +=1
                cmd.Parameters.AddWithValue("@EntryNum", cnt)
                cmd.Parameters.AddWithValue("@Name", cbCustomers.Text)
                cmd.Parameters.AddWithValue("@Rate", txtRate.Text)
                cmd.Parameters.AddWithValue("@Amount", CInt(txtAmount.Text) * 1000)
                cmd.Parameters.AddWithValue("@Team", cbTeam.Text)
                cmd.Parameters.AddWithValue("@Type", cbType.Text)
                cmd.Parameters.AddWithValue("@cAmount", Val(CInt(txtAmount.Text) * 1000) * Val(txtRate.Text))

                cmd.ExecuteNonQuery()

            End Using

        End Using

    End Sub

I didn't test this, I just wrote it free hand.

I should mention this method only works if the form is NOT closed and reopen during data entry. I gave this example because you said the table would be emptied after each entry/print session.

[kpmc]

There's more than one way to do this but here's an example,

 Public Sub SaveData()

         Static cnt as integer

        Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Access Database Projects\Database8.accdb"

        Dim query As String = "INSERT INTO BETTABLE (EntryNum, CustomerName, Rate, Amount, FavTeam, BetType, CalculatedAmount) VALUES (@EntryNum, @Name, @Rate, @Amount, @Team, @Type, @cAmount)"

        Using conn As New OleDbConnection(conString)

            Using cmd As New OleDbCommand(query, conn)

                conn.Open()
          
                cnt +=1
                cmd.Parameters.AddWithValue("@EntryNum", cnt)
                cmd.Parameters.AddWithValue("@Name", cbCustomers.Text)
                cmd.Parameters.AddWithValue("@Rate", txtRate.Text)
                cmd.Parameters.AddWithValue("@Amount", CInt(txtAmount.Text) * 1000)
                cmd.Parameters.AddWithValue("@Team", cbTeam.Text)
                cmd.Parameters.AddWithValue("@Type", cbType.Text)
                cmd.Parameters.AddWithValue("@cAmount", Val(CInt(txtAmount.Text) * 1000) * Val(txtRate.Text))

                cmd.ExecuteNonQuery()

            End Using

        End Using

    End Sub

I didn't test this, I just wrote it free hand.

this is problematic.you need to get the value of EntryNum first and += that value before writing it back

[wes4dbt]

this is problematic.you need to get the value of EntryNum first and += that value before writing it back

No you don't, each entry session will start with an empty table.

I want to make them sequence just for simplicity because my application will be used by an average computer user and user will insert some data into it then do some calculation based on that data and print the report. Thats all. No permanant records and no table relation in my case.

[kpmc]

ok, i missed that bit

[jmcilhinney]

No permanant records and no table relation in my case.

Then why are you using a database at all? Is it just because you think that that is the only way to have a table? It's not. You can simply create a DataTable or DataSet in your app to work with current data. That's what you would use to store data locally even if you did have a database, but you can populate one with data from any source. If the data is not to be persisted between sessions then that's all you need. In that case, you can configure one of the DataColumns in your DataTable to generate sequential IDs automatically by setting the AutoIncrement property to True. There's not really any good reason to change the auto-generated values when a row is deleted.

[Rashidfahim]

Thanx Alot guys. Your answers helped me alot.

[kpmc]

Then why are you using a database at all? Is it just because you think that that is the only way to have a table? It's not. You can simply create a DataTable or DataSet in your app to work with current data. That's what you would use to store data locally even if you did have a database, but you can populate one with data from any source. If the data is not to be persisted between sessions then that's all you need. In that case, you can configure one of the DataColumns in your DataTable to generate sequential IDs automatically by setting the AutoIncrement property to True. There's not really any good reason to change the auto-generated values when a row is deleted.

Didnt make any sense to me either /shrug