add to multidimensional array

dcass
dcass used Ask the Experts™
on
I need to add one array to another (or add them both to a new one, but MSDN says redim is not needed but I get an error when I try to add to the next available index.
I tried dim and redim - still error - subscript out of range on arrResultsx(x,ctrx).  I also tried without the internal x loop.
SQL = "SELECT * FROM T1News where ..."
      SQL_Rs.open SQL,SQL_Cn,3
       	IF NOT(SQL_Rs.EOF) THEN
			arrResultsx = SQL_Rs.getrows
		    ctrx = ubound(arrResultsx,2)
		END IF
      	SQL_Rs.close
ctrx2=0
      SQL = "SELECT * FROM T1News where ..."
      SQL_Rs.open SQL,SQL_Cn,3
       	IF NOT(SQL_Rs.EOF) THEN
			arrResultsx2 = SQL_Rs.getrows
		    ctrx2 = ubound(arrResultsx2,2)
		END IF
      	SQL_Rs.close
      	
    ctrx = ctrx + 1
'    ctr = ctrx+ctrx2
'    ReDim arrResultsx(ctr)
'    %><%=arrResultsx(x,ctr)%><Br><%
    if ctrx2 > 0 then      ' if anything in 2nd array, then proceed
      for i = 0 to ctrx2
        for x = 0 to 30
'          redim preserve arrResultsx(x,ctrx)
          arrResultsx(x,ctrx) = arrResultsx2(x,i) ' also tried arrResultsx(ctrx)=arrResultsx2(i)
          ctrx = ctrx + 1
        next
      next
    end if

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
If the tables are same then why don't you combine both the sql statement using joins/union and loop through single recordset?
Commented:
Sorry - got it (after 2 hours) and will post as the examples don't work

<%
ctrx=0 
      SQL = "SELECT * FROM yourfile"
      SQL_Rs.open SQL,SQL_Cn,3
       	IF NOT(SQL_Rs.EOF) THEN
			arrResultsx2 = SQL_Rs.getrows
		    ctrx2 = ubound(arrResultsx2,2)
		END IF
      	SQL_Rs.close
ctrx2=0
      SQL = "SELECT * FROM anotherfile"
      SQL_Rs.open SQL,SQL_Cn,3
       	IF NOT(SQL_Rs.EOF) THEN
			arrResultsx = SQL_Rs.getrows
		    ctrx = ubound(arrResultsx,2)
		END IF
      	SQL_Rs.close
      	
    ctrN = cdbl(ctrx+ctrx2)
    Dim arrResultsN()
    ReDim arrResultsN(10,ctrN)

    if ctrx > 0 then
      for i = 0 to ctrx
       for x = 0 to 10
        arrResultsN(x,i) = arrResultsx(x,i)
       next
      next
    end if
    ctrx=ctrx+1
    if ctrx2 > 0 then
      for i = 0 to ctrx2
       for x = 0 to 10
        arrResultsN(x,ctrx) = arrResultsx2(x,i)
        ctrx=ctrx+1
       next
      next
    end if
   ctrx = ctrx-1
 %>
<table cellspacing="0" cellpadding="0" width="975" bgcolor="#ffffff" align="center" style="margin-top:-5;">
    <tbody>
        <tr>
                        <td valign="top" width="160" align=center><!-- #include virtual="rightlinks.asp" --></td>
                        <td valign="top" width="680" align=left><br>&nbsp;
						<img border="0" src="images/NewsFeed.jpg" width="269" height="40"><hr>
						<table align=left cellpadding=4 width=668><tr><td>
						<% 
						if isarray(arrresultsx) then
						 for i = 0 to ctrx
						  sdate = arrResultsx(5,i) 
etc.

Open in new window

Top Expert 2012

Commented:
>>but I get an error when I try to add to the next available index.<<
From MSDN:

Preserve
Preserves the data in an existing array when you change the size of the last dimension.
Top Expert 2012

Commented:
Incidentally looping like that has to be a dog performance-wise.  Not to mention the fact you have to maintain that mess of a code.

You really need to look into the Recordset method GetRows, this will load a resultset into an array directly.

Author

Commented:
Solution for defining multidimensional variable array and loading and adding to an array!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial