Getrows and 2 dimensional array - need gurus help!

Results 1 to 3 of 3

Thread: Getrows and 2 dimensional array - need gurus help!

  1. #1
    Draya Guest

    Default Getrows and 2 dimensional array - need gurus help!

    Problem:<BR> allData= myRecordSet.GetRows<BR> colNo = ubound(alldata,1)<BR> rowNo = ubound(alldata,2)<BR> newcolNo=ColNo + 1<BR> ReDim Preserve allData(newcolNo, rowNo)<BR><BR>This code gives me a " Microsoft VBScript runtime error &#039 800a0009&#039 Subscript out of range " error.<BR><BR>my Goal: I will need loop through each row and average some of the fields, then put the result in the last field/column of each row of the redimmed array.<BR><BR>example:<BR>I have a recordset (actually a query) in an Access database. <BR>I get the recordset using .getrows.<BR>Depending on a users selections, I will average each rows selected fields and place the result in a new field. But to do this, I need to make my array larger by 1 field to store the average calculation. <BR><BR>The reason I need to store the average in my array is because the next step will be to sort the array in descending order by the average scores. Next I will display on screen the top X average scores.<BR><BR>Please give any Advise/work arounds. <BR>Thank You!<BR>

  2. #2
    BalletChick Guest

    Default No guru needed.

    Hello Draya,<BR><BR>I&#039m not sure if you&#039ve bothered to look at the documentation on REDIM, which is the line that is giving you the error, but if you had, you would have noticed that you can only change the LAST dimension (not the first, as you are trying to do) when you use PRESERVE.<BR><BR>Does that help?<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default Ehhh...this one not that obvious...

    As &#039Chick pointed out, you can NOT change the number of columns once the array is created.<BR><BR>SO the solution is to change the number of columns *BEFORE* the array is created.<BR><BR>But the number of columns is set automatically by the GetRows method.<BR><BR>What to do?<BR><BR>Easy answer: Add a dummy column in your SELECT statement that will, indeed, give you a column to put your new value into!<BR><BR>Thus:<BR><BR>&#060;%<BR>QRY = "SELECT 9999,* FROM table WHERE ... ORDER BY ..."<BR>Set RS = someConnection.Execute(QRY)<BR>allData = RS.GetRows<BR>%&#062;<BR><BR>And now *every* row (record) in that allData array will have a 9999 as the value of the first column therein.<BR><BR>That is,<BR><BR>&#060;% allData(0,anyRow) %&#062;<BR><BR>will be 9999. Or whatever constant value you put into the SQL SELECT. It could even be a string, but a number uses less memory. You can even use 0. I use 9999 as a kind of flag: If I ever were to find 9999 while later processing the array, it would indicate to me that somehow I had missed processing that row.<BR><BR>Hokay?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts