locked
update requires a valid updatecommand when passed datarow collection with modified rows RRS feed

  • Question

  • Imports System.Data
    Imports System.Console
    Imports System.Data.SqlClient
    Imports System.Data.OleDb

    Public Class Form1
        Inherits System.Windows.Forms.Form

        Dim sqlcon As New SqlConnection
        Dim condb1 As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim dc As SqlCommand
        Dim daa As New SqlDataAdapter
        Dim bs As New BindingSource
        Public Const ConnectionString As String = "Data Source=SQLORACLE;Initial Catalog=estimate;Persist Security Info=True;User ID=classic; Password=stripes;"

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            sqlcon.ConnectionString = "Data Source=SQLORACLE;Initial Catalog=estimate;Persist Security Info=True;User ID=classic; Password=stripes;"
            Try
                sqlcon.Open()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Connection to 192.168.1.251 Failed", MessageBoxButtons.OK)

            End Try
            If sqlcon.State = 1 Then
                Me.Text = "Project is now connected to SQL"
            End If

            Call populate_combo(cboest, "ESTNO")
            cboest.Sorted = True
            cmdsave.Enabled = False
            cboest.Enabled = False
            cbover.Enabled = False
            DataGrid1.Visible = False
            Dim commSql As New SqlClient.SqlCommand
            commSql.Connection = sqlcon
            commSql.CommandText = "select count(*) from estp"
            MessageBox.Show(commSql.ExecuteScalar().ToString, " Total records ")
            bs.DataSource = ds.Tables("estp")
            DataGrid1.DataSource = bs
        End Sub

        Sub populate_Adapter_Dataset()
            Dim daa As SqlDataAdapter
            daa = New SqlClient.SqlDataAdapter("Select * from estp where ESTNO = '" & cboest.Text & "' AND ESTVERSION = '" & cbover.Text & "' ", sqlcon)
            daa.Fill(ds, "estp")
            DataGrid1.DataSource = ds.Tables("estp")
        End Sub

        Public Sub populate_combo(ByRef cmb1 As ComboBox, ByVal cbest As String)
            dc = New SqlCommand("Select distinct " & cbest & " from estp", sqlcon)
            Dim da As SqlClient.SqlDataReader = dc.ExecuteReader
            cmb1.Items.Clear()
            While da.Read
                cmb1.Items.Add(da("" & cbest & ""))
            End While
            da.Close()
        End Sub

        Public Sub populate_combo1(ByRef cmb2 As ComboBox, ByVal cbver As String)
            dc = New SqlCommand("Select distinct estversion from estp where estno = '" & cboest.Text & "' ", sqlcon)
            Dim da As SqlClient.SqlDataReader = dc.ExecuteReader
            cmb2.Items.Clear()
            While da.Read
                cmb2.Items.Add(da("" & cbver & ""))
            End While
            da.Close()
        End Sub

        Private Sub cmdselect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdselect.Click
            cboest.Enabled = True
            cmdselect.Enabled = False
            cmdsave.Enabled = False
        End Sub

        Private Sub cmdexit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdexit.Click
            End
        End Sub

        Private Sub cboest_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboest.SelectedIndexChanged
            cbover.Enabled = True
            Call populate_combo1(cbover, "estversion")
        End Sub

        Private Sub cbover_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbover.SelectedIndexChanged
            DataGrid1.Visible = True
            Call populate_Adapter_Dataset()
            DataGrid1.ColumnHeadersVisible = True
            cmdsave.Enabled = True
            cboest.Enabled = False
            cbover.Enabled = False
            cmdselect.Enabled = True
            DataGrid1.ReadOnly = False
        End Sub
        Public Sub SaveChanges()

            Dim dt As DataTable
            dt = ds.Tables("estp")
            Me.DataGrid1.BindingContext(dt).EndCurrentEdit()
            Me.daa.Update(dt)
            MessageBox.Show("Changes updated!", "Confirmation", MessageBoxButtons.OK)
        End Sub

        Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click

            Call SaveChanges()
        End Sub
    End Class
    Tuesday, December 28, 2010 4:36 AM

