adding to session array

[seddies]

Hi,

I am inserting into a database by looping over some excel spreadsheets and then inserting the results into a 2d array using getrows, everyhthing is good to here,

I store this in a session variable which I then insert into the database.

problem is in my loop the session variable only gets the first sheets data...

I have tried to redim my session array which it does not like, I have also tried converting to a local array and then reinstating that array as my session array.

Sorry about the big chunk of code but I have marked where my session var is and hopefully someone has tried to do something like this before.


' -- loop over the sheets

For Each Sheet In colSheets
' -- Create a Recordset Object
Dim Conn
Dim Rs
Dim SQL
'Conn.Open
Set Conn = Server.CreateObject("ADODB.Connection&quot
Set Rs = Server.CreateObject("ADODB.Recordset&quot

Conn.Open "DefaultDir=" & path & File & ";Driver={Microsoft Excel Driver (*.xls)};"

Response.write &quot;<strong>&quot; & Sheet.Name & &quot;</strong><p></p>&quot;

SQL = &quot;SELECT * FROM [&quot; & Sheet.Name & &quot;$] &quot; &_
&quot; Where QcNum IS NOT Null &quot;


rs.Open SQL, Conn, 1,3



alldata = rs.GetRows


' -- Set a session variable in order to use the array later to import

Session(&quot;ImpData&quot = alldata <--------------THATS THE ONE I NEED TO ADD TO




' -- Output the excel data so the user can see the info is correct

response.write &quot;<table border='1'><tr>&quot; & vbcrlf
'Put Headings On The Table of Field Names

for each column in rs.fields
response.write &quot;<td><b>&quot; & column.name & &quot;</B></TD>&quot; & vbcrlf
next
response.write &quot;</tr>&quot; & vbcrlf

' grab all the records


numcols=ubound(alldata,1)
numrows=ubound(alldata,2)

FOR rowcounter= 0 TO numrows
response.write &quot;<tr>&quot; & vbcrlf
FOR colcounter=0 to numcols
thisfield=alldata(colcounter,rowcounter)
if isnull(thisfield) then
thisfield=shownull
end if
if trim(thisfield)=&quot;&quot; then
thisfield=showblank
end if
response.write &quot;<td valign=top>&quot;
response.write thisfield

response.write &quot;</td>&quot; & vbcrlf
NEXT
response.write &quot;</tr>&quot; & vbcrlf
NEXT
response.write &quot;</table><p></p>&quot;
next

%>

<hr>

<form action=&quot;import.asp&quot; method=&quot;post&quot;><input type=&quot;submit&quot; name=&quot;btn&quot; value=&quot;Import&quot;></form>
</body>
</html>

<% End If %>
<% If request.form(&quot;btn&quot = &quot;Import&quot; then
Dim ImpConn
Dim ImpRs

Set ImpConn = Server.CreateObject(&quot;ADODB.Connection&quot
Set ImpRs = Server.CreateObject(&quot;ADODB.Recordset&quot
ImpConn.Open Application(&quot;GlobalQCSQL_ConnectionString&quot

ImpRs.Open &quot;Tests&quot;, ImpConn, 0,3

' -- The actual importing gets done

For i = 0 to ubound(Session(&quot;ImpData&quot,2)
ImpRs.AddNew
ImpRs(&quot;QcNum&quot = Session(&quot;ImpData&quot(0,i)
ImpRs(&quot;Date&quot = Session(&quot;ImpData&quot(1,i)
ImpRs(&quot;Time&quot = Session(&quot;ImpData&quot(2,i)
ImpRs(&quot;Measurement1&quot = Session(&quot;ImpData&quot(3,i)
ImpRs(&quot;Measurement2&quot = Session(&quot;ImpData&quot(4,i)
ImpRs(&quot;YesNo&quot = Session(&quot;ImpData&quot(5,i)
ImpRs(&quot;MachineID&quot = Session(&quot;ImpData&quot(6,i)
ImpRs(&quot;Cavity&quot = Session(&quot;ImpData&quot(7,i)
ImpRs(&quot;Gauge&quot = Session(&quot;ImpData&quot(8,i)
ImpRs(&quot;Comment&quot = Session(&quot;ImpData&quot(9,i)
ImpRs.Update
Next

Response.write &quot;Bingo, Your import went well take the week off with pay&quot;
%>