469,925 Members | 1,492 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,925 developers. It's quick & easy.

What is the correct syntax for the GetRows method

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
Oct 2 '07 #1
3 2790
markrawlingson
346 Expert 100+
I believe that the only argument that GetRows takes is a numerical value representing the maximum number or records you wish to return.

Eg:
Expand|Select|Wrap|Line Numbers
  1. oRS.GetRows(1000)
  2.  
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.

Expand|Select|Wrap|Line Numbers
  1. sSQL = "SELECT Name FROM table WHERE IDField >= 32 AND IDField <= 113"
  2. Set oRS = oConnection.Execute(sSQL)
  3.  
  4. '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.
  5.  
  6. 'Then, shove the information into your array using GetRows()
  7. If Not oRS.EOF
  8. aResultSet = oRS.GetRows()
  9. End If
  10.  
  11. 'Clean up the rs Obj
  12. oRS.Close
  13. Set oRS = Nothing
  14.  
  15. 'And use the information...
  16. For i = 0 To UBount(aResultSet)
  17. Response.Write aResultSet(i) & "<br/>"
  18. Next
  19.  
Hope this helps,
Mark
Oct 2 '07 #2
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
Oct 3 '07 #3
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
Oct 3 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Liza | last post: by
11 posts views Thread by Crane Linkledder | last post: by
9 posts views Thread by bajopalabra | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.