pdate database with data in dataadapter.

[bhavin12300]

i am trying to update database with updated data in data adapter.
error message.

"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

here is my code.i get this error when i try to update dataadapter with following line.

 da.Update(ds2)

here is code to load dataadapter as well as datagrideview.

Dim da As OleDb.OleDbDataAdapter
    Dim ds2 As New DataTable
ds2.Clear()
        da = New OleDb.OleDbDataAdapter("select *  from tabel_user", myConnection)
        da.Fill(ds2)
        DataGridView1.DataSource = ds2

 

[jmcilhinney]

You have provided SQL code to retrieve data from the database but you haven't provided any to save data to the database.

Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                      connection)
Private table As New DataTable

Private Sub InitialiseDataAdapter()
    Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
    Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
    Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)

    delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")

    update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

    Me.adapter.DeleteCommand = delete
    Me.adapter.InsertCommand = insert
    Me.adapter.UpdateCommand = update

    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub

Private Sub GetData()
    'Retrieve the data.
    Me.adapter.Fill(Me.table)

    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.
End Sub

Private Sub SaveData()
    'Save the changes.
    Me.adapter.Update(Me.table)
End Sub

Note that if your query involves only one table and it has a primary key then you can take the easy option and use a CommandBuilder instead of creating the non-query commands yourself:

Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                      connection)
Private builder As New SqlCommandBuilder(adapter)
Private table As New DataTable

Private Sub InitialiseDataAdapter()
    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub

Private Sub GetData()
    'Retrieve the data.
    Me.adapter.Fill(Me.table)

    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.
End Sub

Private Sub SaveData()
    'Save the changes.
    Me.adapter.Update(Me.table)
End Sub

That code is for SQL Server but you can just substitute OleDb objects for all the SqlClient ones.

 

[bhavin12300]

hi,
it work thanks you so much for this.i am able to do what i suppose to do.
thanks once again.
topic solved.