[thelist] conserving resources - closing a recordset in ASP

[Wade Armstrong]

On 9/22/03 Sarah <poohbear at designshift.com> wrote:

>
>> >I am trying to make my ASP code a little easier to write, and read, 
>by
>> >creating a function to perform a query for me and return a
recordset.
>> >Here
>> >is the function:
>> >   function doQuery(sql)
>> >   {
>> >     var rs = Server.CreateObject("ADODB.Recordset");
>> >     rs.Open(sql,conn,adOpenStatic);
>> >     return rs;
>> >   }
>> >Of course, using this function means that the recordset doesn't get
>> >closed.
>>
>>How about:
>>
>>   function doQuery(sql)
>>   {
>>     var rs = Server.CreateObject("ADODB.Recordset");
>>     rs.Open(sql,conn,adOpenStatic);
>>     var myArr = rs.GetRows()
>>     rs.Close
>>     rs = null
>>     return myArr;
>>   }
>>
>>That will leave you with a nice, handy two-dimensional array of your
>>query results, through which you can iterate without having to carry
an
>>object around.
>
>Wade,
>
>I tried this, but couldn't figure out how to loop through the returned 
>array. This is what I was trying:
>var contents = doQuery('SELECT * FROM Contents');
>for (i = 0; i < contents.length; i++)
>{
>   Response.Write(contents[i][0] + '<br />');
>}
>But for some reason contents.length is undefined.

I'm a VBScript person, not a JScript person, so my ability to help on
this detail is limited, but I do see this example:
http://www.aspalliance.com/PeterJohnson/JSGetRows.asp
describes how to use an array from GetRows() in JScript


>Also, I like being able to use the recordset because I can then refer
>to the columns in the query by name. 
It's easy to set up some constants to use, rather than array positions:
    const PersonName = 47
    const PersonFlavor = 48
    for (var iRecord = 0; iRecord <= iUBound; iRecord++) {
        strName = myArray.getItem(PersonName, iRecord);
        strFlavor = myArray.getItem(PersonFlavor, iRecord);
        response.Write(strName + ' likes ' + strFlavor + ' ice cream.');
        }
        
(give or take dozens of syntax errors due to unfamiliarity with
server-side JScript)


>Maybe I will just give up on my function and write out the full code
>each time
I have a function like the one you're trying to write, just in VBScript,
and I find it saves me an immense amount of time, not least on typos
writing out the full code incorrectly. I highly recommend trying to make
this work.

Wade