Saving Dataview edited records to underlying Access Database.

[Kieran777]

I am trying to update an Access database.
I am attempting to do the update via a Dataview
The record to be edited is found and the Dataview data
is edited OK. My problem occurs when I try to save this to the database.
I don't get an error but the dataview has the updated data and this data
is not saved to the underlying database as I require.

'A Dataset dsBirds already exists and is populated

If Action = "Edit" Then
'Find Record in Dataview
Dim SortBy As String
SortBy = "yr, ringno"

Dim pRow As DataRowView
Dim dv1 As DataView
dv1 = New DataView

Dim oBird1(1) As Object
oBird1(0) = CLng(txtYear.Text)
oBird1(1) = txtRingNo.Text

With dv1
.Table = dsBirds.Tables("Bird")
.AllowDelete = False
.AllowEdit = True
.AllowNew = False
.Sort = SortBy
End With

Dim i As Long
i = dv1.Find(oBird1)

dv1(i).Item("name") = "Tweety"
dv1(i).Item("sex") = "Hen"

' The problem is around here...
'send row through to dataadaptor to update table
'the sdaBirds is the original dataadaptor

me.sdaBirds.Update("dsBirds", "Bird")

dv1.Table.AcceptChanges()

End If
End Function

 

[jebenson]

does the DataAdapter (sdaBirds) have an UpdateCommand assigned, with parameters bound to the DataSet's table columns?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Kieran777]

The code to create Dataset is as follows

Dim strSQL As String = "SELECT * from Master"

' A OleCommand object is used to execute the SQL commands.
Dim scmdBirds As New OleDbCommand(strSQL, scnnNW)

' A OleDataAdapter uses the SqlCommand object to fill a DataSet.
Private sdaBirds As OleDbDataAdapter
sdaBirds = New OleDbDataAdapter(scmdBirds)
sdaBirds.MissingSchemaAction = MissingSchemaAction.AddWithKey

' A SqlCommandBuilder automatically generates the SQL commands needed
' to update the database later - is this correct?
Dim scbBirds As New OleDbCommandBuilder(sdaBirds)

' Create a new DataSet and fill its first DataTable.
dsBirds = New DataSet
Me.sdaBirds.Fill(dsBirds, "Bird")


Given the above code and original post, I'm wondering when the dataview datarow is changed is this change reflected in
the underlying Dataset? Do I need to do the dataview.acceptchanges before calling
the dataadaptor.update method or is it the other way around?

 

[jebenson]

MSDN has this to say about the CommandBuilder and how it works:

...you can create an OleDbCommandBuilder object to automatically generate SQL statements for single-table updates if you set the SelectCommand property of the OleDbDataAdapter. Then, any additional SQL statements that you do not set are generated by the OleDbCommandBuilder.

So, looking at your code it seems that you need to set the SelectCommand for the DataAdapter (sdaBirds) before you call the CommandBuilder. Try this:

sdaBirds.SelectCommand = New OleDbCommand(strSQL, scnnNW)

Hope this helps.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson