Creating Dynamic arrays. 1

[qwert231]

I want to create an array depending on the size of a recordset. Let say recSet.RecordCount = 4
So, I want my array to be testArray(3)
I want something like this:
testArray(recSet.RecordCount - 1)

Now, I can't do that, but that is the basic effect I would like to get. Any ideas guys?

 

[JohnYingling]

Dim testArray ' Variant
.......
Redim testArray(recSet.RecordCount - 1) ' Put Array in variant
......
Redim Preserve testArray(recSet.RecordCount - 1) ' Save Contents on resizing

http://www.VBResizer.com

Generate Forms/Controls Resizing/Tabbing Class

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[Jonax]

If you retrieve your records from the db using

testArray = recSet.getrows

you'll get the array and increased performance. This is not a bug - it's an undocumented feature...
;-)

 

[qwert231]

Kewl, now how do I access the items in the array? How is it set up? No documentation, huh? Hmmm... tough.

 

[alan2624]

Use this to set up the database connection:
<%
'connection.inc create connection to inventory.mdb
dim cnnDataACCESS 'ActiveX Data Object-Connection Object
dim strConnectACCESS 'Connection String

'create instance of ActiveX Connection Object
set cnnDataACCESS = SERVER.createObject(&quot;adodb.Connection&quot
'build the connection string
strConnectACCESS= &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot;& _
&quot;Data Source=&quot;&SERVER.mapPath(&quot;.\Resources\PhoneValid.mdb&quot&&quot;;&quot; & _
&quot;Persist Security Info=False&quot;
cnnDataACCESS.open(strConnectACCESS)
%>

set these up as variables:
Dim rsX, testarray
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTable = 1

Set up the RecordSet object:
Set rsX = Server.CreateObject(&quot;ADODB.Recordset&quot

Set up your database query:
I use the variable sQuery

Open the recordset:

rsX.Open sQuery, strConnectAccess, adOpenForwardOnly, adLockReadOnly, adCmdTable

Call the Get rows line:
testarray = rsX.GetRows

Close the connection:
rsX.Close

Set the Connection to Nothing:
set rsX = nothing

You can get the upper bound of the test array by:
UBOUND(testarray, 2)
You can loop through the array using a for loop
You can access the rows and columns of the array by:
testarray(row, column)

 

[Jonax]

Hehe,
oki, here goes:

If you have a table like this:

UserId	UserFName		UserLName
1			Jonas			Jensen
2			qwert			123

and you do this:

strSQL = "SELECT * FROM tbUsers ORDER BY UserLName, UserFName"
set conn = server.createobject("adodb.connection")
conn.open strConn 'strConn is previously set!
set rs = server.createobject("adodb.recordset")
rs.open strSQL, conn, 1, 3
if NOT (rs.bof AND rs.eof) then 'Check for empty recordset
	arrResults = rs.getrows
end if
rs.close
set rs = Nothing
conn.close
set conn = Nothing

if varType > 8191 then 'Check that it indeed IS an array
	response.write &quot;<table cellpadding=2 cellspacing=0 border=0>&quot;
	for t = 0 to ubound(arrResults,2)
		response.write &quot;<tr>&quot;
		for u = 0 to ubound(arrResults,1)
			response.write &quot;<td valign=top>&quot; & arrResults(u,t) & &quot;</td>&quot;
		next
		response.write &quot;</tr>&quot;
	next
	response.write &quot;</table>&quot;
end if

You'll have a nice little table

A few examples on what gets where in the array:

response.write arrResults(0,0)

will output &quot;1&quot;

response.write arrResults(0,1)

will output &quot;2&quot;

response.write arrResults(1,1)

will output &quot;qwert&quot;

So I guess you could sum it up in this: arrResults(Column,Row)

...How is that for documentation!
This is not a bug - it's an undocumented feature...
;-)

 

[qwert231]

Kewl.