Update sql statement returning incorrect results

I am reading in a group of results from a user form when the form is submitted. The purpose of the code is to split the group of results into single records and then update the records in a SQL server database. Most of the code appears to work but the when looking at the sql statement produced it is only updating one record from the group of results.

This is the code that I am using in the web page:

Variable 'school' is the group of results from the user form.


Dim schoolEntry, schoolvalues, singlevalues, basic, sql, i, numupdated
schoolEntry=split(school,"*")

'for error checking only
response.write (school&"<br>")

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
basic=split(schoolvalues,",")
'for error checking only
response.write schoolvalues
next

for each singlevalues in basic
sql="UPDATE sss_results SET "
i=1
do while i >= 0
select case i
case 0
sql=sql & "WHERE school_name=" & SQLFormat(basic(i)) & ""
case 1
sql=sql & "save_priority=" & basic(i) & " "
end select
i = i - 1
loop
'for error checking only
response.write ("<br>"&sql)
conn.Execute sql
next


This is what is displayed with response.write (school&"<br>"):

*Biggenden State School, 1, *Mungar State School, 2

This is what is displayed with response.write schoolvalues:

Biggenden State School,1,Mungar State School,2

This is the result of the sql statement, but it is only 'reading' one record of the array:


UPDATE sss_results SET save_priority=2 WHERE school_name=Mungar State School
UPDATE sss_results SET save_priority=2 WHERE school_name=Mungar State School


I thought the result of the sql statement should be something like this:


UPDATE sss_results SET save_priority=2 WHERE school_name=Mungar State School
UPDATE sss_results SET save_priority=1 WHERE school_name=Biggenden State School


I have looked at this from every side and cannot see what part of the code is causing the problem.

Any help is greatly appreciated.

That is because you are using a single variable named basic. Everytime the for-each loop iterates, it overwrites the previous value of basic, thus the final value of it is the last set in the schoolvalues string which is the Mungar State School set in this case.

Why not execute the SQL query inside the first for-each loop and get rid of the 2nd for-each loop? And you don't need the do-while loop and select case either.

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
basic=split(schoolvalues,",")
'for error checking only
response.write schoolvalues

'execute SQL
sql="UPDATE sss_results SET save_priority=" & basic(1) & " WHERE school_name=" & SQLFormat(basic(0)) & ""
'for error checking only
response.write ("<br>"&sql)
conn.Execute sql
next

Thanks glenngv,

I was doing more than needed.

I tried the code and got this error:


*Biggenden State School, 1, *Mungar State School, 2

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '[number: 1]'

/bq/sss_insertpriorityrecord.asp, line 80


I had to add the trim() function to remove the ',' after the last set in the schoolvalues string and it fixed the error.


for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
if trim(schoolvalues) <> "" then
basic=split(schoolvalues,",")
sql="UPDATE sss_results SET save_priority=" & basic(1) & " WHERE school_name='" & SQLFormat(basic(0)) & "'"
conn.Execute sql
end if
next

Dear,

This might be the error of the total of the array. ( eg:basic(1) )
Try to check the total value of i.