related sites: <FREE Help> <ASP> <Asp.net> <worldwide> feedback: <lovethat> <hatethat> <thanks> <credits> <contact us>
Ultimate GetRows by Charles Carroll
Sure GetRows is fast and improves scalability. I discussed why http://www.learnasp.com/advice/whygetrows.asp But it is a pain because it transfers the recordset into a multi-dimensional array and the code to walk the array is complex and re-usability is low. Well thanks to the magic of dictionaries or execute we can produce clever code that deals with named fields.
Here is the dictionary version:
<Test Script Below>
<%Option explicit%> <!--#include virtual="/learn/test/lib_getrows_dictionary.asp"--> <HEAD><TITLE>test getrows</TITLE></HEAD> <HTML><body bgcolor="#FFFFFF"> <% 'on error resume next dim myconn,mySQL,myfields,rowcounter Dim mydata myconn="DSN=student;uid=student;pwd=magic" 'mySQL="select PubID, [Company Name], Address, City, State, Zip, Telephone, Fax, Comments from publishers where state='NY'" mySQL="select * from publishers where state='NY'" set myfields=server.createobject("scripting.dictionary") Call DataGetrows(myConn,mySQL,mydata,myfields) ' now display the data response.write "<table border='1'><tr>" & vbcrlf FOR rowcounter= 0 TO myfields("rowcount") response.write "<tr>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("pubid"),rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("Company Name"),rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("address"),rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("city"),rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("state"),rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("zip"),rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("telephone"),rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("fax"),rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(myfields("comments"),rowcounter) & "</td>" & vbcrlf response.write "</tr>" & vbcrlf NEXT response.write "</table>" ' finished displaying the data set myfields=nothing %> </BODY></HTML>
Here is the relevant library:
<% SUB DataGetRows(parmConn,parmSQL,byref parmArray,byref parmDict) dim conntemp,rstemp,howmany,counter set conntemp=server.createobject("adodb.connection") conntemp.open parmConn set rstemp=conntemp.execute(parmSQL) If rstemp.eof then response.write "No records matched<br>" response.write mySQL & "<br>So cannot make table..." rstemp.close set rstemp=nothing conntemp.close set conntemp=nothing exit sub end if ' Now lets fill up dictionary with field names/numbers howmany=rstemp.fields.count for counter=0 to howmany-1 parmdict.add lcase(rstemp(counter).name), counter next ' Now lets grab all the records parmArray=rstemp.getrows() parmdict.add "rowcount", ubound(parmarray,2) parmdict.add "colcount", howmany-1 rstemp.close set rstemp=nothing conntemp.close set conntemp=nothing END SUB %>
Here is the variables version that is dependent on VBSCRIPT 5.5 Execute (see /learn/vbs5.asp):
<%Option explicit%> <!--#include virtual="/learn/test/lib_getrows_vars.asp"--> <HEAD><TITLE>test getrows</TITLE></HEAD> <HTML><body bgcolor="#FFFFFF"> <% 'on error resume next dim myconn,mySQL,myfields,rowcounter Dim mydata myconn="DSN=student;uid=student;pwd=magic" 'mySQL="select PubID, [Company Name], Address, City, State, Zip, Telephone, Fax, Comments from publishers where state='NY'" mySQL="select * from publishers where state='NY'" Call DataGetrows(myConn,mySQL,mydata,myfields) execute(myfields) ' now display the data response.write "<table border='1'><tr>" & vbcrlf FOR rowcounter= 0 TO rowcount response.write "<tr>" & vbcrlf response.write "<td valign=top>" & mydata(pubid,rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(Company_Name,rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(address,rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(city,rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(state,rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(zip,rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(telephone,rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(fax,rowcounter) & "</td>" & vbcrlf response.write "<td valign=top>" & mydata(comments,rowcounter) & "</td>" & vbcrlf response.write "</tr>" & vbcrlf NEXT response.write "</table>" ' finished displaying the data %>
<% ' requires VBScript 5.5 since EXECUTE will be used on return fields ' spaces become _ in variable names but may need to deal with others in future SUB DataGetRows(parmConn,parmSQL,byref parmArray,byref parmFields) dim conntemp,rstemp,howmany,counter,tempname set conntemp=server.createobject("adodb.connection") conntemp.open parmConn set rstemp=conntemp.execute(parmSQL) If rstemp.eof then response.write "No records matched<br>" response.write mySQL & "<br>So cannot make table..." rstemp.close set rstemp=nothing conntemp.close set conntemp=nothing exit sub end if ' Now lets fill up dictionary with field names/numbers howmany=rstemp.fields.count for counter=0 to howmany-1 tempname=lcase(rstemp(counter).name) tempname=replace(tempname," ","_") parmfields=parmfields & tempname & "=" & counter & vbcrlf next ' Now lets grab all the records parmArray=rstemp.getrows() parmfields=parmfields & "rowcount=" & ubound(parmarray,2) & vbcrlf parmfields=parmfields & "colcount=" & howmany-1 & vbcrlf rstemp.close set rstemp=nothing conntemp.close set conntemp=nothing END SUB %>
"The Worlds Fastest Listbox" http://www.learnasp.com/learn/speedappdata.asp shows the only faster way to display databases.
I have now made a huge library I am maintaining and constantly adding features to that encapsulates the power of Getrows/Getstring but takes away the complexity and incorporates caching. see: http://www.learnasp.com/learn/rsfast.asp for my library that makes Getrows/Getstring easy.