Hi,

I am trying loop through a one column recordset, which has been Select Distinct'ed.

I simply want to assign an operator to an array so I can later loop through another table and count the number of time that operators appears. The number of operators will vary each time so I need to use a dynamic array.

Code:

str_no=CStr(Request("str_no"))
str_name=CStr(Request("str_name"))

DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=DATA_WAREHOUSE;UID=INFOSEC;pwd=password"
objConn.Open

strSQL="SELECT DISTINCT to_number(ltrim(rtrim(op))) op FROM users WHERE str_no='"& CStr(Request("str_no")) & "' order by op"


Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn

i=0
dim myArray()

while not objRS.eof

* myArray(i)=objRS("op")
objRS.Movenext
i=i+1

wend


I am receiving the error "Microsoft VBScript runtime error '800a0009'

Subscript out of range

/chall_by_coll.asp, line 49"

I have put a * by line 49.

Any ideas on where I am going wrong?

Thanks in advance
Ron

You can not refer to a recordset in the way you have, you need to assign it to an array using getRows like:

objRS.Open strSQL, objConn
myArray = objRS.getRows()

Have a look on the net about the getRows part of the above code

Wind is your friend
Matt

Thanks for the tip Matt.

I managed to use the GetRows thing to get the unique values into an array.

I now need to get a count of how many times the values occur (in the same table).

Is there an easy way of doing this? I could just select the column and order it by "op" which would look something like this:

101
101
101
302
302
603
603 etc etc

I previously thought I would need a list of the unique values (hence the SELECT DISTINCT above) and then loop though a recordset and count the occurances however I am not getting very far..

Is there a better way of achieving this?

I have not bothered to include my pitiful attempts at coding this problem as I was hoping someone could point me in the right direction.

It's not that complicated is it?

Cheers Guys.