Sign in to follow this  
Followers 0
marko001

ADO Update from Listview

8 posts in this topic

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

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Here is a subset of $datafile

<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 by marko001

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by marko001

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0