_ArraySort is returning an error;

[Valiante]

First of all, here's my code;

#include <Array.au3>
Local $oRS
Local $oConn

$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\temp\mydatabase.mdb")

$oRS.Open("Select * FROM Table1", $oConn, 1, 3)

$rows = $oRS.RecordCount
$array = $oRS.GetRows($rows,0,0)
_ArraySort($array)
_ArrayDisplay($array)

$oConn.Close
$oConn = 0

But the following error is returned in the Output;

C:\Program Files\AutoIt3\Include\Array.au3 (666) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: 
$t = $array[$i] 
$t = ^ ERROR
>Exit code: 1

The only way I can get it to work is by commenting out the _ArraySort command, but the contents of that column aren't in order and I need them to be.

[SmOke_N]

How many dimensions is $array? If it's more than 1 (which it looks like) it won't work with _ArraySort(), that's why you are getting the error. Shows how much I know , the help file says it will sort a 2 dimension array, I was always under the assumption it was one.

Anyway, how many dimensions is the array?

Edited September 18, 2007 by SmOke_N

[Valiante]

I'm not sure. The array's created by ADODB.Connection.GetRows and I don't know how that works.

When I use _ArrayDisplay it just looks like a basic 1-dimentional array, but what do I know.

How can I tell how many dimensions it has? Will I need to use Ubound, or something like that?

[SmOke_N]

I'm not sure. The array's created by ADODB.Connection.GetRows and I don't know how that works.

When I use _ArrayDisplay it just looks like a basic 1-dimentional array, but what do I know.

How can I tell how many dimensions it has? Will I need to use Ubound, or something like that?

Yes that's a good way...

MsgBox(64, "Info", "Number of Dimensions: " & _NumDim($array))
Func _NumDim(ByRef $avArray)
    For $iCC = 64 To 1 Step -1
        If UBound($avArray, $iCC) Then Return $iCC
    Next
    Return SetError(1, 0, 0)
EndFunc

[Siao]

Why not simply UBound($array, 0)?

Edited September 18, 2007 by Siao

[SmOke_N]

Why not simply UBound($array, 0)?

Siao, though I may know a lot of the functions, I have been using them for some time, I'm not sure if "0" always returned the number of dimensions, and I've had no need to look at this function until you pointed it out. If it's been an option the whole time, then I'll say it as I've had no need for the option, and was only looking for a solution I knew would work.

That's why I didn't "simply" use the "0" option.

[ChrisL]

Why do you need array sort?

Can you not sort the data in the original querry like sql?

$oRS.Open("Select * FROM Table1 Order by whaterver the column name to order by is", $oConn, 1, 3)

Failing that there some sql stuff in my signature below, maybe you can use the _SQLGetData2D() function

and then do an array sort, infact if you change the connection string in my _sql.au3 file _SQLConnect() function you could probably use all of it

[Valiante]

Chris,

While that sounds like a very good idea, I'm afraid I'm just not clever enough to implement it. I read some of the code you guys post and it baffles me - I'm just not a born programmer (I wish I was), but I love AutoIt and the things it lets me achieve.

I think I'll try Smoke's idea first, as that seems a little more like something I'd be able to do.

I wouldn't dare ask anyone to write code for me, but sometimes I think that's the only way I'd get anything complex to work!

Thanks for your input though - it's all very welcome and appreciated.

[randallc]

Chris,

While that sounds like a very good idea, I'm afraid I'm just not clever enough to implement it. I read some of the code you guys post and it baffles me - I'm just not a born programmer (I wish I was), but I love AutoIt and the things it lets me achieve.

I think I'll try Smoke's idea first, as that seems a little more like something I'd be able to do.

I wouldn't dare ask anyone to write code for me, but sometimes I think that's the only way I'd get anything complex to work!

Thanks for your input though - it's all very welcome and appreciated.

Hi,

Just for your interst, i found soetimes in vbs with GetRows I had ti go to ".movefirts" first, else I only got 1D array of last row!. Not in au3, at least with ador;

eg

randall

[Valiante]

Yes that's a good way...

MsgBox(64, "Info", "Number of Dimensions: " & _NumDim($array))
Func _NumDim(ByRef $avArray)
    For $iCC = 64 To 1 Step -1
        If UBound($avArray, $iCC) Then Return $iCC
    Next
    Return SetError(1, 0, 0)
EndFunc

Hi Smoke,

Sorry for not replying sooner, work has been busy of late. Ok, I introduced your code and it returns "Number of Dimensions: 2" in the MsgBox.

[Valiante]

Success!

I borrowed some code from the _ArrayDisplay function in the include file "Array.au3", as I realised part of this function's job is to "convert" a 2D array to a 1D array. Here's the resulting code;

#include <Array.au3>
Local $oRS
Local $oConn
Local $1Darray[1]

$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\temp\mydatabase.mdb")

$oRS.Open("Select * FROM RESOURCES", $oConn, 1, 3)

$rows = $oRS.RecordCount
$2Darray = $oRS.GetRows($rows,0,0)

ReDim $1Darray[UBound($2Darray)]
For $i = 0 To UBound($2Darray) - 1
    For $c = 0 To UBound($2Darray, 2) - 1
            $1Darray[$i] &= $2Darray[$i][$c]
    Next
Next

_ArrayDisplay($1Darray)
_ArraySort($1Darray)
_ArrayDisplay($1Darray)

$oConn.Close
$oConn = 0

The result of which is that the second _ArrayDisplay shows a nicely sorted array, with no error as it's now working on a 1D array.

So it would seem then that the statement "This sort functions can handle 1 or 2 dimension arrays" in the Help file for _ArraySort, is somewhat untrue.

Thanks for all your help, folks - you inspired me to find the answer!

[randallc]

OK!

Here's an old example file for 2D sort;

; ArraySortHelp2D.au3
#include <Array.au3>
Opt("TrayIconDebug", 1)
Dim $aArr[10][5] ; Array
for $i=0 to 9
    for $j=0 to 4
        $aArr[$i][$j]=round(Random($i+$j))
    next
Next
_ArrayDisplay($aArr, "$output b4 sort", 0)
;_ArraySort ( ByRef $a_Array, [$i_Descending[, $i_Base=0[, $i_Ubound=0[, $i_Dim=1[, $i_SortIndex=0]]]]] )
_ArraySort($aArr, 0, 0, -1, UBound($aArr,2), UBound($aArr,2) -1)
_ArrayDisplay($aArr, "$output after sort ASC, last column; '_ArraySort($aArr, 0, 0, -1, UBound($aArr,2), UBound($aArr,2) -1)'", 0)
_ArraySort($aArr, 1, 0, -1, UBound($aArr,2), 0)
_ArrayDisplay($aArr, "$output after sort DESC, first col '_ArraySort($aArr, 1, 0, -1, UBound($aArr,2), 0'", 0)
_ArraySort($aArr, 0, 0, -1, UBound($aArr,2), 1)
_ArrayDisplay($aArr, "$output after sort ASC col2 (index1) '_ArraySort($aArr, 0, 0, -1, UBound($aArr,2), 1'", 0)
_ArraySort($aArr, 1, 0, 5, UBound($aArr,2), 1)
_ArrayDisplay($aArr, "$output after sort DESC col2 (index1) (only 5 rows) '_ArraySort($aArr, 0, 0,5, UBound($aArr,2), 1'", 0)

Best, randall