Search code examples
vbscriptasp-classic

Remove a dimension from an array

I am using an array to get the column names from a table and I am using Getrows to populate it from a MYSQL recordset. Like this:

arrFOO= fooRS.GetRows()

Now, this does get the job of getting the columns loaded into the array. However, GetRows is a multidimensional array, and contains the data about the column (datatype,max length,etc).
My goal is to pop the array into a SELECT/OPTION form and allow the user to select the column from a form. The problem is that being a multidimensional array, i do not need the other data in the array at all. Originally, was curious if its possible to drop the entire dimension of an array, maybe use of a redim preserve might be used, but no luck so far. Is there a way to getrows and only get a single dimension, or is there a way to trim a multidimensional array down to just one dimension? ie - array(0,0) becomes array(0) and I want to preserve the 2nd slot, and drop the first I believe, if that helps explain it better. Here is the option/select example:

                    <% for each x in arrfoo
                        formloopcount = formloopcount + 1
                    %>
                        <option id="optionid" value="<%=x%>" ><%=x%></option>
                    <%  next %>

What I have is working but not as intended, because the array is multidimensional, the OPTION includes the datatype, and other data i do not need nor want displayed during the process. The drop down is displaying results like this :

COLUMNAME
INT
NO

COLUMNAME
varchar(#)
YES

So although the form works in concept, if they select the column it passes and works normally, but obviously i do not want users to select the other items or the space between them, just column names and that is it. Thank you for your time, and if you need more examples or would like elaboration, please ask. And in closing, I am aware how much validation I am going to need to do to allow a form like this to exist, but the end user wants to be able to change things on the fly, and its all an internal network, so security and access is super limited as it is, and only the CTO would even have access to the UPDATE end of this, for now, this is just a simple select, and the users doing it should have the rights to access it anyways, so long story short, I don't need a lecture on validation or SQL injection, just need some help with the array in question, but will accept any advice or alternative methods, thanks again!

Solution

  • The GetRows() method of ADODB.Recordset object returns a 2-Dimensional Array containing the data (there are no data types etc, it is just the raw data in columns and rows). As its data is structured ordinally it suits using a For statement to enumerate the data via an index as opposed to using a For Each statement which will not behave as you expect.

    Using IsArray() to check the array is a valid array and then using UBound() to determine how many rows of data the array contains is recommended.

    Here is a quick example of working with GetRows(), the example will enumerate each column and row and return the value.

    'Assuming your ADODB.Recordset is fooRS
Dim data: data = fooRS.GetRows()
Dim row, col, rows, cols
If IsArray(data) Then
  rows = UBound(data, 2)
  For row = 0 To rows
    cols = UBound(data, 1)
    For col = 0 to cols
      Call Response.Write(data(col, row))
    Next
  Next
End If

    This allows you a great deal of control, for example, you could return just the values in the first column for all rows (adjust the first element in the array to return other columns instead).

    If IsArray(data) Then
  rows = UBound(data, 2)
  For row = 0 To rows
    Call Response.Write(data(0, row))
  Next
End If

    Useful Links