Arrays and Strings

[voice3]

Can some one please help, what I am trying to do is extract the data from a Access database and return these variable in to a array, so I can then join them up and pass them to the next page as strings. The problem is that it is not a set number of records so I don't the size of the array, so I am trying to use a loop but at I get is "subscript out of range"

Thank you for your time

<%
Dim Connect, RS, Query, full_total, clientid, itemid, Nearlyprice, Newprice
clientid = Request.Cookies(&quot;user&quot
Set Connect = Server.CreateObject(&quot;ADODB.Connection&quot
Connect.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & &quot;Data Source=D:\staging\basketdb\jigsawbasket.mdb&quot;
Connect.Open
Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
Query = &quot;SELECT * FROM basket WHERE Clientid ='&quot;& clientid & &quot;' &quot;
RS.Open Query, Connect


Dim itemArray, longdescArray, priceArray, qtyArray, StrItem, Strlongdesc, StrPrice, StrQty
qtyArray = Array()
priceArray = Array()
longdescArray = Array()
itemArray = Array()
i=1
Do While Not RS.EOF
itemArray(i) = RS(&quot;Itemid&quot
longdescArray(i) = RS(&quot;longdesc&quot
priceArray(i) = Replace(RS(&quot;price&quot, &quot;.&quot;,&quot; &quot
qtyArray(i) = RS(&quot;qty&quot
RS.MoveNext
i = i + 1
Loop
StrItem = Join(itemArray,&quot;,&quot
Strlongdesc = Join(longdescArray,&quot;,&quot
Strprice = Join(priceArray,&quot;,&quot
StrQty = Join(qtyArray,&quot;,&quot
%>

 

[shaddow]

That doesnt work if you gonna use VBScript,
That would work only in JScript.
For VB You have to Redim your Array variables. You cant use an variable to specify the dimension of an array when dim-ing, unless it's an constant variable, so Redim it's the trick.

This shoulw work.

<%
 Dim Connect, RS, Query, full_total, clientid, itemid, Nearlyprice, Newprice
  clientid = Request.Cookies(&quot;user&quot;)
  Set Connect = Server.CreateObject(&quot;ADODB.Connection&quot;)
  Connect.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & &quot;Data Source=D:\staging\basketdb\jigsawbasket.mdb&quot;
  Connect.Open 
  Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot;)
  Query = &quot;SELECT * FROM basket WHERE Clientid ='&quot;& clientid & &quot;' &quot;
  RS.Open Query, Connect,3,3

recNo=RS.RecordCount
Dim StrItem, Strlongdesc, StrPrice, StrQty
dim qtyArray()
dim priceArray()
dim longdescArray()
dim itemArray()

Redim qtyArray(recNo)
Redim priceArray(recNo)
Redim longdescArray(recNo)
Redim itemArray(recNo)

i=1
Do While Not RS.EOF
itemArray(i) = RS(&quot;Itemid&quot;)
longdescArray(i) = RS(&quot;longdesc&quot;)
priceArray(i) = Replace(RS(&quot;price&quot;), &quot;.&quot;,&quot; &quot;)
qtyArray(i) = RS(&quot;qty&quot;)
RS.MoveNext
  i = i + 1
 Loop
StrItem = Join(itemArray,&quot;,&quot;)
Strlongdesc = Join(longdescArray,&quot;,&quot;)
Strprice = Join(priceArray,&quot;,&quot;)
StrQty = Join(qtyArray,&quot;,&quot;)
%>

________
George, M

 

[tsuji]

Hello voice3,

A possible solution be:

Dim itemArray(), longdescArray(), priceArray(), qtyArray(), StrItem, Strlongdesc, StrPrice, StrQty

i=0   'Or maybe you really want 1-based rather than 0-based
Do While Not RS.EOF
ReDim Preserve itemArray(i), longdescArray(i), priceArray(i), qtyArray(i)
itemArray(i) = RS("Itemid")
longdescArray(i) = RS("longdesc")
priceArray(i) = Replace(RS("price"), "."," ")
qtyArray(i) = RS("qty")
RS.MoveNext
  i = i + 1
Loop
StrItem = Join(itemArray,",")
Strlongdesc = Join(longdescArray,",")
Strprice = Join(priceArray,",")
StrQty = Join(qtyArray,",")

regards - tsuji

 

[tsuji]

Hello shaddow,

Haven't seen your posting before I draft mine. Not sure my solution is satisfactory or not. Glad there are difference.

regards - tsuji

 

[shaddow]

Hehe, they are both ok,actually we have explained ways of using Redim and Redim Preserve.

________
George, M

 

[voice3]

Thanks alot for your help it works like a treat, why is it always something simple which stop everything from working.

Thank again

 

[clarkin]

I may be missing the point entirely, but ADO has a couple of built in functions to throw a recordset into an array.

See here

http://www.aspfaq.com/show.asp?id=2467

and here

http://msdn.microsoft.com/library/en-us/ado270/htm/mdamth02_10.asp

So you could:

RS.Open Query, Connect,3,3

dim RSdata
'RSdata = RS.getRows() 'gets all rows/fields
' or maybe
RSdata = RS.getRows( , , Array("Itemid","longdesc","price","qty")) 'gets these specified fields

RS.close()

RSdata would be a 2d array of your results.

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].