How to Use an Update Statement in SQLDataAdapter in BP automate

I could not update table using this code, please advice me how to repair: User that I use got insert grant to table

SQL query

UPDATE [UFC].[dbo].[CreateProfile]
SET ATTEMPT = '4'
WHERE SKP = 804401;

As suggested i tried to use below code, unfortunately not work either:


' Assume success
Success = True
Message = ""

Try
    Using cmd As New SqlCommand()
        cmd.Connection = moConnection
        cmd.Transaction = moTransaction
        cmd.CommandText = SQL

        Using adapter As New SqlDataAdapter()
            adapter.UpdateCommand = cmd //------comment:when use 
        End Using
    End Using

Catch ex As Exception
    Success = False
    Message = ex.Message

End Try

Solution

This article provides a guide on how to perform an Update using a SqlDataAdapter.

First, you need to populate the dataSet variable with the schema information of the target table to update and the data currently in said table. This is done via the FillSchema and Fill methods respectively.
Next, find the row you want to update. Since you previously loaded the schema, the table variable uses the primary key to find the row. I assumed the column SKP is your primary key for this table, and as such, I used the value "804401" from your UPDATE query as the value to use to find the row.
After finding the row, you can specify the column you want to update and the value to update it to by doing dataRow("<ColumnName>") = <value>.
Using a SqlCommandBuilder that takes in your SqlDataAdapter, .NET will automatically build the SQL statement for updating the table. Alternatively, you can write your own SQL if you wish.
Call the Update() method on your SqlDataAdapter and pass in the dataset for the table to update and the table name to update.

'Assume success
Success = True
Message = ""
Dim dataSet As DataSet = New DataSet("UpdateDataSet")

Try
    Using adapter As New SqlDataAdapter("SELECT * FROM CreateProfile", moConnection) 'Note - I don't recommend selecting everything, but for brevity I did
        adapter.FillSchema(dataSet, SchemaType.Source, "CreateProfile")
        adapter.Fill(dataSet, "CreateProfile")
        Dim table As DataTable = dataSet.Tables("CreateProfile")
        Dim dataRow As DataRow = table.Rows.Find("804401")
        dataRow("Attempt") = "4"
        Dim objCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(adapter)
        adapter.Update(dataSet, "CreateProfile")
    End Using
Catch ex As Exception
    Success = False
    Message = ex.Message
End Try

Of course I have hard coded values in some spots, to which you'll need to parameterize instead.