how many items SQL

[zivon]

hello everybody!

I have an sql statment, and I want to add all the data into an array.

when I declare the array I need to write the amount of items.

how can I know before I do the loop rs.EOF = false
how many items the sql statment has ?

thanks in advance,
Idan

[shweta123]

Hi,

To change upper bound for the array at run time , Redim statement is used.
Syntax:
Redim arrayname(arraylimit)

Example:
<%
Dim myArray(0)
Redim PRESERVE myArray(uBound(myArray)+1)
%>

Explanation :
In the above example Array called as myArray is defined with zero dimensions. Afterwords array size is incremented by one.

hello everybody!

I have an sql statment, and I want to add all the data into an array.

when I declare the array I need to write the amount of items.

how can I know before I do the loop rs.EOF = false
how many items the sql statment has ?

thanks in advance,
Idan

[DrBunchman]

Hi Zivon,

You can use the rs.RecordCount property to get the number of records returned by your query. To get this to work you'll need to do the following:

1. In order to to use a variable as your array size you'll need to dimension the array without a size then redim it with the record count.
2. You need to specify the correct cursor type when you open the recordset. The default cursor type is adOpenForwardOnly which doesn't allow the RecordCount property.

Example code:

 
<%
Dim oConn, rs, sSQL
Dim sArray()
Dim x
 
Set oConn = server.createobject("ADODB.connection")
oConn.Open Session("sConStr")
 
Set rs= server.createobject("ADODB.Recordset")
sSQL=" SELECT * FROM Table1 "
 
'The 3 in the rs.Open statement specifies the cursor type as adOpenStatic which allows the RecordCount property
rs.Open sSQL, oConn, 3
 
ReDim sArray(rs.RecordCount)
x = 0
Do until rs.eof
sArray(x) = rs("Column1")
x = x + 1
rs.MoveNext
Loop
%>
 

Let me know if you have any problems.

Hope this helps,

Dr B

[zivon]

Thanks for you both :)

Have a nice week !