Function populates array

Hi,

I need to make the following function, that will sit in a 'local functions' include file, that I can use to return me an array of regions from a database.

I've got it pulling the data out ok, but I'm a little unclear on how to pass an array to a function for it to populate and return.

I currently get a "Subscript out of range" error, which I guess it telling me I'm trying to treat something as an array that isn't an array?



Function GetRegionsList()

dim buf, n
buf=Array()
n=0

Qstr="SELECT * FROM [" & application("tblSKU") & "] WHERE [U_Product_Type] = " & AO_Region
if AO_OnWebSale <> "" then
Qstr = Qstr & " And [U_Web_Sales_Status] = " & AO_OnWebSale
end if
Qstr = Qstr & " Order By DESCRIPTION"

set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open ConnectString
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open qstr,objConn, 1

do while not rs.eof
buf(n) =rs("SKU_ID")
n=n+1
rs.movenext
loop
rs.close
set rs=nothing
objConn.close
set objConn=Nothing

GetRegionsList=buf

End function

If the only field you are using is the SKU_ID then there is no need to select all or loop try:


Function GetRegionsList()

dim strbuf


Qstr="SELECT SKU_ID FROM [" & application("tblSKU") & "] WHERE [U_Product_Type] = " & AO_Region
if AO_OnWebSale <> "" then
Qstr = Qstr & " And [U_Web_Sales_Status] = " & AO_OnWebSale
end if
Qstr = Qstr & " Order By DESCRIPTION"

set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open ConnectString
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open qstr,objConn, 1
'I use a comma as the delimiter
GetRegionsList= rs.GetString(adClipString,,,"," , "Null Entry")

rs.close
set rs=nothing
objConn.close
set objConn=Nothing


End function



Now when ever you call this function you will get a comma delimited string of all the SKUs(which you can split() to create an array), alternatively you could use getRows too return a 2d array.

Thanks for that, I'd forgotten about getRows()

I'd really like to get this returning an array, rather than a string.

If I do this with getString(), I get a list of values as expected.

If I do it with getRows(), I get a seemingly unpopulated array:


<%
dim myArray
myArray=GetRegionsList()
response.write(ubound(myArray))
%>


- this writes "0".


I've tried using the (optional) parameters:

GetRegionsList= rs.GetRows(-1, 1, "SKU_ID")

but still my array remains unpopulated.

Any ideas?

GetRows() should return an array, have you tested your query in SQL Query Analyzer?