Result as an array

[BryanVest]

I have been retrieving values from MSACCESS databases using the guide on this site. http://vista.tutorialref.com/autoit/autoit-access-database.html

I have modified it a bit to work with accdb's, but it still works. Currently though it only returns one result. Anyone know how to make it show the 3-4 that it should return by the query in an array?

Code:

$dbname = "p:\Pallet Tag\Pallet Tracking_be.accdb"
$query = "SELECT PalletCreate.PalletID FROM PalletCreate LEFT JOIN PalletTruck ON PalletCreate.[PalletID] = PalletTruck.[PalletID] WHERE (((PalletTruck.PalletID) Is Null))"
Local $title
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname)
$adoRs = ObjCreate ("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open ($query, $adoCon)
$title = $adoRs.Fields("PalletID").value
MsgBox(0,"testing",$title)
EndSwitch

[spudw2k]

Check out the _GetRecords function in the example

Might give you some ideas on how to accomplish the desired result.

[Spiff59]

You've created a recordset, now you need need to advance the cursor through until EOF.

Try something like this after the $adoRS.Open statement:

$cnt = 0
While Not $adoRS.EOF
    $cnt += 1
    Msgbox(0,"Record " & $cnt, $adoRs.Fields("PalletID").value)
    $adoRS.MoveNext
WEnd
$adoRS.Close

Edited July 2, 2012 by Spiff59

[BryanVest]

You've created a recordset, now you need need to advance the cursor through until EOF.

Try something like this after the $adoRS.Open statement:

$cnt = 0
While Not $adoRS.EOF
$cnt += 1
Msgbox(0,"Record " & $cnt, $adoRs.Fields("PalletID").value)
$adoRS.MoveNext
WEnd
$adoRS.Close

That worked and shows each value in the Msgbox now how would I get that into an array?

EDIT: Nevermind I got it Thanks for the help.

Final code:

$dbname = "p:Pallet TagPallet Tracking_be.accdb"
$query = "SELECT PalletCreate.PalletID FROM PalletCreate LEFT JOIN PalletTruck ON PalletCreate.[PalletID] = PalletTruck.[PalletID] WHERE (((PalletTruck.PalletID) Is Null))"
Local $title
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname)
$adoRs = ObjCreate ("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open ($query, $adoCon)
$cnt = 0
Local $list[1]
While Not $adoRS.EOF
$cnt += 1
_ArrayAdd($list, $adoRs.Fields("PalletID").value)
$adoRS.MoveNext
WEnd
$adoCon.
GuiCtrlSetData($List1, _ArrayToString($list))

Edited July 2, 2012 by BryanVest

[spudw2k]

Ahem...