I'm trying to do a loop of SQL upda

[cjkenworthy]

I'm trying to do a loop of SQL updates. I've got three arrays with values corresponding values in which make up record update values:

TERMNOarray(value1), PRICEarray(value1), NGNarray(value1)
TERMNOarray(value2), PRICEarray(value2), NGNarray(value2)
etc..

and want to place these values into an SQL database using the following code:

dim I
dim size

size = Ubound(NGNarray)

set rstUpdate_NGN_termno = Server.CreateObject("ADODB.Recordset&quot
rstUpdate_NGN_termno.ActiveConnection = MM_UNCL_telecom_STRING

for I=0 to size

rstUpdate_NGN_termno.Source = "SELECT * FROM NGN WHERE NGN_number = '" & NGNarray(I) & "'"

rstUpdate_NGN_termno("termination_no&quot = TERMNOarray(I)
rstUpdate_NGN_termno("sale_price&quot = PRICEarray(I)
rstUpdate_NGN_termnpen()

next

Set rstUpdate_NGN_termno.ActiveConnection = Nothing
rstUpdate_NGN_termno.Close

%>

So basically what I want is the SELECT to pull up NGNarray(I), then sets its price value to PRICEarray(I), and term_no value to TERMNOarray(I).

This doesn't work as firstly ASP tells me "The operation requested by the application is not allowed if the object is closed. " so My code is wrong somewhere (help?!). But even when I can get it working it doesn't update all records, only the first one in the array.

I really need some help. thanks.

Chris

 

[rac2]

Don't you need to open the recordset before you try to use it?

Then you need to update the database with the values you assigned

And if the values in NGN_number and NGNarray are actually numbers then you do not use single quote marks around the value.

And just to be clear about it you might want to close the recordset after using it.

for I=0 to size    
        
rstUpdate_NGN_termno.Source = "SELECT * FROM NGN WHERE NGN_number = " & NGNarray(I)

rstUpdate_NGN_termno.Open()        

rstUpdate_NGN_termno("termination_no") = TERMNOarray(I)
rstUpdate_NGN_termno("sale_price") = PRICEarray(I)

rstUpdate_NGN_termno.Update()        

rstUpdate_NGN_termno.Close()

next