Multiple split(ing) of an array

[JustAsking]

OK, I am having difficulty understanding how I need to split an array I 've got in my code. This is what I've got:

1. I am performing, school = request.form("school") from a form which will contain an unknown number of values.

2. I then create an array from this string as follows: schoolEntry = split("school","*").

3. Once the string has been split into the array 'schoolEntry' the array will contain the following:

schoolEntry (0) = school, priority01, priority02, priority03, priority04, priority05, priority06, reason01, reason02, reason03, reason04, reason05, reason06,
schoolEntry (1) = school, priority01, priority02, priority03, priority04, priority05, priority06, reason01, reason02, reason03, reason04, reason05, reason06,
.
.

4. 'school' will be of a string type (e.g. Alexandra Hills School), and the priority and reason values will be a single number (1).

5. I have an SQL database setup with the field names the same as the array 'schoolEntry'. e.g. Database fields are, school, priority01, priority02, priority03, priority04, priority05, priority06, reason01, reason02, reason03, reason04, reason05, reason06.

6. I then need to breakdown the array 'schoolEntry' and create seperate variables (school, priority01, priority02, priority03, priority04, priority05, priority06, reason01, reason02, reason03, reason04, reason05, reason06), and insert the variable values into the database where the field 'school' in the database record equals the variable school in the array. For example:

school = "Alexandra Hils School"
priority01 = 3
priority02 = 1
.
.
reason01 = 2
reason02 = 3


I do not know how to code item 6, I am able to get up till item 6 working. If I am on the right track or there is another way to do this, I would appreciate some help.

Thanks.

[raf]

If i understand it correct, then all values from "school" contain a fixed number of values after the split (like schoolEntry (0)contains 13)

In this case, you can split these values, with the ',' as separator and then dynamically build the sql statement to insert the record for that school. (but it would be a better idea to use another separator, since schoolnames can contain ",". Maybe use "|" or so --> values need to be stored in the array using this seperator).

It doesn't matter how many elements school has, since you can use a for each loop on the schoolEntry array


schoolEntry=split("school","*")
dim schoolvalues, basic, sql, i, numinserted
for each schoolvalues in schoolEntry
sql="INSERT INTO table (school, priority01, priority02, priority03, priority04, priority05, priority06, reason01, reason02, reason03, reason04, reason05, reason06) VALUES ("
basic=split(schoolvalues,",")
i=0
do while i <= 12
if i = 0 then
sql=sql & "'" & basic(i) & "',"
else
sql=sql & "," & basic(i)
end if
i = i + 1
loop
sql = sql & ")"
numinserted = 0
con.Execute sql, numinserted
if numinserted = 1 then
response.write("School" & basic(0) & " inserted")
else
response.write("School " & basic(0) & " not inserted")
end if
next

[JustAsking]

If i understand it correct, then all values from "school" contain a fixed number of values after the split (like schoolEntry (0)contains 13)

Yeah that;s right, if the user does not select a value for say "priority01 and reason01" I default the value to 0. So the array will always contain 13.

What I forgot to mention was I need to UPDATE the values in the database based on the school name rather thn INSERT. In the database table there will already be a record with a school name "Alexandra Hills School", this record containd fields: priority01, priority02, priority03, priority04, priority05, priority06, reason01, reason02, reason03, reason04, reason05, reason06 which are SET to 0. I need to update these fields using the vaues from the array.

sql = "UPDATE table SET priority01 = priority01(array value), priority02 = priority02(array value), priority03 = priority03(array value), priority04 = priority04(array value), priority05 = priority05(array value), priority06 = priority06(array value), reason01 = reason01(array value), reason02 = reason02(array value), reason03 = reason03(array value), reason04 = reason04(array value), reason05 = reason05(array value), reason06 = reason06(array value) WHERE school = school(array value)"

In summary, the code will need to UPDATE the table where the school in the table matches the school in the array.

Sorry for not clarifying this originally.

Thanks.

[raf]

I guess you then need something like

schoolEntry=split("school","*")
dim schoolvalues, basic, sql, i, numupdated
for each schoolvalues in schoolEntry
sql="UPDATE table SET " 'DONT FORGET THE SPACE AT THE END
basic=split(schoolvalues,",")
i=12
do while i >= 0
select case i
case 0
sql=sql & "WHERE school='" & resplace(basic(i),"'","''") & "'"
case 12
sql=sql & "reason06=" & basic(i) & ", " 'space at end !!!
etc --> all 12 variabels until you get at
case 1
sql=sql & "priority01=" & basic(i) & " " 'space at end !!!
end select
i = i -1
loop

