Can't update an Access database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Mrk Blackall said:
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
Click to expand...
Couldn't you use a SqlCommand (System.Data.SqlCommand?) and just execute an
INSERT INTO or UPDATE query?
Failing that, look into the properties of your DataSet and ensure you've
opened it for writing as well.
I'd suggest exact code, but I'm only just starting to get used to .NET, and
all the data projects I'm working on, I'm either pulling data from a
webservice, or a SQL2000 database.
I'd hazard a guess that there may be a 3rd option in the OleDbDataAdapter
constructor that would specify whether you're opening the databse for
reading only(adForwardOnly) or for writing back to it as well.
Also check the file permissions and make sure that you can actually write to
the database?

Hope some of this helps.

--Paul Evans CCNA
SHL Computing
 
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
 
Back
Top