GetRows() with Column Names

Results 1 to 4 of 4

Thread: GetRows() with Column Names

  1. #1
    Join Date
    Dec 1969

    Default GetRows() with Column Names

    I am wondering if anyone knows of a way to pull the column names with all the data when using the GetRows() function<BR><BR>I know I could do it by getting the array of recordset data, and then inserting an array of column names, but I am curious if there is a faster/easier way to do it.<BR><BR>Thanks <BR> <BR>

  2. #2
    SPG Guest

    Default In Short: Nope

    At least not to my knowledge.<BR><BR>Reason is:<BR>.getRows exists to shed the unnecessary burden of a RecordSet structure. If you were careful in your "Select" statement (the reasoning goes), then you already know what columns you requested as well as what order they come in ("foo.intID as ItemNumber, bar.strName as ItemName, ...") . And if you&#039re that advanced, there&#039s the possibility that your query left some columns unnamed ("count(foo.intID)"), hence that information ("The Column With No Name") won&#039t help you too much anyway. So let&#039s just shed this excess and useless data known as "Column Names" and get right to the content. Right?<BR><BR>I&#039d be interested to find out why you need this information -- there may be an application-specific workaround, depending on what you&#039re planning on doing with the column names.

  3. #3
    Join Date
    Dec 1969

    Default RE: GetRows() with Column Names

    There are a couple of ways (perhaps more, but these come to mind):<BR><BR>1) I don&#039t know if you&#039re using stored procs or not, but basically you could do this:<BR><BR> select &#039custid&#039, &#039custname&#039, &#039custaddr&#039; select custid, custname, custaddr from custs;<BR><BR>The first getRows would return the field names (which you hardcoded into the query). you would then do a .nextrecordset to get the data.<BR><BR>2) You could do a union query:<BR><BR> select &#039custid&#039, &#039custname&#039, &#039custaddr&#039 union select custid, custname, custaddr from custs<BR><BR>and have it all in one recordset, as long as ALL of the fields are strings (you can convert the numbers to strings in the query, if necessary.<BR><BR>I know, neither are a GREAT general purpose solution, but I don&#039t know much about your app, so perhaps this will help.

  4. #4
    Join Date
    Dec 1969

    Default ...but if you really *must*...

    SPG&#039s answer is 100% on the money.<BR><BR>However...<BR><BR>If, for example, you wanted to pass the names on to a subroutine or perhaps store them in a session variable...<BR><BR>Well, why not just create an array that has *only* the column names and pass it along in parallel with the GetRows result?<BR><BR>You *can* access the column names from the RS so long as you do it *before* you do GetRows. So something like this:<BR><BR>&#060;%<BR>... I assume you have already executed the query and have the RS in hand...<BR>HiColumnNum = RS.Fields.Count - 1<BR>Dim columnNames( )<BR>ReDim columnNames( HiColumnNum )<BR>For cnum = 0 To HiColumnNum<BR> &nbsp; columnNames = RS.Fields(cnum).Name<BR>Next<BR>allRows = RS.GetRows<BR>...<BR>%&#062;<BR><BR>If you *really* wanted to, you could even do<BR>&#060;%<BR>ReDim Preserve allRows( UBound(allRows,1), UBound(allRows,2)+1 )<BR>%&#062;<BR>and then copy the column names into the *LAST* row of the GetRows array. But I think it is less trouble to simply pass around the pair of arrays.<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