SqlCommandBuilder Update Command is Generating Command but Not Updating Database From DataGridView

I am inheriting a form class (Form1) for Form 2. Form 1 code works without error. In Form 1, I use SqlCommandBuilder(SQL.DBDA).GetUpdateCommand to generate the update command for my Datagrid to pass to SQL data table which again works perfectly and the table is updated successfully. The SQL command text for Form 1 Update is shown here:

In Form 2, I write the following for the update command, where the only difference is Selecting the Table shown here:

SQL.ExecQuery("SELECT * FROM dtbRateVerse;")
SQL.DBDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.DBDA).GetUpdateCommand
MsgBox(SQL.DBDA.UpdateCommand.CommandText)
SQL.DBDA.Update(SQL.DBDT)

The command text for this update command is shown here:

It is not dissimilar to the successful update command shown in Form1 (image 1). Still, no data is passed to the SQL from the Gridview.

I also tried writing a dynamic Update statement without using the command builder shown below. The text of this statement generates an accurate SQL command but again, nothing passed to the database. This code is shown here:

For i = 1 To colEnd
    colName.Add("[" & DataGridView1.Columns(i).HeaderText.ToString & "]")
Next
For i = 1 To colEnd
    For y = 0 To Me.DataGridView1.RowCount - 1
        For n = 1 To colEnd
            gridVals.Add(DataGridView1.Rows(y).Cells(n).Value.ToString)
        Next
        With Me.DataGridView1
            SQL.AddParam("@PrimKey", .Rows(y).Cells(0))
            cmdUpdate = "UPDATE " & tbl_Name & " SET " & colName.Item(i - 1) & "=" & gridVals.Item(i - 1) & " WHERE ID=@PrimKey;"
            SQL.ExecQuery(cmdUpdate)
        End With
    Next
Next

If anyone has any ideas/ solutions on what I need to do to get the update command working properly, I’d really appreciate it. Thanks!

Added ExecQuery methdod per request below:

Public Class SQLControl
    Private DBConnect As New SqlConnection("SERVER STRING HERE")
    Private DBCmd As SqlCommand

    'DB DATA
    Public DBDA As SqlDataAdapter
    Public DBDT As DataTable

    'QUERY PARAMETERS
    Public Params As New List(Of SqlParameter)

    'QUERY STATISTICS
    Public RecordCount As Integer
    Public Exception As String

    Public Sub New()
    End Sub

    'ALLOW CONNECTION STRING OVERRIDE
    Public Sub New(ConnectionString As String)
        DBConnect = New SqlConnection(ConnectionString)
    End Sub

    'EXECUTE QUERY SUB
    Public Sub ExecQuery(Query As String)
        'RESET QUERY STATS
        RecordCount = 0
        Exception = ""

        Try
            DBConnect.Open()
            'CREATE DATABASE COMMAND
            DBCmd = New SqlCommand(Query, DBConnect)

            'LOAD PARAMS INTO DB COMMAND

            Params.ForEach(Sub(p) DBCmd.Parameters.Add(p)) 'LAMBDA EXPRESSION

            'CLEAR PARAMS LIST
            Params.Clear()

            'EXECUTE COMMAND & FILL DATASET
            DBDT = New DataTable
            DBDA = New SqlDataAdapter(DBCmd)
            RecordCount = DBDA.Fill(DBDT)

        Catch ex As Exception
            'CAPTURE ERROR
            Exception = "ExecQuery Error: " & vbNewLine & ex.Message
        Finally
            'CLOSE CONNECTION
            If DBConnect.State = ConnectionState.Open Then DBConnect.Close()
        End Try

    End Sub

    'ADD PARAMS

    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New SqlParameter(Name, Value)
        Params.Add(NewParam)
    End Sub

    'ERROR CHECKING
    Public Function HasException(Optional Report As Boolean = False) As Boolean
        If String.IsNullOrEmpty(Exception) Then Return False
        If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
        Return True
    End Function
End Class

Answer

The issue appears to be as I suspected it was. Here’s for code from the form:

SQL.ExecQuery("SELECT * FROM dtbRateVerse;")
SQL.DBDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.DBDA).GetUpdateCommand
SQL.DBDA.Update(SQL.DBDT)

In that, you first call ExeQuery and finally call Update on the data adapter and pass the DBDT DataTable. In your ExecQuery method, you have this:

DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)

That means that the first code snippet is going to be calling Update and passing a DataTable that was just freshly created and populated. Why would you expect that DataTable to have any changes in it to save? This is an example of why I think DAL classes like this are garbage.

If you’re going to be using a class like that then you should be creating a command builder inside it when you create the data adapter, e.g.

'DB DATA
Public DBDA As SqlDataAdapter
Public DBCB As SqlCommandBuilder
Public DBDT As DataTable

and:

'EXECUTE COMMAND & FILL DATASET
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
DBCB = New SqlCommandBuilder(DBDA)
RecordCount = DBDA.Fill(DBDT)

Now there’s no need to call ExecQuery again or create your own command builder. Just call ExecQuery once when you want the data, get the populated DataTable, use it and then call Update on the data adapter and pass that DataTable when it’s time to save.

That said, you don’t even necessarily need to change that class. If you already have an instance and you already called ExecQuery then it already contains the data adapter and the DataTable. You can still create your own command builder if you want but just don’t call ExecQuery again and lose the objects you already had. If you changed that first code snippet to this:

Dim commandBuilder As New SqlClient.SqlCommandBuilder(SQL.DBDA)

SQL.DBDA.Update(SQL.DBDT)

Then it would work, assuming that you are using the same SQLControl instance as you used to get the data in the first place.