update problem

[cjkenworthy]

I have 3 arrays with corresponding values in:
NGNarray(value1), PRICEarray(value1), TERMNOarray(value1)
NGNarray(value2), PRICEarray(value2), TERMNOarray(value2)
etc.
(Each represents new values for a record)

I have an SQL update statement into which these values are placed, in turn these are supposed to update each record in the database accordingly:


for I=0 to Ubound(NGNarray)

"UPDATE NGN SET termination_no = '" & TERMNOarray(I) & "', sale_price = " & PRICEarray(I) & " WHERE sold = 1 and held = 1 and held_by = '" & varCA & "' and NGN_number IN (SELECT s.NGN_Number FROM Sale s, NGN n WHERE s.NGN_number = n.NGN_number and s.sale_id = '" & varsale_id & "' and s.NGN_number = '" & NGNarray(I) & "')"

next

HOWEVER, the problem is that it is only updating the first record in the database which matches the criteria. I have executed the query manually and it works perfectly (conditions are met, sub query returns correct values), so it is definately a loop problem or something.

I have also checked what values are in the array for each loop occurance and this is correct e.g:

TERMNOarray = 01111111111
PRICEarray = 35
NGNarray = 08451254423

TERMNOarray = 02222222222
PRICEarray = 75
NGNarray = 08702112122

Can anyone help?

Thanks, Chris.

 

[patrician]

This might be a silly question, but at what size did you dim the array?

 

[cjkenworthy]

I didnt specify a size, just declared as:

dim strNGN
dim NGNarray
dim strTERMNO
dim TERMNOarray
dim strPRICE
dim PRICEarray

Then, read the values in to the strings, split them then put them in the arrays. e.g:

NGNarray = split(strNGN,",&quot

The arrays are dynamic and seem to work because when i debug and output their contents it is working fine (see previous post). The main problem still persists though in getting them into the query loop.

Any ideas?