Database Update...

[Xmas79]

Hello, I can't update my database. Why??? I found code snippets in thius forum, mine is the same. Here's the code:

Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" _
& "c:\mypath\db.mdb" ' Put here the connection string
Dim myConnection As New OleDbConnection(myConnString) 'Create a new connection
 
Dim MyDataSet As New DataSet()
Dim MyDataAdapter As New OleDbDataAdapter("SELECT * from MyTable", myConnection)
MyDataAdapter.Fill(MyDataSet, "MyTable")
MyDataSet.Tables(0).Rows(0).Item(2) = 123412 'modify 3nd column of first row
MyDataAdapter.Update(MyDataSet) 'Here's the error

The error is: "Update was not able to find TableMapping['Table'] or DataTable 'Table'".

Thnx
Xmas

[Pirate]

How many tables do you have in the database ?

[Pirate]

Try this

'instead of the one you wrote
MyDataAdapter.Update(MyDataSet,"MyTable")

[Xmas79]

Got another error:
"Update needs a valid UpdateCommand if DataRow collection is passed with modified rows"
What's that?

Thx

[Pirate]

Where's the error ?

[Xmas79]

In the same place:

MyDataAdapter.Update(MyDataSet,"MyTable")

I have 10 tables in my DB, but I tried with a new DB with only 1 table. Nothing to do

Pirate, any ideas?
Tx

[Pirate]

Ok , are you updating existing data or adding new data ?

[Xmas79]

I'm updating, not adding. I tried without modifying the content of the table, and everything works. But when I change something in the dataset I get this error. I didn't try adding a new row yet.

[Lunatic3]

Do you have a valid update command for your dataadapter?
It should look something like this:

Me.myDataAdapter.UpdateCommand = Me.OleDbUpdateCommand1
Me.OleDbUpdateCommand1.Connection = myConnection
Me.OleDbUpdateCommand1.CommandText = "UPDATE myTable SET myField= ? where (myField = ?)"
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("myField", System.Data.OleDb.OleDbType.VarWChar, 50, "myField"))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_myField", System.Data.OleDb.OleDbType.VarWChar, 50,  _ 
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "myField", System.Data.DataRowVersion.Original, Nothing))

Edits: Some Spelling Errors

[Pirate]

I sounds you are missing Update sql command . I've not tried your method to update existing data but I only use that when I add new data .

[Pirate]

Lunatic3 was a bit faster...

[Lunatic3]

In case you need to insert data to table you also need a valid Insert command.

[DevGrp]

You can let VS.NET build an update command for you. Just declare a commandbuilder object and pass it to your dataadapter.

[Xmas79]

Wow! Looking at Lunatic3's code it seems to me very complicated, but with commandbuilder, DevGrp's trick, it works great!!!
Thank you all, guys!

Xmas

[Lunatic3]

The command builder just automates that process for you and the idea is basically the same.

[Xmas79]

Sure, but i'm not so strong with SQL

[276560]

Hi,

I also can't complete the database update routine.
I wanted to bind a dataset to a datagrid. Then to modify data in the datagrid, and on "Save" click to update a table.

'To populate the grid:
Dim da As New OleDbDataAdapter("Select * From Table1", cnn)
Dim ds As New DataSet()
da.Fill(ds, "Table1")
da.Dispose()
cnn.Close()
dGrid.SetDataBinding(ds, "")

'On Save click:
Dim cnn As New OleDbConnection(ConnString)
cnn.Open()
Dim da As New OleDbDataAdapter("Select * From Table1", cnn)
Dim cb As New OleDbCommandBuilder(da)
da.Update(ds.Tables("Table1"))

This code allows me to delete rows from the table, and to edit a specific columns, but I can't add new records. I'm getting "Error in the INSERT Statement"

Any ideas why?

Thanks

[Xmas79]

Originally posted by Lunatic3
In case you need to insert data to table you also need a valid Insert command.

I think this is the reason. Try to add

cb.getinsertcommand() ' I think this is the method, now I can't check it...

Another question: I noticed that I can select only ONE table at time. In fact, if I select 2 or more tables, i.e. "SELECT * FROM table1,table2", it returns me only "</newdataset>". Any ideas ?

[Lunatic3]

it returns me only "</newdataset>".

What you mean by that?
You can select from multiple tables in one dataadapter, however they will be mapped to only one table in the dataset.

Edits:

cb.getinsertcommand() ' I think this is the method, now I can't check it...

By the way your method you mentioned just returns the insert statment(does not produce one, its produced already) so he can check what may be wrong with it. More detail of error may be helpful.

[Xmas79]

Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" _
& "c:\mypath\db.mdb" ' Put here the connection string
Dim myConnection As New OleDbConnection(myConnString) 'Create a new connection
 
Dim MyDataSet As New DataSet()
Dim MyDataAdapter As New OleDbDataAdapter("SELECT * from MyTable1,MyTable2", myConnection) 'Select from 2 tables
MyDataAdapter.Fill(MyDataSet)
MsgBox(MyDataSet.GetXml()) ' MsgBox's message is only "</newdataset>"

If I remove MyTable2 (or MyTable1) I get the real content of my database.

In my code I used this:

Mydataadapter.insert.textcommand=cb.getinsertcommand()

and it worked.

[Lunatic3]

Look, you can select from two table, but as far as you dont define any logical relationship (Inner join, outer join...) between them then you will have a table that will contain ALL of the fields of both tables and ... (give it a try ..)

So if you want to have two separate tables you have to define tow seprate dataaadapters, but still you can map that adapaters to one Dataset and your dataset will have two tables.

Mydataadapter.insert.textcommand=cb.getinsertcommand()
and it worked.

It would have worked without that too. All you have to do is defining a commandbuilder for a dataadapter. It does the job automatically.

[Xmas79]

Thanks, I'll do in that way, but I can't still understand why it returns me a blank dataset instead of a dataset with one big table with all the columns.

Xmas.

[Lunatic3]

Yes, its strange