Sign in to follow this  
Followers 0
arcticpup

SQL Select utility

4 posts in this topic

#1 ·  Posted (edited)

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

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

Share this post


Link to post
Share on other sites



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?

Share this post


Link to post
Share on other sites

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()

Share this post


Link to post
Share on other sites

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

Iain

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