numupdated = 0
con.Execute sql, numupdated
if numupdated = 1 then
response.write("School" & basic(0) & " updated")
else
response.write("Problem. For " & basic(0) & " there were " & numupdated &" records updated")
end if
next

[JustAsking]

raf, thanks for the help and quick response. I'll give it a try, and if I have any problems which I cannot sort out, I'll know where to come.

Cheers.. :thumbsup:

[JustAsking]

This is the error I am getting when trying to run the code.

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '12'

/bq/insertpriorityrecord.asp, line 71



schoolEntry=split("school","*")
dim schoolvalues, basic, sql, i, numupdated
for each schoolvalues in schoolEntry
sql="UPDATE NF_Results SET " 'DONT FORGET THE SPACE AT THE END
basic=split(schoolvalues,",")
i=12
do while i >= 0
select case i
case 0
sql=sql & "WHERE school_name='" & replace(basic(i),"'","''") & "'"
case 12
sql=sql & "edreason_project6=" & basic(i) & ", " this is line 71
case 11
sql=sql & "edreason_project5=" & basic(i) & ", " 'space at end !!!
case 10
sql=sql & "edreason_project4=" & basic(i) & ", " 'space at end !!!
case 9
sql=sql & "edreason_project3=" & basic(i) & ", " 'space at end !!!
case 8
sql=sql & "edreason_project2=" & basic(i) & ", " 'space at end !!!
case 7
sql=sql & "edreason_project1=" & basic(i) & ", " 'space at end !!!
case 6
sql=sql & "edpriority_project6=" & basic(i) & ", " 'space at end !!!
case 5
sql=sql & "edpriority_project5=" & basic(i) & ", " 'space at end !!!
case 4
sql=sql & "edpriority_project4=" & basic(i) & ", " 'space at end !!!
case 3
sql=sql & "edpriority_project3=" & basic(i) & ", " 'space at end !!!
case 2
sql=sql & "edpriority_project2=" & basic(i) & ", " 'space at end !!!
case 1
sql=sql & "edpriority_project1=" & basic(i) & " " 'space at end !!!
end select
i = i -1
loop
numupdated = 0
conn.Execute sql, numupdated
if numupdated = 1 then
response.write("School" & basic(0) & " updated")
else
response.write("Problem. For " & basic(0) & " there were " & numupdated &" records updated")
end if
next


This is the contents of 'school' before it is split into an array:

Alexandra Hills State School, 1, 2, 3, 4, 5, 6, 1, 1, 1, 6, 3, 4, *, Birkdale South State School, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, *, Hilliard State School, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, *, Wellington Point State School, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, *

[raf]

Hmm.

In any case, you should remove the "," in front of the schoolnames in your initial array+the spaces behind the "," --> or beter still, choose another separator like "|"

About the error. Best print all values to look at them in your browser:

