1

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!

user692942
  • 16,398
  • 7
  • 76
  • 175
easleyfixed
  • 219
  • 1
  • 13
  • The `ADODB.Recordset` method `GetRows()` returns data only, it is a 2-Dimensional array consisting of columns and rows, the array is structured as `data(col, row)` so for example `data(0, 0)` will return the value of the first column first row in the array. You can use a `For` statement to iterate through the array row by row, also recommend using `UBound(data, 2)` to determine how many rows of data you have. See [Using Stored Procedure in Classical ASP .. execute and get results](https://stackoverflow.com/a/21944948) for an example of using `GetRows()` and enumerating the data. – user692942 Apr 01 '22 at 17:27

1 Answers1

1

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

user692942
  • 16,398
  • 7
  • 76
  • 175
  • So is there any way to trim an array and drop the first or second slot of the array, make a 2 dimensional array into a preserved 1 dimensional? Being I can do one line of code to transfer the rs to the array with getrows is the only reason it was used. I guess there isn't a non logical based way to get an array from a recordset that is not multidimensional? Was trying to avoid adding yet another if/then for next loop into this,? I do a row count before i do the loop so i could toss a ubound in there if I must, but getrows spoiled me .. one line, just need only 1/2 of what it offers. – easleyfixed Apr 01 '22 at 18:10
  • @easleyfixed You could enclose the code in a function, that would make it a one-liner? End of the day, you are returning a resultset which is down to what data you return from MySQL. If you want to limit it to a specific column of data just return that one column in your `SELECT` SQL statement. Don't get the extra `If` issue, would prefer to write guarded code than write a one-liner and not know whether it might fail in the future or not. – user692942 Apr 01 '22 at 18:14
  • @easleyfixed An `ADODB.Recordset` but its very nature is a multidimensional array as you have columns and rows, how do you expect to splice that into one dimension? – user692942 Apr 01 '22 at 18:20
  • lol, i mean sure if you write all the code to build the function to turn it into a one liner, than its not really a one liner. I guess I should of said it like this, there is no Redim ARRAY DropArray(0) type function that already exists. So the answer is that getrows will not work for me since i can't just drop a dimension. So the long answer is I need to build a manual array and loop through the ubound, which previous sp already counts the rows, just stumbled into getrows and never used it, but now I know why I haven't. – easleyfixed Apr 01 '22 at 18:26
  • Oh sure of course a recordset can be a form of multi dimensional array, and tbh I use it mostly cause I know what col names I am dealing with, but I've been tasked with doing highly irregular things against my wishes in order to make it dynamic enough I wont have to drive at 3am to add a column and supporting form code to the database on a whim. But I guess, as far as getrows, being i only need the first value it could contain since I am populating a drop down with the result, i need to build a manual array and loop it that way. – easleyfixed Apr 01 '22 at 18:29
  • Personally, I've always found `GetRows()` really easy to use, prefer it over enumerating `ADODB.Recordset` every time. Simple and flexible. It also has the added benefit of not requiring a DB connection as it's in-memory so DB calls can be short-lived and not require being left open for the duration of a page. – user692942 Apr 01 '22 at 18:29
  • Oh don't get me wrong, I can see where it could be handy for some things, I am probably going to use it elsewhere on this page to return results, but for now, I have to get the ability to generate those results dynamic from a form, so thinking a single dimensional array with all the column names from a recordset pull is probably workable, but just hoped there was something as easy as getrows to use, or a method to drop a dimension of an array. But seems no luck .. perhaps those exist in the .NET world? Anywho, thanks again! – easleyfixed Apr 01 '22 at 18:42
  • Wait .. hmm I had an idea .. what if i loop the recordset into a string with commas like: STRING = "firstcol,secondcol,thirdcol" maybe i dont need an array but just a string and use for next to go through it? Going to have to use a split() I'm sure. – easleyfixed Apr 01 '22 at 18:45
  • There are lots of ways to skin this but none of them will be a one-liner. Think I've answered the original question, but good luck anyway. – user692942 Apr 01 '22 at 18:47
  • 1
    I think I will credit you with a solve on this, as the questions of is there a single dimension getrows is a no, and there is no dimension dropper function, and you provided more info for others on this path, so thanks for chiming in. – easleyfixed Apr 01 '22 at 18:49