Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts

    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?


    Code:
    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

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If the only field you are using is the SKU_ID then there is no need to select all or loop try:

    Code:
    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.

  • #3
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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?

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    GetRows() should return an array, have you tested your query in SQL Query Analyzer?
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •