UPDating database from DAtaset which is populated by XML

[jcoder]

Hi

I'm sure this is probably a very easy thing to do, but here it goes. I have an xml file that i'm able to load into a dataset and then load into a dbgrid. What i would like to do then (once the dataset is loaded from an XML file) is to update the database.

Google tells me that i have to use a data adapter, but i noticed that everytime you use a data adapter you have to run a SQL query, which in my case would defeat the purpose since i created the xml file to make changes and then intend to upload to the database server. So the process would go something like this:

fill dataset from database at office -->save to xml file--->modify xml file at home-->load modified xml file into dataset-->update database with changes.

Can anyone help?

 

[jmcilhinney]

No, you don't have to execute a query. A DataAdapter can retrieve and/or save data. If all you want to do is insert data then all you need is the InsertCommand populated and then call Update.

 

[jcoder]

No, you don't have to execute a query. A DataAdapter can retrieve and/or save data. If all you want to do is insert data then all you need is the InsertCommand populated and then call Update.

Hi
the problem is that my dataset is not filled by the DataAdapter it is filled by the XML file that has the modified data. Is there a way around this?

 

[jmcilhinney]

Where the data in the DataSet came from is COMPLETELY irrelevant. It's usually the case that a DataTable is populated from a database and changes saved back to a database using the same DataAdapter, but there is absolutely no requirement that that be the case. You have a DataAdapter, you call Update and it saves the changes in the DataTable you pass back to the database in accordance with the connection details and SQL code you specify.

That said, if you want to save changes to the database then there have to be changes in the DataTable to save. That means that the RowState of your DataRows cannot be Unchanged, which they will most likely be by default. If you want to insert data then you can call SetAdded on the DataRow and your DataAdapter will need to have its InsertCommand populated. If you want to edit data then you can call SetModified on the DataRow and your DataAdapter will need to have its UpdateCommand populated.

 

[jcoder]

Thanks for your help so far

I have the following code:

Try
constr= "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=mydb;User ID=sa;Password=pass"
cn = New OleDbConnection(constr)
cn.Open()
Dim ds1 As New DataSet()
Dim SqlTransaction As OleDbTransaction
ds1.ReadXml("myfile.xml")


' Begin the transaction
SqlTransaction = cn.BeginTransaction

'create the data adapters
Dim Table1Adapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM mytablename", cn)


' create the command builders for each data adapter
Dim cmdbuilder As New OleDbCommandBuilder(Table1Adapter)


' we must specify the transaction used by these adapter.
Table1Adapter.SelectCommand.Transaction = SqlTransaction
'update db
Table1Adapter.Update(ds1, "mytablename")


SqlTransaction.Commit()




Catch ex As Exception

MsgBox(ex.Message)
End Try

End If

When i run this code, i get the following error:

unable to find mapping['mytablename'] or table "'mytablename'"

Any ideas? I'm desparate.
Thanks

 

[jmcilhinney]

Do you have a table named "mytablename"? Presumably not. Presumably you are supposed to read that and think "I'm supposed to put my table name there".

 

[jcoder]

Do you have a table named "mytablename"? Presumably not. Presumably you are supposed to read that and think "I'm supposed to put my table name there".

Yes, the database contains a table named "mytablename". I've double checked that. Also, after the line

Dim Table1Adapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM mytablename", cn)

I checked the tablemappings.count property to see if anything is return and it is zero. So i assume there's something missing or wrong with the code, which i also double and triple checked.

I'm not sure where exactly i'm going wrong with this?

 

[jmcilhinney]

Does your DataSet contain a DataTable named "mytablename"? That's the missing mapping that error message is talking about. The SQL code specifies the name of the database table. When you call Update on a DataAdapter, it's the name of the DataTable to get the data from that you specify.