[thelist] conserving resources - closing a recordset in ASP

[Sarah]

> >> >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;
> >> >   }

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

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

Thanks for the sample code, Wade. Unfortunately, it looks like using 
GetRows, since it means setting up constants to refer to the columns in my 
query, is going to be just as - if not more - complex than just not using 
this function at all. I think that as an alternative, I will create a 
snippet in Homesite to paste in the framework of the code I need for 
executing a query.

Sarah

<tip type="Homesite" author="Sarah">
Homesite's snippets feature can be a huge time-saver if you find yourself 
typing the same bit of code over and over. Go to the snippets tab and 
right-click to add a snippet. Then you can add a keyboard shortcut to a 
snippet by going to Options > Customize > Snippet Shortcuts.
</tip>

While I'm at it...

<tip type="Homesite" author="Sarah">
The only problem I've ever really had with Homesite - aside from the fact 
that it's not free :) - is that in order to change the case of a selection, 
you have to right-click, go to Selection, and change to upper or lower 
case. So I did some searching and found these scripts to change case 
(lower, upper, or proper case). If you download these script, you can (a) 
go to Options > Customize > Script Shortcuts to add a keyboard shortcut or 
(b) go to Options > Customize > Toolbars to add a custom button to run the 
scripts. The scripts are available at:
http://www.aftershockweb.com/free/free_scripts.cfm
("ColdFusion Studio / HomeSite Convert Case Scripts")
</tip>