You need the OleDBCommandBuilder that will automatically the insert, delete
and update commands.
Here is an example of updating using the commandbuilder from VS 2005 Help
Public Shared Function UpdateRows(connectionString As String, _
queryString As String, tableName As String) As DataSet
Dim dataSet As DataSet = New DataSet
Using connection As New OleDbConnection(connectionString)
Dim adapter As New OleDbDataAdapter()
adapter.SelectCommand = New OleDbCommand(queryString, connection)
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)
connection.Open()
adapter.Fill(dataSet, tableName)
' Code to modify data in DataSet here
' Without the OleDbCommandBuilder this line would fail.
adapter.Update(dataSet, tableName)
End Using
Return dataSet
End Function
"Mrk Blackall" <Mrk
Bl******@discussions.microsoft.comwrote in message
news:37**********************************@microsof t.com...
Hi all,
This is my first attempt to update an access database in VB.Net 2005. It
goes broadly as follows (I have condensed it):
Dim MyConnection As New Data.OleDb.OleDbConnection
MyConnection.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\books.mdb")
Dim Myadaptor As OleDb.OleDbDataAdapter
Myadaptor = New OleDb.OleDbDataAdapter("select * from stockitems",
MyConnection)
Dim MyDataSet As New DataSet
MyConnection.Open()
Myadaptor.Fill(MyDataSet)
Dim newRow As DataRow = MyDataSet.Tables(0).NewRow
newRow("code") = "New Code"
MyDataSet.Tables(0).Rows.Add(newRow)
Myadaptor.Update(MyDataSet)
MyConnection.Close()
I have tried a few ways to do this but I always get an error:
"Update requires a valid InsertCommand when passed DataRow collection with
new rows."
Any ideas? Any help gratefully received.
Mark