Jump to content
Sign in to follow this  
skysel

Populating GuiCtrlSetData with MSSQL output to array

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 :-) :

#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 by skysel

Share this post


Link to post
Share on other sites
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? 

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  

×