Reset row numbers with query everytime i delete a record in my App?-VBForums
Results 1 to 17 of 17

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    9

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

    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.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,670

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

    Quote Originally Posted by Rashidfahim View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    9

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

    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.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    9

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

    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?

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,524

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

    Quote Originally Posted by Rashidfahim View Post
    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.

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,524

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

    Quote Originally Posted by Rashidfahim View Post
    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.

  7. #7
    Hyperactive Member
    Join Date
    Sep 2017
    Posts
    309

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

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

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

  8. #8

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    9

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

    Quote Originally Posted by wes4dbt View Post
    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

    Code:
     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

  9. #9
    Hyperactive Member
    Join Date
    Sep 2017
    Posts
    309

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

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

  10. #10

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    9

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

    Quote Originally Posted by kpmc View Post
    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.

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,524

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

    There's more than one way to do this but here's an example,
    Code:
     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.
    Last edited by wes4dbt; Feb 1st, 2018 at 04:28 PM.

  12. #12
    Hyperactive Member
    Join Date
    Sep 2017
    Posts
    309

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

    Quote Originally Posted by wes4dbt View Post
    There's more than one way to do this but here's an example,
    Code:
     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

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,524

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

    Quote Originally Posted by kpmc View Post
    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.

  14. #14
    Hyperactive Member
    Join Date
    Sep 2017
    Posts
    309

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

    ok, i missed that bit

  15. #15
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,670

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

    Quote Originally Posted by Rashidfahim View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  16. #16

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    9

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

    Thanx Alot guys. Your answers helped me alot.

  17. #17
    Hyperactive Member
    Join Date
    Sep 2017
    Posts
    309

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

    Quote Originally Posted by jmcilhinney View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width