Can't update an Access database

[=?Utf-8?B?TXJrIEJsYWNrYWxs?=]

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.OleD bConnection
MyConnection.Co nnectionString = ("Provider=Micr osoft.Jet.OLEDB .4.0;
Data Source=c:\books .mdb")
Dim Myadaptor As OleDb.OleDbData Adapter
Myadaptor = New OleDb.OleDbData Adapter("select * from stockitems",
MyConnection)
Dim MyDataSet As New DataSet
MyConnection.Op en()
Myadaptor.Fill( MyDataSet)
Dim newRow As DataRow = MyDataSet.Table s(0).NewRow
newRow("code") = "New Code"
MyDataSet.Table s(0).Rows.Add(n ewRow)
Myadaptor.Updat e(MyDataSet)
MyConnection.Cl ose()

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

[Paul Evans]

"Mrk Blackall" <Mrk Bl******@discus sions.microsoft .comwrote in message
news:37******** *************** ***********@mic rosoft.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.OleD bConnection
MyConnection.Co nnectionString = ("Provider=Micr osoft.Jet.OLEDB .4.0;
Data Source=c:\books .mdb")
Dim Myadaptor As OleDb.OleDbData Adapter
Myadaptor = New OleDb.OleDbData Adapter("select * from stockitems",
MyConnection)
Dim MyDataSet As New DataSet
MyConnection.Op en()
Myadaptor.Fill( MyDataSet)
Dim newRow As DataRow = MyDataSet.Table s(0).NewRow
newRow("code") = "New Code"
MyDataSet.Table s(0).Rows.Add(n ewRow)
Myadaptor.Updat e(MyDataSet)
MyConnection.Cl ose()

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

Couldn't you use a SqlCommand (System.Data.Sq lCommand?) 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 OleDbDataAdapte r
constructor that would specify whether you're opening the databse for
reading only(adForwardO nly) 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

[aaron.kempf]

ADO.net is unnecessarily complex

use a command, dipshit


On Feb 16, 4:11 pm, Mrk Blackall <Mrk
Black...@discus sions.microsoft .comwrote:

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.OleD bConnection
MyConnection.Co nnectionString = ("Provider=Micr osoft.Jet.OLEDB .4.0;
Data Source=c:\books .mdb")
Dim Myadaptor As OleDb.OleDbData Adapter
Myadaptor = New OleDb.OleDbData Adapter("select * from stockitems",
MyConnection)
Dim MyDataSet As New DataSet
MyConnection.Op en()
Myadaptor.Fill( MyDataSet)
Dim newRow As DataRow = MyDataSet.Table s(0).NewRow
newRow("code") = "New Code"
MyDataSet.Table s(0).Rows.Add(n ewRow)
Myadaptor.Updat e(MyDataSet)
MyConnection.Cl ose()

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

[William LaMartin]

You need the OleDBCommandBui lder 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(conn ectionString As String, _
queryString As String, tableName As String) As DataSet

Dim dataSet As DataSet = New DataSet

Using connection As New OleDbConnection (connectionStri ng)
Dim adapter As New OleDbDataAdapte r()
adapter.SelectC ommand = New OleDbCommand(qu eryString, connection)
Dim cb As OleDbCommandBui lder = New OleDbCommandBui lder(adapter)

connection.Open ()

adapter.Fill(da taSet, tableName)

' Code to modify data in DataSet here

' Without the OleDbCommandBui lder this line would fail.
adapter.Update( dataSet, tableName)
End Using

Return dataSet
End Function
"Mrk Blackall" <Mrk Bl******@discus sions.microsoft .comwrote in message
news:37******** *************** ***********@mic rosoft.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.OleD bConnection
MyConnection.Co nnectionString = ("Provider=Micr osoft.Jet.OLEDB .4.0;
Data Source=c:\books .mdb")
Dim Myadaptor As OleDb.OleDbData Adapter
Myadaptor = New OleDb.OleDbData Adapter("select * from stockitems",
MyConnection)
Dim MyDataSet As New DataSet
MyConnection.Op en()
Myadaptor.Fill( MyDataSet)
Dim newRow As DataRow = MyDataSet.Table s(0).NewRow
newRow("code") = "New Code"
MyDataSet.Table s(0).Rows.Add(n ewRow)
Myadaptor.Updat e(MyDataSet)
MyConnection.Cl ose()

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