Populating an Array

Results 1 to 7 of 7

Thread: Populating an Array

  1. #1
    Hank L. Guest

    Default Populating an Array

    Since I failed at my atempt can someone please tell me the syntax for making a loop that would create an array with vaules from a database. If that is too much, a link to a good tutorial on the subject (which I can find none)<BR><BR>Thanks for any help

  2. #2
    Mark Guest

    Default RE: Populating an Array

    This might give you a good head start. It&#039;s pretty simplistic, you might want to look at Microsoft&#039;s documentation on VBScript as well, or get a good ASP book. Good luck!<BR><BR>&#039;Set up the connection variable<BR>set conn = Server.CreateObject("ADODB.Connection")<BR><BR>&#0 39;Set up the recordset variable<BR>set rs = Server.CreateObject("ADODB.Recordset")<BR><BR>&#03 9;Connect to the database using ODBC connection<BR>conn.open "DSN=sample"<BR><BR>&#039;Set connection properties<BR>set rs.ActiveConnection = conn<BR>rs.CursorType = adOpenDynamic<BR><BR>&#039;Dimension array<BR>dim arrSample(10)<BR><BR>&#039;SQL query string<BR>sSQLstring = "SELECT * FROM Countries ORDER BY CountryName"<BR><BR>&#039;Open a recordset of the results of the query<BR>rs.Open sSQLstring,,,adCmdTable<BR><BR>&#039;Counter for placement in the array<BR>i = 0<BR><BR>&#039;Not at the end of the results and not at the end of the array<BR>while not rs.eof and i &#060;= 10<BR><BR> &#039;Set the array at position i to the value in the<BR> &#039;field "CountryName"<BR> arrSample(i) = rs("CountryName")<BR><BR> &#039;Move on to the next record in the recordset<BR> rs.movenext<BR><BR> &#039;Move on to the next array position<BR> i = i + 1<BR><BR>&#039;End while<BR>wend<BR><BR>&#039;Close the recordset<BR>rs.Close<BR><BR>&#039;Close the database connection<BR>conn.Close

  3. #3
    Join Date
    Dec 1969

    Default *MUCH* better solution!

    Mark&#039;s code works, but its performance will be horrible compared to using the wonderful method that is BUILT IN to ADODB that already does what you want!<BR><BR>Read up on ADODB.RecordSet.GetRows -- in one call you convert the entire Recordset into a two dimensional array. What could be simpler or faster?<BR><BR>

  4. #4
    Join Date
    Dec 1969

    Default Great, but...

    Thanks you that worked very well however now what do I do when I want all records entered in the array.<BR><BR>Is there a way to count the number of records returned by the sql statment so I can use that number?

  5. #5
    Dementions Guest

    Default RE: *MUCH* better solution!

    I did some reading on the subject and it seems a very good soultion to my problem what I still wnat to konw is what is with dementions of the array? does that refer to the colum? And if not how do I refer to another colum... or do I just have to create another array with a different name?

  6. #6
    Join Date
    Dec 1969

    Default Dimensions of array from GetRows...

    Good questions...maybe I&#039;ll turn this into an FAQ!<BR><BR>So let&#039;s say you do this:<BR><BR>&#060;%<BR>... you have a connection object ...<BR>Set RS = Conn.Execute( yourSQLquery )<BR>records = RS.GetRows<BR>%&#062;<BR><BR>So now you have this two-dimensional array ("records" in my example). What do you do with it?<BR><BR>Well, to find any given cell in the array, you address it thus:<BR><BR>&#060;%<BR>cell = records( columnNumber, recordNumber )<BR>%&#062;<BR><BR>[ columnNumber and fieldNumber being synonyms ]<BR><BR>Do you see the ordering? You specify the column/field number *first* and *then* the record/row number.<BR><BR>And how do you *know* how many columns or records you have in the array? Dirt easy:<BR><BR>&#060;%<BR>maxColumnNumber = UBound( records, 1 )<BR>maxRowNumber = UBound( records, 2 )<BR>%&#062;<BR><BR>Look up "UBound" in the VBS manual if you don&#039;t understand that.<BR><BR>So, finally, how could you write all the fields in all the records to the screen, with the help of GetRows?<BR><BR>Thus:<BR><BR>&#060;%<BR>... you have a connection object ...<BR>Set RS = Conn.Execute( yourSQLquery )<BR>records = RS.GetRows<BR><BR>maxColumnNumber = UBound( records, 1 )<BR>maxRowNumber = UBound( records, 2 )<BR><BR>Response.Write "&#060;TABLE Border=1&#062;" & vbNewLine<BR>For rowNum = 0 To maxRowNumber<BR>&nbsp; &nbsp; Response.Write "&#060;TR&#062;" & vbNewLine<BR>&nbsp; &nbsp; For colNum = 0 To maxColumnNumber<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.Write " &#060;TD&#062;" & records(colNum,rowNum) & "&#060;/TD&#062;" <BR>&nbsp; &nbsp; Next<BR>&nbsp; &nbsp; Response.Write "&#060;/TR&#062;" & vbNewLine<BR>Next<BR>Response.Write "&#060;/TABLE&#062;" & vbNewLine<BR>%&#062;<BR><BR>Hokay?<BR><BR>

  7. #7
    Join Date
    Dec 1969

    Default my 0.1$ worth

    BW,<BR><BR>This should be an faq, I found back when i learned to use getrows, i couldn&#039;t find something simple to explain it, i wish that would have been posted 6 months ago.<BR><BR>(but then again, i only scored a 5 :) (inside joke)<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