Answers

  • I can see there is no UpdateCommand attached to DataAdapter, due to this updation process will fail.You should provide valid update/delete/add commands to DataAdapter before calling Update( ) method.

    I'm adding few notes from MSDN, please continue reading ...

    The update is performed on a by-row basis. For every inserted, modified, and deleted row, the Update method determines the type of change that has been performed on it (Insert, Update or Delete). Depending on the type of change, the Insert, Update, or Delete command template executes to propagate the modified row to the data source. When an application calls the Update method, the DataAdapter examines the RowState property, and executes the required INSERT, UPDATE, or DELETE statements iteratively for each row, based on the order of the indexes configured in the DataSet. For example, Update might execute a DELETE statement, followed by an INSERT statement, and then another DELETE statement, due to the ordering of the rows in the DataTable.

    It should be noted that these statements are not performed as a batch process; each row is updated individually. An application can call the GetChanges method in situations where you must control the sequence of statement types (for example, INSERT before UPDATE). For more information, see Updating Data Sources with DataAdapters (ADO.NET).

    If INSERT, UPDATE, or DELETE statements have not been specified, the Update method generates an exception. However, you can create a SqlCommandBuilder or OleDbCommandBuilder object to automatically generate SQL statements for single-table updates if you set the SelectCommand property of a .NET Framework data provider. Then, any additional SQL statements that you do not set are generated by the CommandBuilder. This generation logic requires key column information to be present in the DataSet. For more information see Generating Commands with CommandBuilders (ADO.NET).

    The Update method retrieves rows from the table listed in the first mapping before performing an update. The Update then refreshes the row using the value of the UpdatedRowSource property. Any additional rows returned are ignored.

    • Proposed as answer by Jackie-Sun Wednesday, December 29, 2010 3:31 AM
    • Marked as answer by Jackie-Sun Monday, January 3, 2011 1:40 AM
    Tuesday, December 28, 2010 4:50 AM

All replies

  • The error is

    {"update requires a valid updatecommand when passed datarow collection with modified rows"}

    where in I am updating the MS SQL database by the code. Please the verify the code, if any mistakes.

    I can access the data from the database, edit the data; but the changes are not saved back to the database.

    What could be the problem?

    Please help!

     

    Thank you!

    Tuesday, December 28, 2010 4:40 AM
  • I can see there is no UpdateCommand attached to DataAdapter, due to this updation process will fail.You should provide valid update/delete/add commands to DataAdapter before calling Update( ) method.

    I'm adding few notes from MSDN, please continue reading ...

    The update is performed on a by-row basis. For every inserted, modified, and deleted row, the Update method determines the type of change that has been performed on it (Insert, Update or Delete). Depending on the type of change, the Insert, Update, or Delete command template executes to propagate the modified row to the data source. When an application calls the Update method, the DataAdapter examines the RowState property, and executes the required INSERT, UPDATE, or DELETE statements iteratively for each row, based on the order of the indexes configured in the DataSet. For example, Update might execute a DELETE statement, followed by an INSERT statement, and then another DELETE statement, due to the ordering of the rows in the DataTable.

    It should be noted that these statements are not performed as a batch process; each row is updated individually. An application can call the GetChanges method in situations where you must control the sequence of statement types (for example, INSERT before UPDATE). For more information, see Updating Data Sources with DataAdapters (ADO.NET).

    If INSERT, UPDATE, or DELETE statements have not been specified, the Update method generates an exception. However, you can create a SqlCommandBuilder or OleDbCommandBuilder object to automatically generate SQL statements for single-table updates if you set the SelectCommand property of a .NET Framework data provider. Then, any additional SQL statements that you do not set are generated by the CommandBuilder. This generation logic requires key column information to be present in the DataSet. For more information see Generating Commands with CommandBuilders (ADO.NET).

    The Update method retrieves rows from the table listed in the first mapping before performing an update. The Update then refreshes the row using the value of the UpdatedRowSource property. Any additional rows returned are ignored.

    • Proposed as answer by Jackie-Sun Wednesday, December 29, 2010 3:31 AM
    • Marked as answer by Jackie-Sun Monday, January 3, 2011 1:40 AM
    Tuesday, December 28, 2010 4:50 AM