multidimensional array, variable number of elements 2

[skibascott]

I am trying to read a recordset into a multidimensional array. Here is the code that I have so far:

<%
  Dim objRS         
  Dim numRows
  Dim avarFields(2)
  avarFields(0,) = "sheetno"
  avarFields(1) = "pattern"
  avarFields(2) = "machine"
	
	
  Set objRS = Server.CreateObject ("ADODB.Recordset") 
            objRS.Open "GrindsheetLR",strConnect,adOpenKeyset,adLockReadOnly, adCmdTable 
	
  numRows = 0
  Do While NOT objRS.EOF
    numRows = numRows + 1
    ReDim Preserve avarFields(2, numRows)
    avarFields(0, numRows - 1) = objRS(0)
    avarFields(1, numRows - 1) = objRS(1)
    avarFields(2, numRows - 1) = objRS(2)
    objRS.MoveNext
  Loop

  objRS.Close
  Set objRS = Nothing
%>

For each record I only want to read the three fields: sheetno, pattern and machine. What I am aiming for is this example: avarFields(0,0)=8596 (which is the sheetno)
avarFields(0,1)=RF2L005 (this is the pattern)
avarFields(0,2)=Mold/Pour (this is the machine)

I have been able to succesfully copy this data into a one-dimension array, so the db connection details that are not shown are correct. When executing the code here, I receive a subscript out of range error that cites the ReDim line. There are most likely a few problems here, for I am totally new to ASP. Any suggestions on where to start?

 

[ChrisHirst]

much simpler to use GetRows()

dim RS_Array

strSQL = "SELECT sheetno,pattern,machine FROM table ;"

objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText

RS_Array = objRS.GetRows()

array returns with;
columns in 'x' axis
rows in 'y' axis



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[Genimuse]

Plus, right after your .GetRows() line you can close the recordset and dump the object, easing the load on the server and the database.

GetRows rules!

 

[tsuji]

Hello skibascott,

>What I am aiming for is this example:
>avarFields(0,0)=8596 (which is the sheetno)
>avarFields(0,1)=RF2L005 (this is the pattern)
>avarFields(0,2)=Mold/Pour (this is the machine)

I thank what you really meant is
avarFields(0,0)=8596 (which is the sheetno)
avarFields(1,0)=RF2L005 (this is the pattern)
avarFields(2,0)=Mold/Pour (this is the machine)

You have to make a little concession of letting (.,0) reserved for the title and the actual data starting from (.,1) onward, meaning:
avarFields(0,1)=8596 (which is the sheetno)
avarFields(1,1)=RF2L005 (this is the pattern)
avarFields(2,1)=Mold/Pour (this is the machine)

And this is how the redim functions.

<%
  Dim objRS         
  Dim numRows
  Dim avarFields(2,0)
  avarFields(0,0) = "sheetno"
  avarFields(1,0) = "pattern"
  avarFields(2,0) = "machine"
    
    
  Set objRS = Server.CreateObject ("ADODB.Recordset") 
            objRS.Open "GrindsheetLR",strConnect,adOpenKeyset,adLockReadOnly, adCmdTable 
    
  numRows = 0
  Do While NOT objRS.EOF
    numRows = numRows + 1
    ReDim Preserve avarFields(2, numRows)
    avarFields(0, numRows) = objRS(0)
    avarFields(1, numRows) = objRS(1)
    avarFields(2, numRows) = objRS(2)
    objRS.MoveNext
  Loop

  objRS.Close
  Set objRS = Nothing
%>

This does not mean to imply any superiority/inferiority with respect to .getrows as advised. You have to make your choice.

regards - tsuji

 

[tsuji]

Correction:

I should start with dim avarFields(). The corresponding starting lines are:
[tt]
Dim avarFields()
reDim avarFields(2,0)
[/tt]
- tsuji

 

[Genimuse]

Excellent non-lazy answer, tsuji!

I will argue, though, that GetRows accomplishes the effectively same result for far less processing time, and far less likelihood of bugs.

 

[skibascott]

Thank you for all of the suggestions.

I did have this setup using GetRows. When I used GetRows I could display the data on the page, but got stuck trying to id the different records and fields?

I would like to display the records in a select box so that when a user selects a record, the sheetno field can be used as a parameter to send back to a query that will give me all of the documents associated with the selected sheetno.

I figured that if I use a multidimensional array the individual fields would have names whose values I could use to assign to variants.

If I use GetRows, how can I identify only the sheetno in a selected record?

 

[Tarwn]

Well, sheetno would be the array(0,recordCtr) value, so if you output your options with that in the value field you can still give the user something readable between the option tags (text):

--- assume an array called myArray created using GetRows and the above three columns of data

<select name="selSheet">
<%
Dim recordCtr
For recordCtr = 0 to UBound(myArray,2)
   %><option value="<%=myArray(0,recordCtr)"><%=myArray(1,recordCtr)%></option>%><%
Next
%>
</select>

That would give you a select box filled with options for various patterns and when the user selected one the passed value for selSheet would be the sheetno for the selected pattern. Only the value for the option gets passed (not the text) so you could even put the pattern and machine name in between the option tags and they would still only pass a sheetno when it got selected (as it is in the value="" section.

-T

 

[skibascott]

I did get this to work using a combination of Tarwn and ChrisHirsts ideas. I have learned that GetRows is definitely the way to go.
I thank everyone for there posts.