What is the correct syntax for the GetRows method

[colm1974]

Hi,
I hope somebody can help. I have been trying to use the GetRows method but it only seems to work when there are no constants assigned.

aGetElement = rs_zgl_element.GetRows()
This works and I can select any array element I need from aGetElement. I can use Lbound, Ubound etc etc. In this case the recordset has in effect 2 columns and 197 rows. Column names are converted (int), name (varchar).


aGetElement = rs_zgl_element.GetRows(numRows, startRow, converted)
This doesn't work

I have checked to make sure that numRows, startRow are valid. 'converted' is the name of the field I want the data from in the recordset. I have also tried,

aGetElement = rs_zgl_element.GetRows(numRows, startRow, "converted")
but this does not work either.

Replacing numRows & startRow with integers 92 & 21 respectively does not work.

The error I get regardless is

ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I have looked for reasons why but there seems to be little info around about using GetRows with this syntax.

Any help would be much appreciated.
Thanks

[markrawlingson]

I believe that the only argument that GetRows takes is a numerical value representing the maximum number or records you wish to return.

Eg:

oRS.GetRows(1000)
 

Will limit the records returned to a maximum of the first 1000 records in the database. It doesn't take any further arguments as far as I am aware, and therefore the error message that you are recieving (arguments are out of acceptable range) makes sense.

It seems to me that what you are trying to do is return a recordset into an array, but tell the recordset the record in which to start at, and the record in which to end at. In addition, it looks like you are also trying to retrieve JUST one column rather than all columns from the table.

eg: Return records # 32 to # 113 and return only the column name "Name"

If this is a correct assumption, then you're going about it in the wrong direction. You can't tell GetRows to exclude this information, GetRows just grabs data from a recordset, cuts it up according to column/row and pops it into an array. To exlude records/columns, you'd do that in your SQL statement itself before you use GetRows - then GetRows will push whatever is returned into your array properly - with your exclusions in tact.

As for the example above, this would be the way to go about doing it.

sSQL = "SELECT Name FROM table WHERE IDField >= 32 AND IDField <= 113"
Set oRS = oConnection.Execute(sSQL)
 
'The above will open a recordset retrieving just the "Name" field from the table where the id field, or autoincrement field is between record number 32 and 113 and will return only this information.
 
'Then, shove the information into your array using GetRows()
If Not oRS.EOF
aResultSet = oRS.GetRows()
End If
 
'Clean up the rs Obj
oRS.Close
Set oRS = Nothing
 
'And use the information...
For i = 0 To UBount(aResultSet)
Response.Write aResultSet(i) & "<br/>"
Next
 

Hope this helps,
Mark

[colm1974]

Hi Mark,

Thanks for reply. You are spot on in what I want to do. Ultimately the 'filtered' arrays must end up in javascript page and the way this is constructed limits how and when the arrays are constructed. It may end up that I have to use your suggestion in the end.

Not wanting to challange your knowledge (as I am sure its vastly greater than mine... I am only plodding through vbscript) but I did find 2 references to the GetRows method that eluded to more than you suggest.
I'm not sure what the rules in this forum are about adding weblinks to other sites (so sorry if I am breaking)

http://www.w3schools.com/ado/met_rs_getrows.asp

http://www.devguru.com/Technologies/ado/QuickRef/recordset_getrows.html

Maybe I have mis-understood something...

Thanks again
Colm

[colm1974]

I nutted out an alternative approach to using the GetRows as described in intial thread. Here is the code

'To extract the row start position I created an index of the recordset and compared where the values changed.

' Initialising statusChangeArray
statusChangeArray(scIndex) = 0
scIndex = scIndex + 1

Dim i
For i = 1 To MaxArraySize
if statusArray(0,(i-1)) <> statusArray(0,(i)) then
' Status value has changed - record the position where this happened and increment size of statusChangeArray

statusChangeArray(scIndex) = i
scIndex = scIndex + 1

end if
Next

Dim numRows
Dim startRow
Dim RowCounter
Dim lnColumnCounter
Dim aGetElement()
Dim aGetElement_rowValue


For i=1 to scIndex-1
if i = scIndex-1 then numRows = (Ubound(statusChangeArray)) - statusChangeArray(i) startRow = statusChangeArray(i)
else
numRows = statusChangeArray(i+1) - statusChangeArray(i)
startRow = statusChangeArray(i)
end if

'this is a bit of checking I was doing to try and understand getrows
'If not rs_zgl_element.bof then
'rs_zgl_element.MoveFirst
'aGetElement = rs_zgl_element.GetRows()
'aGetElement = rs_zgl_element.GetRows(numRows, startRow, "converted")

ReDim aGetElement(1,(numRows))

aGetElement_rowValue = numRows
statusArray_rowValue = startRow + numRows

'---start----This is the piece I used to replace the getrows

For RowCounter = startRow to (startRow + numRows)

aGetElement(0,aGetElement_rowValue) = statusArray(0,statusArray_rowValue)
aGetElement(1,aGetElement_rowValue) = statusArray(1,statusArray_rowValue)

if aGetElement_rowValue <> 0 then
aGetElement_rowValue = aGetElement_rowValue-1
statusArray_rowValue = statusArray_rowValue-1
end if
'---end----

Next

'else
'response.write ("No records to select")
'end if

'Now passing the new VBArray to javascript
Call ConvertToJSArray2D(aGetElement, "Status" & i)
Next

Maybe this could be of use....
Thanks
Colm