d=0
for each schoolvalues in schoolEntry
d=d+1
response.write("School " & d & " : " & schoolvalues & "<br />")
basic=split(schoolvalues,",")
i=12
do while i >= 0
response.write(basic(i) & ", ")
loop
response.write("<br /><br />"
next

The copy trhe result here.

[raf]

Hmm.

In any case, you should remove the "," in front of the schoolnames in your initial array+the spaces behind the "," --> or beter still, choose another separator like "|"

About the error. Best print all values to look at them in your browser:

d=0
for each schoolvalues in schoolEntry
d=d+1
response.write("School " & d & " : " & schoolvalues & "<br />")
basic=split(schoolvalues,",")
i=12
do while i >= 0
response.write(basic(i) & ", ")
loop
response.write("<br /><br />"
next

If you get an error, then take out the 'do loop'. Then copy the result here.

[JustAsking]

The below code will only enter the last record of the array into the database. So if i have an array that looks like:

Alexandra Hills State School,1,2,3,4,5,6,1,1,1,6,3,4,Wellington Point State School,7,8,9,10,11,12,1,1,2,1,1,1,

only Wellington Point State School,7,8,9,10,11,12,1,1,2,1,1,1, is updated in the database. The code isn't producing the "Subscript out of range: '12'" error so I think I'm on the right track. But what am I doing wrong?


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

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
basic=split(schoolvalues,",")
next

for each singlevalues in basic
sql="UPDATE NF_Results SET "
i=12
do while i >= 0
select case i
case 0
sql=sql & "WHERE school_name='" & replace(basic(i),"'","''") & "'"
case 12
sql=sql & "edreason_project6=" & basic(i) & ", "
case 11
sql=sql & "edreason_project5=" & basic(i) & ", "
case 10
sql=sql & "edreason_project4=" & basic(i) & ", "
case 9
sql=sql & "edreason_project3=" & basic(i) & ", "
case 8
sql=sql & "edreason_project2=" & basic(i) & ", "
case 7
sql=sql & "edreason_project1=" & basic(i) & ", "
case 6
sql=sql & "edpriority_project6=" & basic(i) & ", "
case 5
sql=sql & "edpriority_project5=" & basic(i) & ", "
case 4
sql=sql & "edpriority_project4=" & basic(i) & ", "
case 3
sql=sql & "edpriority_project3=" & basic(i) & ", "
case 2
sql=sql & "edpriority_project2=" & basic(i) & ", "
case 1
sql=sql & "edpriority_project1=" & basic(i) & " "
end select
i = i -1
loop
numupdated = 0
conn.Execute sql, numupdated
if numupdated = 1 then
response.write(basic(0) & " updated"&"<br />")
else
response.write("Problem. For " & basic(0) & " there were " & numupdated &" records updated")
end if
next


THIS IS THE OUTPUT OF THE ABOVE CODE:


Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated
Wellington Point State School updated

[raf]

I assume the problem is that you have this for each next loop to split all schoolvalues, but then you don't do anything with the split schoolvalues, so this loop threats all schoolvalues and the values from the last school are stored in the basic-arrayvariabel. Whichn at the end of the loop, will of coarse only contain the values of the last school.

Look at my previous posts. The processing (updating of the reords, should be inside that for each next loop. Like
Dim schoolEntry, schoolvalues, singlevalues, basic, sql, i, numupdated, iLoop
schoolEntry=split(school,"*")

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
basic=split(schoolvalues,",")

sql="UPDATE NF_Results SET "
i=12
do while i >= 0
select case i
case 0
sql=sql & "WHERE school_name='" & replace(basic(i),"'","''") & "'"
case 12
sql=sql & "edreason_project6=" & basic(i) & ", "
case 11
sql=sql & "edreason_project5=" & basic(i) & ", "
case 10
sql=sql & "edreason_project4=" & basic(i) & ", "
case 9
sql=sql & "edreason_project3=" & basic(i) & ", "
case 8
sql=sql & "edreason_project2=" & basic(i) & ", "
case 7
sql=sql & "edreason_project1=" & basic(i) & ", "
case 6
sql=sql & "edpriority_project6=" & basic(i) & ", "
case 5
sql=sql & "edpriority_project5=" & basic(i) & ", "
case 4
sql=sql & "edpriority_project4=" & basic(i) & ", "
case 3
sql=sql & "edpriority_project3=" & basic(i) & ", "
case 2
sql=sql & "edpriority_project2=" & basic(i) & ", "
case 1
sql=sql & "edpriority_project1=" & basic(i) & " "
end select
i = i -1
loop
numupdated = 0
conn.Execute sql, numupdated
if numupdated = 1 then
response.write(basic(0) & " updated"&"<br />")
else
response.write("Problem. For " & basic(0) & " there were " & numupdated &" records updated")
end if
next


So you just need to delete 2 lines, i presume.

[JustAsking]

Sorry guys this thread is getting long, but I'm stuffed to know what is wrong.

I have used the following code:


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

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
basic=split(schoolvalues,",")

sql="UPDATE NF_Results SET "
i=12
do while i >= 0
select case i
case 0
sql=sql & "WHERE school_name='" & replace(basic(i),"'","''") & "'"
case 12
sql=sql & "edreason_project6=" & basic(i) & ", "
case 11
sql=sql & "edreason_project5=" & basic(i) & ", "
case 10
sql=sql & "edreason_project4=" & basic(i) & ", "
case 9
sql=sql & "edreason_project3=" & basic(i) & ", "
case 8
sql=sql & "edreason_project2=" & basic(i) & ", "
case 7
sql=sql & "edreason_project1=" & basic(i) & ", "
case 6
sql=sql & "edpriority_project6=" & basic(i) & ", "
case 5
sql=sql & "edpriority_project5=" & basic(i) & ", "
case 4
sql=sql & "edpriority_project4=" & basic(i) & ", "
case 3
sql=sql & "edpriority_project3=" & basic(i) & ", "
case 2
sql=sql & "edpriority_project2=" & basic(i) & ", "
case 1
sql=sql & "edpriority_project1=" & basic(i) & " "
end select
i = i -1
loop
numupdated = 0
conn.Execute sql, numupdated
if numupdated = 1 then
response.write(basic(0) & " updated"&"<br />")
else
response.write("Problem. For " & basic(0) & " there were " & numupdated &" records updated")
end if
next


and I now once again get the error: Subscript out of range: '12'. Also, the database isn't updated with any values from the array.

sheesh...

I have used the following code to print what the values exactly are I'm getting:


d=0
for each schoolvalues in schoolEntry
d=d+1
response.write("School " & d & " : " & schoolvalues & "<br />")
schoolvalues=replace(schoolvalues,", ",",")
basic=split(schoolvalues,",")
i=12
do while i >= 0
response.write(basic(i) & ", ")
loop
response.write("<br /><br />"
next


and get the following:

School 1 : Wellington State School
1, 2, 3, 4, 5, 6, 1, 1, 2, 1, 1, 1

School 2 : Birkdale State School
8, 9, 10, 11, 12, 13, 2, 1, 1, 1, 3, 3

Nothing looks wrong to me with this output, there are 13 values (0 to 12) for each loop through the array, but something just does not like it. :confused:

[raf]

Hmm. How bizare.
Normally, you should get this output

School 1 : Wellington State School,1, 2, 3, 4, 5, 6, 1, 1, 2, 1, 1, 1
1,1,1,2,1,1,6,5,4,3,2,1

But the i will remain 12 in your loop, no? So it should be timing out with this code. Change that to:

do while i >= 0
response.write(basic(i) & ", ")
i = i-1
loop

Maybe print out school
response.write school

It should look like Wellington State School,1, 2, 3, 4, 5, 6, 1, 1, 2, 1, 1, 1*Birkdale State School,
8, 9, 10, 11, 12, 13, 2, 1, 1, 1, 3, 3

[JustAsking]

This is the output I printed when I use this code: response.write school

printed result

*Atherton State High School, 1, 2, 3, 4, 0, 0, 2, 1, 1, 1, 0, 0, *Wellington Point State School, 5, 6, 7, 0, 0, 0, 2, 3, 1, 0, 0, 0

This is the output printed when I then use this code:
schoolEntry=split(school,"*")

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
response.write schoolvalues
next

printed result

Atherton State High School,1,2,3,4,0,0,2,1,1,1,0,0,Wellington Point State School,5,6,7,0,0,0,2,3,1,0,0,0

Then when I use the following code I get the subscript out of range: '12' error.

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

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
basic=split(schoolvalues,",")

sql="UPDATE NF_Results SET "
i=12
do while i >= 0
select case i
case 0
sql=sql & "WHERE school_name='" & replace(basic(i),"'","''") & "'"
case 12
sql=sql & "edreason_project6=" & basic(i) & ", "
case 11
sql=sql & "edreason_project5=" & basic(i) & ", "
case 10
sql=sql & "edreason_project4=" & basic(i) & ", "
case 9
sql=sql & "edreason_project3=" & basic(i) & ", "
case 8
sql=sql & "edreason_project2=" & basic(i) & ", "
case 7
sql=sql & "edreason_project1=" & basic(i) & ", "
case 6
sql=sql & "edpriority_project6=" & basic(i) & ", "
case 5
sql=sql & "edpriority_project5=" & basic(i) & ", "
case 4
sql=sql & "edpriority_project4=" & basic(i) & ", "
case 3
sql=sql & "edpriority_project3=" & basic(i) & ", "
case 2
sql=sql & "edpriority_project2=" & basic(i) & ", "
case 1
sql=sql & "edpriority_project1=" & basic(i) & " "
end select
i = i -1
loop
numupdated = 0
conn.Execute sql, numupdated
if numupdated = 1 then
response.write(basic(0) & " updated"&"<br />")
else
response.write("Problem. For " & basic(0) & " there were " & numupdated &" records updated")
end if
next

Do the printed results look how they should when I try to update them in the database?

[whammy]

I don't know what this is supposed to be doing, but it won't work:

numupdated = 0
conn.Execute sql, numupdated
if numupdated = 1 then
response.write(basic(0) & " updated"&"<br />")
else
response.write("Problem. For " & basic(0) & " there were " & numupdated &" records updated")
end if

Your "if numupdated = 1" condition will never execute, since you set the variable "numupdated" to 0 immediately before that.

Just something I noticed right off the bat, if you're trying to use that for some kind of error checking.

Pretty sure you're on the right track otherwise, though... what I'd do is Response.Write stuff to your page during each iteration of the loop to see where you're at when the error happens. That will usually guide you to the problem.

[JustAsking]

When I print out the result of sql before it is executed Using this):

response.write sql & "<br />"
conn.Execute sql

I get the following output 13 times (0 to 12 through the loop), the other school entries e.g. Atherton State High School are not being outputed. Any reason why, it seems as though it should?


UPDATE NF_Results SET edreason_project6=0, edreason_project5=0, edreason_project4=0, edreason_project3=1, edreason_project2=3, edreason_project1=2, edpriority_project6=0, edpriority_project5=0, edpriority_project4=0, edpriority_project3=7, edpriority_project2=6, edpriority_project1=5 WHERE school_name='Wellington Point State School'

[whammy]

Yeah, your update statement looks to be extremely wrong. It's usually like this:

UPDATE [tablename] SET somevalue = '" & somevalue & "', somevalue2 = '" & somevalue2 & "' WHERE somethingIMPORTANT = somethingIMPORTANT

First of all, you don't have a WHERE clause in your statement... so it might be a REALLY good thing it didn't run! Otherwise, it would update EVERY RECORD IN YOUR DATABASE, since you don't define any criteria to update by. Not to mention you have the wrong syntax. ;)

That should definitely give you something to go on. :)

[JustAsking]

Yeah, your update statement looks to be extremely wrong. It's usually like this:

UPDATE [tablename] SET somevalue = '" & somevalue & "', somevalue2 = '" & somevalue2 & "' WHERE somethingIMPORTANT = somethingIMPORTANT
huh...

There is nothing wrong with the syntax that is being outputed:

UPDATE NF_Results SET edreason_project6=0, edreason_project5=0, edreason_project4=0, edreason_project3=1, edreason_project2=3, edreason_project1=2, edpriority_project6=0, edpriority_project5=0, edpriority_project4=0, edpriority_project3=7, edpriority_project2=6, edpriority_project1=5 WHERE school_name='Wellington Point State School'


First of all, you don't have a WHERE clause in your statement

This is the code which specifies the WHERE clause:

case 0
sql=sql & "WHERE school_name='" & replace(basic(i),"'","''") & "'"

1. The sql update statement is working, but only for one school record.

2. When I print the result using this code:

Dim schoolEntry, schoolvalues
schoolEntry=split(school,"*")

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
response.write schoolvalues
next


I get what I expect:

Atherton State High School,1,2,3,4,0,0,2,1,1,1,0,0,Wellington Point State School,5,6,7,0,0,0,2,3,1,0,0,0

3. But when I print the result using this code:

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

for each schoolvalues in schoolEntry
schoolvalues=replace(schoolvalues,", ",",")
basic=split(schoolvalues,",")
next

for each singlevalues in basic
response.write singlevalues
next


Some reason when I perform: basic=split(schoolvalues,",")
The array 'basic' only includes: Wellington Point State School567000231000 (the last record), and not all records.

[whammy]

Without looking at your code in person, I think you're on the right track, then. It seems like the mystery is right under your nose. ;)

Just make sure you print it to the page to verify that what you expect is exactly what is happening. :D

[raf]

Whammy,

That update code should be correct. What kinda may confuse you is that its sort of a 'take the virst value and then jump to the last and run oit in reverse order' building order in the code.

Justasking,

Look at my previous posts. I already pointed out te problem ans sollution.
--> the second loop should be nested inside the first loop