Naughty loop

[cjkenworthy]

I'm trying to do an update loop in ASP using the following code:

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

for I=0 to size

dim strSQL
strSQL = "UPDATE NGN SET termination_no = '" & TERMNOarray(I) & "', sale_price = " & PRICEarray(I) & " WHERE NGN_number = '" & NGNarray(I) & "'"

objRS.Open strSQL

next

Set objRS = Nothing
objRS.Close


The values going into the array are correct, I'm 100% sure of this, as I can output them. The problem is that this does update the database but the loop only appears to work once, it only updates one record so if i have:

Record 1:
TERMNOarray(value1), PRICEarray(value1), NGNarray(value1)
Record 2:
TERMNOarray(value2), PRICEarray(value2), NGNarray(value2)
etc..

It only does Record 1.


Can anyone advise? Thanks.

 

[Jonax]

dim strSQL
for I = 0 to ubound(NGNarray,1)
        
strSQL = "UPDATE NGN SET termination_no = '" & TERMNOarray(I) & "', sale_price = " & PRICEarray(I) & " WHERE NGN_number = '" & NGNarray(I) & "'"

objRS.Open strSQL
objRS.Close
Set objRS = Nothing 
                    
next

.. I hope this helps, but it's not a very effecient way to do this... This is not a bug - it's an undocumented feature...
;-)

 

[Tarwn]

Try using .Execute rather than opening and closing the object in every loop. This should give you better performance and will require less communication between the web server and db.
-Tarwn "The problem with a kludge is eventually you're going to have to back and do it right." - Programmers Saying (The Wiz Biz - Rick Cook)
"Your a geek!" - My Girlfriends saying

 

[cjkenworthy]

I've tried Jonax's code, but it still only updates one record. The code is below.

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

for I=0 to Ubound(NGNarray, 1)

dim strSQL
strSQL = "UPDATE NGN SET termination_no = '" & TERMNOarray(I) & "', sale_price = " & PRICEarray(I) & " WHERE NGN_number = '" & NGNarray(I) & "'"
objRS.Open strSQL
objRS.Close
Set objRS = Nothing

next

still not working. Any ideas?

(Also i get told "The operation requested by the application is not allowed if the object is closed." if i leave the objRS.Close in)

 

[Tarwn]

Since you are setting your recordset object = nothing at the end of the first loop then the next loops can't execute because you no longer have a recordset. using the code above, try:

dim objRS
set objRS = Server.CreateObject("ADODB.Recordset")    
objRS.ActiveConnection = MM_UNCL_telecom_STRING
                    
for I=0 to Ubound(NGNarray, 1)    
        
dim strSQL
strSQL = "UPDATE NGN SET termination_no = '" & TERMNOarray(I) & "', sale_price = " & PRICEarray(I) & " WHERE NGN_number = '" & NGNarray(I) & "'"
objRS.Open strSQL
objRS.Close    

next

'only get rid of the object when your are completely done with it
Set objRS = Nothing

-Tarwn "The problem with a kludge is eventually you're going to have to back and do it right." - Programmers Saying (The Wiz Biz - Rick Cook)
"Your a geek!" - My Girlfriends saying

 

[cjkenworthy]

copied it exaclty and got:

ADODB.Recordset error '800a0e78'

The operation requested by the application is not allowed if the object is closed.

/telecom/sale_4a.asp, line 61

it also still only updates one record.

i'm stumped?!

 

[Tarwn]

Here try this instead:

dim conn, strSQL
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_UNCL_telecom_STRING

for I=0 to Ubound(NGNarray, 1)    

strSQL = "UPDATE NGN SET termination_no = '" & TERMNOarray(I) & "', sale_price = " & PRICEarray(I) & " WHERE NGN_number = '" & NGNarray(I) & "'"

conn.execute strSQL
next

Set conn = Nothing

This uses the connection object to execute the SQL instead of the recordset. A reference can be found here:

http://www.w3schools.com/ado/ado_ref_connection.asp

-Tarwn "The problem with a kludge is eventually you're going to have to back and do it right." - Programmers Saying (The Wiz Biz - Rick Cook)
"Your a geek!" - My Girlfriends saying

 

[cjkenworthy]

(btw I appreciate you help!)

ok, it process it fine without any errors (which is great)

but it still only does the 1 record. I don't see why.

 

[Gorkem]

The only problem this points to is your Array.. it only holds one value.. Perhaps you have your values as comma delimited. If so, you may want to use the SPLIT(string) to turn it into a true array.

Cheers,

G.

 

[cjkenworthy]

I have got the arrray functioning correctly and have seperated out the strings using:

NGNarray = split(strNGN,",&quot
TERMNOarray = split(strTERMNO,",&quot
PRICEarray = split(strPRICE,",&quot

Also, i know that the values in the array are fine, as if i do a response.write in a 'for loop' the values come out perfectly.

So it must be the update statement!

really stuck on this now.

 

[ISPrincess]

I think you might be correct about it being the execute / update statement. I vaguelly remember something about that.

Try and change it around and make sure the connection and recordset cursor location is client side. etc

I am doing something similar but in my case, I am updating a recordset. You could then issue an update back to the table when you are done with your loop.

I am not very familiar with the particulars on this tho - check sql forum ir that is what you are using.

 

[Tarwn]

Try printing out your sql statements in the loop just to make sure it is the driver. After printing them out copy the second or third one into a query in your db and see if it executes alright.
-Tarwn If your happy and you know it...

 

[cjkenworthy]

I think I've found the problem:

Here is an output of the strSQL within the loop, wher i've just put in two NGN records:

UPDATE NGN SET termination_no = '11111111111', sale_price = 50 WHERE NGN_number = '08456223231'

UPDATE NGN SET termination_no = ' 22222222222', sale_price = 75 WHERE NGN_number = ' 08704567890'

As you can see the second line, ASP has put some spaces in - in the termination_no '_22222222222' and the NGN_number '_08704567890'. Any idea how to get rid of these spaces being put in?

Same code as before.

 

[ISPrincess]

You could try:

strSQL = "UPDATE NGN SET termination_no = '" & LTrim(TERMNOarray(I)) & "', sale_price = " & PRICEarray(I) & " WHERE NGN_number = '" & NGNarray(I) & "'"

 

[Tarwn]

When you pass multiple values through a form with the same name they are not delimited with commas. The browser actually delimits them with a comma space to be grammatically correct. I generally don't split on comma anymore, I split like this:

NGNarray = split(strNGN,", ")

Note the space inside the string to split on. Sorry I didn't remember this earlier,
-Tarwn "If you eat a live toad first thing in the morning, nothing worse will happen all day long." - California saying
"To you or the toad" - Niven's restatement of California saying
"-well most of the time anyway..." - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri

 

[cjkenworthy]

thanks everyone for your help. I cracked it by assigning the array element(I) to a temporary variable, trimming it using Trim. I then did:

dim string

string1 = array(I)

string1 = "'" & Trim(string1) & "'"

Then i pasted this into the query:

strSQL = "UPDATE table WHERE attribute = " & string & ""

for simplicity and debugging I did this to track the value as it got passwed around.

thanks again!