Populating GuiCtrlSetData with MSSQL output to array

[skysel]

Just done the googling and forum search, but couldn't find solution to my need. I'm having difficulties understanding arrays (yes I've read help file).. So I'm asking for help.

Below is the code, I want to populate GUICtrlSetData with sql query output, i've defined $array2 to convert array to string with | as delimiter. In that table I have 3 fields as it can be seen, so when the createlist gui appears, there would have to be structure like:

Surname Name - Code

Surname1 Name1 - Code1

etc...

When I launch the script, GUI appears and disappears instantly...

GuiCtrlSetData is normaly populated like this... so this would be desired output.

GUICtrlSetData(-1,"value 1|value 2|value 3")

#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <Guilistview.au3>

$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.ConnectionString = "driver={SQL Server};SERVER=1.1.1.1;database=DbTEST;uid=user;pwd=password"
$sqlCon.Open

$status = $sqlCon.State

; request/insert data
$strSQL = "SELECT [Code], [Surname], [Name] FROM [DbTEST].[dbo].[Employees] ORDER BY [Surname]"
$result = $sqlCon.execute($strSQL)
$array = $result.GetRows
;_ArrayDisplay($Array) - this is for test / array displays fine, no connection issues

$array2 = _ArrayToString($Array,"|",1)

$hGUI = GUICreate("Users", 300, 300)
$cInput = GUICtrlCreateList("", 10, 10, 280, 250)
GUICtrlSetData(-1, $array2)
$cButton = GUICtrlCreateButton("Confirm", 10, 260, 50)
GUISetState()

Edited March 5, 2013 by skysel

[skysel]

Ok, so I found this code somewhere here

Since I have a 2D array, this function deals with it. If I write array output to file, the data is there - but every space is seperated with | sign. I need every row to be seperated with | sign. And will this array population work on GUICtrlSetData?

Obviously I made a little progress :-)

Func _ArrayToString2D(Const ByRef $avArray, $sDelimCol = "|", $sDelimRow = @CRLF, $iStart = 0, $iEnd = 0)

 If Not IsArray($avArray) Then Return SetError(1, 0, "")
 If UBound($avArray, 0) <> 2 Then Return SetError(2, 0, "")

 Local $sResult, $iUBound = UBound($avArray) - 1

 ; Bounds checking
 If $iEnd < 1 Or $iEnd > $iUBound Then $iEnd = $iUBound
 If $iStart < 0 Then $iStart = 0
 If $iStart > $iEnd Then Return SetError(3, 0, "")

 ; Combine
 For $i = $iStart To $iEnd ; rows
    For $j = 0 To UBound($avArray,2) - 1 ; columns
        $sResult &= $avArray[$i][$j] & $sDelimCol
    Next
    $sResult = StringTrimRight($sResult, StringLen($sDelimCol))
    $sResult &= $sDelimRow
 Next

 Return StringTrimRight($sResult, StringLen($sDelimRow))
EndFunc

[skysel]

Uh, so after much research and some luck I finally managed to achieve what I wanted, list in guictrlsetdata is displayed correctly, so thanks to me for solving my own problem :-) :

expandcollapse popup

#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <Guilistview.au3>

$sqlCon = ("ADODB.Connection") $sqlCon.ConnectionString = "driver={SQL Server};SERVER=1.1.1.1;database=DbTEST;uid=user;pwd=password"
$sqlCon.Open $status = $sqlCon.State ; request/insert data
$strSQL = "SELECT [Code], [Surname], [Name] FROM [DbTEST].[dbo].[Employees] ORDER BY [Surname]"
$result = $sqlCon.execute($strSQL)
$array = $result.GetRows ;_ArrayDisplay($Array) - this is for test / array displays fine, no connection issues
$output = _ArrayToString2D($array," ","|",0)

$hGUI = GUICreate("Employees", 250, 200)
$cInput = GUICtrlCreateList("", 10, 10, 230, 110)
GUICtrlSetData(-1, $output)
$cButton = GUICtrlCreateButton("To Clip", 10, 120, 50)
GUISetState()

While 1
Switch GUIGetMsg()
Case $GUI_EVENT_CLOSE
ExitLoop
Case $cButton
$sMyText = GUICtrlRead($cInput)
If $sMyText <> "" Then
ClipPut(StringRegExpReplace($sMyText, "[^[:digit:]]", ""))
EndIf
ExitLoop
EndSwitch
WEnd

GUIDelete($hGUI)

Func _ArrayToString2D(Const ByRef $avArray, $sDelimCol = "", $sDelimRow = "", $iStart = 0, $iEnd = 0)
If Not IsArray($avArray) Then Return SetError(1, 0, "")
If UBound($avArray, 0) <> 2 Then Return SetError(2, 0, "")

Local $sResult, $iUBound = UBound($avArray) - 1

; Bounds checking
If $iEnd < 1 Or $iEnd > $iUBound Then $iEnd = $iUBound
If $iStart < 0 Then $iStart = 0
If $iStart > $iEnd Then Return SetError(3, 0, "")

; Combine
For $i = $iStart To $iEnd ; rows
For $j = 0 To UBound($avArray,2) - 1 ; columns
     $sResult &= $avArray[$i][$j] & $sDelimCol
Next
$sResult = StringTrimRight($sResult, StringLen($sDelimCol))
$sResult &= $sDelimRow
Next

Return StringTrimRight($sResult, StringLen($sDelimRow))
EndFunc

Edited March 5, 2013 by skysel

[jgq85]

Hi I think I'm getting a variable error when I try to replicate your final script, and I've updated my SQL connectivity issue. 

Also are your customizations here only for the table your querying? Or will it work on a table I try to query?