none
The Malfunction of Update Command with Tableadapter in VB.net 2015 RRS feed

  • Question

  • Dear All,

    Firstly, May I introduced my self, I am a new one, who has been learned the VB.net. For the last time, I have developed the Visual Basic and Visual FoxPro.

    Please help me in obtaining a solution about creating  a code for adding and updating a data table.

    My code is below, like that

            Dim i As Integer
            Dim update1 As DataTable = Me.DataGridView1.DataSource
            Try
                Me.Validate()
                Me.LatihanDataSetBindingSource.EndEdit()
                Me.LatihanDataSet.Tables(0).Rows(1).Item(1) = TextBox1.Text
                i = DatamahasiswaTableAdapter1.Update(LatihanDataSet.datamahasiswa)
                Me.DatamahasiswaTableAdapter1.Update(update1)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            MessageBox.Show("no of rows updated=" & i)

    The result : This happened a bug error

    The other message : This need a valid update command.

    Please help me to create the valid code for update a data.

    Thank you for you time and attention,

    My best regards,

    Muljanto

    Wednesday, August 8, 2018 1:33 AM

All replies

  • Hello,

    A common reason for "This need a valid update command" is that the table has no primary key. For the TableAdapter to create a proper update command the database table requires a primary key. Can you indicate if you have a primary key by checking the .xsd file which displays the table, if there is a primary key there will be a key icon on that field/column.

    For a SQL-Server database table as follows DepartmentIdentifier is a primary key and auto incrementing.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, August 8, 2018 1:48 AM
    Moderator
  • Hello all my friend,

    I got the reply for my question, like that :

    A common reason for "This need a valid update command" is that the table has no primary key. For the TableAdapter to create a proper update command the database table requires a primary key. Can you indicate if you have a primary key by checking the .xsd file which displays the table, if there is a primary key there will be a key icon on that field/column.<o:p></o:p>

    My respond : 

    In my perception, the primary existed on the databases. It is not a problem in data bases, the problem is how to create ade for the update command. In the other thing, please help me provide the code for update command

     

    Thanks so much,

     

    My best regards,

     

     

    Muljanto

    Wednesday, August 8, 2018 2:02 AM
  • Hi

    The SqlDataAdapter does not automatically generate the Transact-SQL statements needed to implement the coordination between the changes to the DataSet and the associated SQL Server instance. However, if the SelectCommand property of the SqlDataAdapter is set, a SqlCommandBuilder object can be created to automatically generate the single The table updates the Transact-SQL statement. Then, the SqlCommandBuilder will generate any other Transact-SQL statements that are not set.

    Define a SqlCommandBuilder:

        Dim thisAdapter As SqlDataAdapter = New SqlDataAdapter(strQuery, conn)
        Dim thisBuilder As SqlCommandBuilder = New SqlCommandBuilder(thisAdapter)

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 8, 2018 2:05 AM
  • Hello all my friend,

    I got a reply email, like that 

    The SqlDataAdapter does not automatically generate the Transact-SQL statements needed to implement the coordination between the changes to the DataSet and the associated SQL Server instance. However, if the SelectCommand property of the SqlDataAdapter is set, a SqlCommandBuilder object can be created to automatically generate the single The table updates the Transact-SQL statement. Then, the SqlCommandBuilder will generate any other Transact-SQL statements that are not set.

    Define a SqlCommandBuilder

        Dim thisAdapter As SqlDataAdapter = New SqlDataAdapter(strQuery, conn)

        Dim thisBuilder As SqlCommandBuilder = New SqlCommandBuilder(thisAdapter)

    I have tried this command, but all command is in red-underlined

    It means a vb.net do not recognise this command.

    It has not yet solved our problem.

    Thanks so much,

    My best regards,

    Muljanto

    Wednesday, August 8, 2018 2:24 AM
  • First off, never double post. It may appear to you as not double posting but it is.

    So I ask again, do you see the key next to the primary key as shown below?

    Here is a simple example using the above. If I remove the primary key I will get the same error as you get.

    Public Class Form1
        Private Sub CustomersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _
            Handles CustomersBindingNavigatorSaveItem.Click
    
            Me.Validate()
            Me.CustomersBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.NorthWindAzure1DataSet)
    
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 
            ''NorthWindAzure1DataSet.Customers' table. You can move, or remove it, as needed.
            Me.CustomersTableAdapter.Fill(Me.NorthWindAzure1DataSet.Customers)
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, August 8, 2018 2:34 AM
    Moderator
  • Hello Mrs Karen,

    Firstly, thanks so much for your reply email.

    I have revised this code

    - by restructuring table adapter

    - by putting primary key on table adapter

    The result : It is no bug error, but the record has not yet updated.

    The code :

    Dim i As Integer
            Dim latihandataset As New latihanDataSet()
            Dim datamahasiswatableadapter As New latihanDataSetTableAdapters.datamahasiswa1TableAdapter
            Try
                Me.Validate()
                Me.LatihanDataSetBindingSource.EndEdit()
                Me.LatihanDataSet.Tables(0).Rows(1).Item(1) = TextBox1.Text
                i = DatamahasiswaTableAdapter1.Update(Me.LatihanDataSet.datamahasiswa)
                Me.DatamahasiswaTableAdapter1.Update(Me.LatihanDataSet.datamahasiswa)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            MessageBox.Show("no of rows updated=" & i)

     Please give us a correction code for this one.

    Thank you so much for your time and attention,

    My best regards,

    Muljanto

    <o:p></o:p>

    <o:p></o:p>



    • Merged by Alex Li-MSFT Wednesday, August 8, 2018 5:17 AM Same as the two cases
    • Edited by Muljanto Wednesday, August 8, 2018 9:16 AM Updated code
    Wednesday, August 8, 2018 3:40 AM
  • The result : It is no bug error, but the record has not yet updated.


    Hi,

    The  DataAdapter.Update is a processing flow that acquires the change record(inserts,deletes,update) in the datatable, and then inserts, deletes, and update each record added, deleted, and updated   separately.
    When the datatable is not obtained from the data source table, but is customized (obtained from other data sources, or simply built manually, obtained from the modified datagridview), there is no relative deletion and modification concept above.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 9, 2018 6:11 AM
  • Dear All,

    I am not understand so briefly.

    The statement  :

    The  DataAdapter.Update is a processing flow that acquires the change record(inserts,deletes,update) in the datatable, and then inserts, deletes, and update each record added, deleted, and updated   separately.

    My Code :

    Dim i As Integer

            Dim TABLES1 As DataTable = New DataTable                              (**)
            Try
                Me.Validate()
                Me.LatihanDataSetBindingSource.EndEdit()
                Me.LatihanDataSet.Tables(0).Rows(1).Item(1) = TextBox1.Text
                i = Me.DatamahasiswaTableAdapter.Update(TABLES1)               (*)
               Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            MessageBox.Show("no of rows updated=" & i)

    (**) = It is dimension, that I make. Please make a correction.

    (*) = Please you can revise this code statement, I have tried so many ways....it have failed.

    Thursday, August 9, 2018 6:56 AM
  • Hi,

    I also think should Define a CommandBuilder:

    https://stackoverflow.com/questions/18578077/error-update-requires-a-valid-updatecommand-when-passed-datarow-collection-wit

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 9, 2018 8:33 AM
  • Okay, let's start off with; there is no DataAdapter nor CommandBuilder when you have a TableAdapter. Hidden from view are classes generated by the data wizard. In the example below there is a primary key id. If id was not setup as a primary key when you configured the classes in your project then no update command would be generated and no delete command would be generated because they need a primary key for the UPDATE or DELETE statement.

    Here is what is hidden under the .xsd

    So this is what one of the update command looks like

    <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _
        Global.System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "15.0.0.0"),  _
        Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")>  _
    Public Overloads Overridable Function Update(ByVal dataTable As DemoCreatingDataSet.Table_1DataTable) As Integer
        Return Me.Adapter.Update(dataTable)
    End Function

    Note the declarator indicating the code was generated and this can not happen at runtime. Also note a call is made to an Adapter which can not be altered at runtime. 

    Once you accept the above then the next step is reconfigure as shown below once the primary key has been set. If you simply set the primary key in the database table, Visual Studio has no idea you done this and will continue to give errors as shown after this image.

    Here is a UPDATE generated by Visual Studio with a primary key.

    Me._adapter.UpdateCommand.CommandText = "UPDATE [dbo].[Table_1] SET [FirstName] = @FirstName, [LastName] = @LastName, [Dat"& _ 
        "eJoined] = @DateJoined WHERE (([id] = @Original_id) AND ((@IsNull_DateJoined = 1"& _ 
        " AND [DateJoined] IS NULL) OR ([DateJoined] = @Original_DateJoined)));"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"SELECT i"& _ 
        "d, FirstName, LastName, DateJoined FROM Table_1 WHERE (id = @id)"
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, August 9, 2018 10:12 AM
    Moderator