 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / May 2005Update table fields from XML file
I all, I would like this process to execute a update procedure with ADO and XML file. 1- Select two fields (primary key and a other) of one table from my database with ADO.Recordset 2- Save my recordset to XML File (adodb.recordset.save) 3- The fields values will be modified into XML file (except primary key) 4- Open a recordset from XML file modified 5- Execute a updatebatch method to affect change to database. Is it possible to make this procedure with ADO ??? Thanks Sly >I all, > [quoted text clipped - 10 lines] > > Is it possible to make this procedure with ADO ??? Absolutely, but with a few caveats: 1. The recordset you originally open and persist to XML must be writable (or you'll have to fix-up the attributes in the XML to make it that way, and the fixup cannot contradict database integrity.) Remember that not all recordsets are created equally, and these differences are often reflected in attributes, in XML. 2. In between the time it's persisted and the time UpdateBatch is called, any records that are changed in the database and the persisted copy will cause problems. (Note that it will only check the fields selected into the persisted copy, so if for instance the table has f1, f2 and f3, but only f1 and f3 are selected into the persisted recordset, it won't care if f2 is changed in the db, because it will have no way to tell.) That's basically it, in a nutshell. -Mark > Thanks > > Sly I Mark, How to open my recordset that load a xml file to link my database? I imagine that the link must be establish between my XML file loaded on my recordset and the database that must be updated. I try this: myRecordset.Open "C:\Temp\MyXML.xml", myConnectionADO -> error is occur myRecordset.Open "C:\Temp\MyXML.xml" myRecordset.UpdateBatch -> my modifications was not applied to my database. Which is the solution?? Thanks >I Mark, > [quoted text clipped - 13 lines] > > Thanks Close :-) myRecordset.Open "C:\Temp\MyXML.xml" Set myRecordset.ActiveConnection = myConnectionADO -Mark Thanks Mark But does not work correctly. My value of the field from XML file was not modified in my database. My code : recClientImport.Open "C:\Temp\444.xml", , adOpenStatic, adLockBatchOptimistic Set recClientImport.ActiveConnection = mobjConnection recClientImport.UpdateBatch Sly > Thanks Mark > [quoted text clipped - 6 lines] > Set recClientImport.ActiveConnection = mobjConnection > recClientImport.UpdateBatch The cursor and lock type specifiers at this point are of no consequence, they are important to the original recordset from which the XML was generated, You should look at the XML to make sure the recordset is updatable. The " <s:ElementType name='row' " node must contain the " rs:updatable='true' " attribute, and each " <s:AttributeType " node for a field to be updated must include the " rs:writeunknown='true' " attribute. It also helps if the latter includes the "BaseCatalog" and "BaseTable" attributes, those are a very strong assurance that it knows what it's supposed to be updating. For this persistant schema to be generated, each row must be uniquely identified, typically by an identity column, and the SQL statement that was used to retrieve the data must have been inherently updatable (i.e., not a UNION, not an aggregate, not derived fields, etc.) Before calling UpdateBatch, save the recordset to another stream or temp file to examine it. For each row that was updated it should wrap the <z:row> node in an <rs:update> node. Example: data that has not been updated since it was read from the db looks like this: <rs:data> <z:row ClassID='15' InstID='1' Event='Class_Initialize'/> <z:row ClassID='19' InstID='2' Event='Class_Initialize'/> <z:row ClassID='25' InstID='3' Event='Class_Initialize'/> </rs:data> If the Event field in the second row in the sample above, was altered by adding 3 'x' characters to the existing value, it looks like this: <rs:data> <z:row ClassID='15' InstID='1' Event='Class_Initialize'/> <rs:update> <rs:original> <z:row ClassID='19' InstID='2' Event='Class_Initialize'/> </rs:original> <z:row Event='Class_Initializexxx'/> </rs:update> <z:row ClassID='25' InstID='3' Event='Class_Initialize'/> </rs:data> If updates to the recordset are not generating this construction, it's because the persistance provider does not believe the data can be batch-updated. It is possible to fixup the XML on the fly, but yoy really have to be on your game to get away with that, as when it's all said and done, it's got to be legal to the server, or it's worthless. -Mark > Sly |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.