VB.Net DataBase Add Row Not Working After Reset Command-CodePudding

I am creating a database in which data from an sql database can be recorded, saved, edited, etc. I am running into a problem in which once I hit the reset function for my dataset it will not allow me to add a row after the fact. It is almost like it adds the row but does not become visible because it gives the error: System.Data.SqlClient.SqlException: 'Violation of PRIMARY KEY constraint 'PK_TrackMain$'. Cannot insert duplicate key in object 'dbo.TrackMain$'. The duplicate key value is (20). The statement has been terminated.'

Here is my code for creating the dataset from sql:

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim connection As New SqlConnection("Connection String Placeholder")
    Dim Table As New DataTable()
    Dim Adapter As New SqlDataAdapter("SELECT * FROM TrackMain$", connection)
    Adapter.Fill(Table)
    DataGridView1.DataSource = Table
    bind_data()
    nextId = Convert.ToInt32(dt.Rows(dt.Rows.Count - 1)(0))   1
End Sub

Here is my code for adding a row:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim dr As DataRow = dt.NewRow()
    dr(0) = nextId
    dt.Rows.Add(dr)
    nextId  = 1

End Sub

Here is the code for resetting the dataset:

 Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
    Dim connection As New SqlConnection("Connection String Placeholder")
    Dim Table As New DataTable()
    Dim Adapter As New SqlDataAdapter("SELECT * FROM TrackMain$", connection)
    Adapter.Fill(Table)
    DataGridView1.DataSource = Table


End Sub

I need to be able to add rows properly even after I reset the unsaved data. Please let me know if you have any ideas or need more information. Everything is appreciated!

Here is a picture of the table and how I have primary key set:

CodePudding user response：

Try changing the type of Row# to int and make it auto increment. For example:

So values in Row#cloumn will be ordered when adding new rows, then reset the nextId if you want to reset the dataset.

Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
    ...
    DataGridView1.DataSource = Table
    nextId = Convert.ToInt32(dt.Rows(dt.Rows.Count - 1)(0))   1
End Sub

CodePudding user response：

There are a couple of things going on here. You are determining your nextId value from the last row in your grid. The second you sort the grid on a non-primary key field, you'll get the wrong value. Also, if someone else is adding to the database at the same time you will get the wrong value. Using column positions with a SELECT * (instead of using column names) will also bite you down the track if you ever delete a column, etc.

The best solution is to determine the new primary key value on the database server when you INSERT. This could be either using an IDENTITY (auto-incrementing) column, or you could SELECT MAX(primarykeyfieldname) in the VALUES part of your INSERT, or something like that.