Update ALL changes in a table

Tableadapter update configuration for VS2017

Dear members, Sorry, I have to harass you again with a VB.net SQL problem in VS2017. I searched the internet for many days now, but did not find a solution for my problem. All the examples are or C and outdated.

The situation is as follows: • Created a Tabteadapter update method to change fiels in a DB. • Modified a field in all rows (RENUM field Oserial). • Tried with Tableadapter to update the changes in the DB. • Always an error is generated, except if the Oserial field is set to the value of another field or literal (set Oserial = PID) in this case the DB has realy changed but not with the right values.

I do not understand how to configurea Update method that Update all changes in the tabletwithout setting new values. Hopefully someone can help me out or tell me where to find a sample source code.

My code:-----------------------

Public parmPID As String = "159"

Public Sub Renum()
    Dim n As Integer = 0

    Dim DS As New Pr2DataSet1()
    Dim TA As New Pr2DataSet1TableAdapters.OBjectsTableAdapter()

    TA.OBjectsFillByPIDOserial(DS.OBjects, parmPID)
    Try
        For Each dr As DataRow In DS.Tables(0).Rows
            n = n + 1
            DS.Tables(0).Rows(n - 1).Item   ("Oserial") = n 
       Next

        TA.ObjectsUpdateOserial(parmPID)
        TA.Connection.Close()

    Catch x As Exception
        Labelbox.Text = x.ToString
    End Try
End Sub

DB design: ---------------------------

CREATE TABLE [dbo].[OBjects] (
[PID]          INT   NOT NULL,
[OID]  INT   IDENTITY (1, 1) NOT  ULL,
[Oserial]      INT    NOT NULL,

SQl Query 1 :----------------------

UPDATE [dbo].[OBjects] 
set Oserial= PID
WHERE (PID = @parmPID)

this works OK but with changed PID vlues

SQl Query 2 :----------------------

UPDATE [dbo].[OBjects] 
set Oserial 
WHERE (PID = @parmPID)

ERROR: :----------------------

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WHERE'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,

Of course this doesn't work:

UPDATE [dbo].[OBjects] 
set Oserial 
WHERE (PID = @parmPID)

You're not specifying what to set Oserial to. You have to specify a value to set that column to, just as you're doing in the SQL that you say does work. You can specify a literal value, an expression or a parameter but you have to specify something that evaluates to the value you want to set the column to.

The fact is, though, that you're just doing the saving wrong. Your table adapter already has a method that will save all the changes for you: the Update method. You don't need anything else. Just change this:

TA.ObjectsUpdateOserial(parmPID)

to this:

TA.Update(DS.OBjects)

and you're good to go.

The way a table adapter works is that it contains one or more queries, i.e. SELECT statements, and three actions statements, i.e. one each of INSERT, UPDATE and DELETE statements.

By default, the table adapter has Fill, GetData and Update methods. You can call Fill or GetData to execute the default query, which is basically a SELECT * and should be left that way. The Fill method will populate an existing DataTable while the GetData method will create a new DataTable and populate it. You normally use Fill for a DataTable in a DataSet and GetData for a lone DataTable.

Once you have made any changes you want to make, you call Update and those changes will be saved. Update executes the InsertCommand for all DataRows with a RowState of Added, the UpdateCommand for Modified rows and the DeleteCommand for Deleted rows.

If you want to add another query, e.g. to filter by a specific column, then you can do that in the designer. Doing so will add two new methods equivalent to Fill and GetData but executing your new query rather than the default query. You should use the provided FillBy and GetDataBy prefixes for those methods and name them after the column(s) in the filter. In your case, if you're filtering by PID then those methods should be named FillByPID and GetDataByPID.

Whether you use those methods to populate a DataTable or the defaults, you still use Update to save all the changes in a DataTable. If you want to see the SQL used in those action commands, select your table adapter in the DataSet designer and take a look at the InsertCommand, UpdateCommand and DeleteCommand in the Properties window.