Visual Basic Add Row Function Not Working After Reset Function-CodePudding

I will clarify my question. Here is background:

Creating a Database in Visual Studio 2019 using Visual Basic Database consists of a list of Devices and Serial Numbers etc. that I must have account of I have search functions, filtering functions, and a few editing functions

The problem I am encountering is that once I click the reset button (supposed to reset the dataviewgrid unsaved changes back to last saved changes) the add row function no longer works. The code throws no error but simply refuses to add a new row when clicking the button (Before hitting the reset button the add row button works fine)

Here is how I populate my database using 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

I am also attempting to order my first column numerically and therefore I have the NextID value there as well and in my add row function (which may be the culprit)

Here is the add row and then reset function:

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

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
    nextId = Convert.ToInt32(dt.Rows(dt.Rows.Count - 1)(0))   1
End Sub

If any further questions please let me know, and thank you

Edit: Here is my bind_data() Sub

Private Sub bind_data()
    connection.Open()
    Dim cmdTxt As String = "SELECT * FROM TrackMain$"
    da = New SqlDataAdapter(New SqlCommand(cmdTxt, connection))
    Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
    da.Fill(dt)
    Dim source As BindingSource = New BindingSource With {
                                .DataSource = dt
                              }
    DataGridView1.DataSource = source

End Sub

CodePudding user response：

Here's the whole code of my test, and the code works for me.

Private dt As DataTable = New DataTable
Private da As SqlDataAdapter
Private connection As SqlConnection = New SqlConnection("connection string")
Dim nextId As Integer = 0

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    bind_data()
    nextId = Convert.ToInt32(dt.Rows(dt.Rows.Count - 1)(0))   1
End Sub

' Update database by DataGridView.'
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    For i As Integer = DataGridView1.Rows.Count - 1 To 0
        Dim row As DataGridViewRow = DataGridView1.Rows(i)

        If Not row.IsNewRow AndAlso row.Cells(0).Value Is Nothing Then
            DataGridView1.Rows.RemoveAt(i)
        End If
    Next
    DataGridView1.EndEdit()
    da.Update(dt)
End Sub

' Add new Row.'
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim dr As DataRow = dt.NewRow()
    dr(0) = nextId
    dt.Rows.Add(nextId)
    nextId  = 1
End Sub

' Reset the dt and DataGridView.'
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
    Dim Table As New DataTable()
    dt.Clear()
    Dim Adapter As New SqlDataAdapter("SELECT * FROM TrueTrack", connection)
    Adapter.Fill(dt)
    DataGridView1.DataSource = dt
    nextId = Convert.ToInt32(dt.Rows(dt.Rows.Count - 1)(0))   1
End Sub

Private Sub bind_data()
    connection.Open()
    Dim cmdTxt As String = "SELECT * FROM TrueTrack"
    da = New SqlDataAdapter(New SqlCommand(cmdTxt, connection))
    Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
    da.Fill(dt)
    Dim source As BindingSource = New BindingSource With {
                                    .DataSource = dt
                                  }
    DataGridView1.DataSource = source
End Sub
Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
    connection.Close()
End Sub

Edit: DataBase design(cancel auto-Increment of 'Id' ):

You can refer to the following code to sort the DataGridView by 'Id'.

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
    Me.DataGridView1.Sort(Me.DataGridView1.Columns("Id"), ListSortDirection.Ascending)
End Sub

Then change the code to remove the DataGridView row.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    If DataGridView1.Rows.Count > DataGridView1.CurrentCell.RowIndex   1 Then
        DataGridView1.Rows.RemoveAt(DataGridView1.CurrentCell.RowIndex)
    End If
    nextId = Convert.ToInt32(DataGridView1.Rows(DataGridView1.CurrentCell.RowIndex).Cells(0).Value)   1
End Sub