SQL Select utility

[arcticpup]

Hi,

I'm trying to pull some info out of SQL, but am only getting half the story.

Here's what I've got so far

expandcollapse popup

#include <GUIConstants.au3>
#include <GuiConstantsEx.au3>
#include <GuiListView.au3>
#include <_sql.au3>
#include <array.au3>
 
 
 
 
 
 
;This does the SQL login stuff...
 
Opt ("trayIconDebug",1)
     _SQL_RegisterErrorHandler()
    $oADODB = _SQL_Startup();
    If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _sql_Connect(-1,"SAGE010567\SAGESPS","2010","User1","$ageAdm1n") = $SQL_ERROR then
        Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
        _SQL_Close()
        Exit
    EndIf
 
 
;This puts results into array...
dim $aItems,$iRows,$icolumns
$iRval = _SQL_GetTable2d(-1,"SELECT * FROM  [2010].[dbo].[Lookup2010] WHERE FORM='SA100' AND PAGE='1' AND BOX='3';",$aItems,$iRows,$icolumns)
 
 
 
;This gets the results into a gui...
 
dim $hListView
    ; Create GUI
    GUICreate("Changer", 400, 200)
    $hListView = GUICtrlCreateListView("F1" , 3, 3, 394, 268)
 
    GUISetState()
    ; Add columns
    _GUICtrlListView_AddColumn($hListView, "F2", 500)
    _GUICtrlListView_AddArray($hListView, $aItems)
    ; Loop until user exits
    Do
    Until GUIGetMsg() = $GUI_EVENT_CLOSE
    GUIDelete()

That returns:

F1, F2

Tax return and computations, Contact and Signatory details

Now, if I were to run my select statement in Management Studio

SELECT * FROM  [2010].[dbo].[Lookup2010]
WHERE
FORM='SA100'
AND
PAGE='1'
AND
BOX='3'

which returns

Headers:F1, F2, F3, F4, F5

Values: Tax return and computations, Contact and Signatory details, Contact & Signatory Details, Select daytime contact , Client

Now, I'm not fussed about the headers being displayed (F1, F2, F3) as they are of no interest to the end user.

However, I can't understand why my code is only returning the first column from SQL and not the other 4?

Any help please! I'm sure it's something basic I'm missing!

Many thanks

Iain

Edited October 17, 2011 by arcticpup

[Spiff59]

Have you done an _ArrayDisplay() on $aitems?

I'm guessing that all the data is there, and although I've never used the function before, I'm again guessing that _GUICtrlListView_AddArray() only populates however many columns you've already created in your listview?

[Zedna]

Try this

#include <GUIConstants.au3>
#include <GuiConstantsEx.au3>
#include <GuiListView.au3>
#include <_sql.au3>
#include <array.au3>
 
     _SQL_RegisterErrorHandler()
    $oADODB = _SQL_Startup();
    If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _sql_Connect(-1,"SAGE010567\SAGESPS","2010","User1","$ageAdm1n") = $SQL_ERROR then
        Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
        _SQL_Close()
        Exit
    EndIf
 
dim $aItems,$iRows,$icolumns
$iRval = _SQL_GetTable2d(-1,"SELECT * FROM  [2010].[dbo].[Lookup2010] WHERE FORM='SA100' AND PAGE='1' AND BOX='3';",$aItems,$iRows,$icolumns)
 
dim $hListView
    GUICreate("Changer", 400, 200)
    $hListView = GUICtrlCreateListView("F1|F2|F3|F4|F5" , 3, 3, 394, 268)
    GUISetState()
    _GUICtrlListView_AddArray($hListView, $aItems)
    ; Loop until user exits
    Do
    Until GUIGetMsg() = $GUI_EVENT_CLOSE
    GUIDelete()

[arcticpup]

Cheers guys, that's given me the head strart I needed!

Iain