ADO Update from Listview

[marko001]

Hi all great programmers,

I have a (probably) difficult matter I hope you can help me with:

I use ADO objects to populatea LV:

func _ADOCalls()
    With $adoRsItems
        .CursorLocation = 3; adUseClient
        .CursorType = 2; adOpenDynamic
        .LockType = 2; adLockPessimistic
        If FileExists($DataFile) Then
        .Open($DataFile) ; Load From XML File
endfunc

and

Func _PopulateListView()
Local $I, $J, $K, $Txt
   _GUICtrlListView_DeleteAllItems($hlistView)
   $adoRsItems.MoveFirst()
   $J = $adoRsItems.Fields.Count() - 1
   While NOT $adoRsItems.EOF()
     $K = $adoRsItems.Fields("First Name").Value
     For $I = 0 To $J
         $Txt = $adoRsItems.Fields($I).Value
         If $I = 0 Then
           $K = _GUICtrlListView_AddItem($hlistView, $Txt, $K)
         Else
           _GUICtrlListView_SetItemText($hlistView, $K, $Txt, $I)
         EndIf
         ReDim $sets[$k+1]
         $sets[$k] = $adoRsItems.fields(4).value
     Next; $I
     $adoRsItems.MoveNext()
    Wend
EndFunc ; PopulateListView

Now I have my LV filled with all datas.

Now I filter some of them:

Case $BT_CountrySearch
            $adoRsItems.Filter = "Country = 'USA'"
            _PopulateListView()

and I get the new LV

Now I change a value, directly in the LV, from (i.e.) USA to JAPAN.

Problem comes now: How can I save back datas in the .xml file?

I tried different ADO commands but I get errors so I can't go on.

What I need is to update the file so when I filter again I get the correct values.

Thanks,

Marco

[marko001]

I found the way to create the string to replace the existing one, but when I "_populateListview()" it doesn't get the new datas but keeps the old ones.

How to solve it?

M.

[PsaltyDS]

Your $Datafile is an .XML file? How did you initialized $adoRsItems before calling _ADOCalls()?

[marko001]

Here is a subset of $datafile

expandcollapse popup

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
        <s:AttributeType name='c0' rs:name='First Name' rs:number='1' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='256' rs:precision='0' rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='c1' rs:name='Stocks' rs:number='2' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='c2' rs:name='Weight' rs:number='3' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='Mean' rs:number='4' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='50' rs:precision='0' rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='Over' rs:number='5' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='25' rs:precision='0' rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='c5' rs:name='Alter N.' rs:number='6' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='65' rs:precision='0' rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='Fault' rs:number='7' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='50' rs:precision='0' rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='c7' rs:name='Over Price' rs:number='8' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='currency' dt:maxLength='8' rs:precision='0' rs:fixedlength='true'
             rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='c8' rs:name='Under Price' rs:number='9' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='currency' dt:maxLength='8' rs:precision='0' rs:fixedlength='true'
             rs:maybenull='false'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
    </s:ElementType>
</s:Schema>
<rs:data>
<z:row c0='John Holder' c1='9' c2='9' Mean='High' Over=' ' c5='n/a' Fault='No' c7 = '0' c8='0'/>
<z:row c0='Andrew Call' c1='9' c2='9' Mean='Low' Over=' ' c5='n/a' Fault='No' c7 = '0' c8='0'/>
<z:row c0='Phill Nurew' c1='9' c2='9' Mean='Low' Over=' ' c5='n/a' Fault='Yes' c7 = '0' c8='0'/>
<z:row c0='Stephan More' c1='9' c2='9' Mean='High' Over=' ' c5='n/a' Fault='No' c7 = '0' c8='0'/>
</rs:data>
</xml>

And:

$adoRsItems = ObjCreate( "ADODB.RecordSet" )
...
...
_ADOCalls()
_PopulateListView()
GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
GUIRegisterMsg($WM_COMMAND, "WM_COMMAND")
GUICtrlRegisterListViewSort($hListView, "LVSort")
; ### GUIRegisterMsgs
Global $B_DESCENDING[_GUICtrlListView_GetColumnCount($hListView)]

GUISetState(@SW_SHOW)

then:

func _ADOCalls()
    With $adoRsItems
        .CursorLocation = 3; adUseClient
        .CursorType = 2; adOpenDynamic
        .LockType = 2; adLockPessimistic
        If FileExists($DataFile) Then
            .Open($DataFile) ; Load From XML File
        Else ; Define Columns
        EndIf
   EndWith
EndFunc

and

Func _PopulateListView()
Local $I, $J, $K, $Txt
   _GUICtrlListView_DeleteAllItems($hlistView)
   If $adoRsItems.EOF() Then
      MsgBox(64, "Filter Failed", "Filter Excluded All Items." & @CRLF & "Filter=" & $adoRsItems.Filter & @CRLF & @CRLF & "Reverting to All Items Display.", 5)
      $adoRsItems.Filter = 0
      
   EndIf
   $adoRsItems.MoveFirst()
   $J = $adoRsItems.Fields.Count() - 1
   While NOT $adoRsItems.EOF()
     $K = $adoRsItems.Fields("First Name").Value
     For $I = 0 To $J
         $Txt = $adoRsItems.Fields($I).Value
         If $I = 0 Then
           $K = _GUICtrlListView_AddItem($hlistView, $Txt, $K)
         Else
           _GUICtrlListView_SetItemText($hlistView, $K, $Txt, $I)
         EndIf
         ReDim $sets[$k+1]
         $sets[$k] = $adoRsItems.fields(4).value
     Next; $I
     $adoRsItems.MoveNext()
    Wend
EndFunc ; PopulateListView

Listview is editable so I can change values, I use a button for fast changing:

func _setOverprice($type)
    $visibleItems = _GUICtrlListView_GetItemCount($hlistView)
    if $type = "Over" Then
        for $i = 0 to $visibleItems
            _GUICtrlListView_SetItemText($hlistView,$i,GUICtrlRead($mass_Over_price),7)
        Next
    EndIf
    if $type = "Under" Then
        for $i = 0 to $visibleItems
            _GUICtrlListView_SetItemText($hlistView,$i,GUICtrlRead($mass_Under_price),8)
        Next
    EndIf
EndFunc

If this function is called, a subset of data is changed (I 1st filter LV then apply the mass change).

Now I need to save back data to the xml.

I've done it with a complicate but working code (if there is a simple way...better) BUT when I recall Populatelistview() he takes the old data, not the modified ones.

M.

Edited June 27, 2011 by marko001

[marko001]

I added an errorhandler and If I call

case $update
            _GUICtrlListView_SaveXML($hListView, $output_file)
            _ADOCalls()
            _PopulateListView()

I get this error:

err.description is: Operation not allowed if object is open

err.source is: ADODB.Recordset

Edited June 27, 2011 by marko001

[PsaltyDS]

I think the ADO format for XML is very specific, and _GuiCtrlListView_SaveXML() probably doesn't provide that format the way it just uses flat string manipulation.

Instead, create an open XML DOM object, then use the .Save method of the recordset object where the target is the XML DOM object.

See linked example.

[marko001]

It's a modified function, I modified for my own use to a) create the same output as .save does and replace the old line with the new one with updated datas.

Beside that, if I use .save (tell me if I'm wrong) it just saves the records it sees in the table and add them at the end of the file, while I need them to be updated and deleted (the old ones).

M.

[PsaltyDS]

Have you looked at the _XMLDomWrapper.au3 UDF?