|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 13th, 2003, 08:54 PM
|
|||
|
|||
Insert multiple records
I'm trying to set up a form whereby users can select a variety of different subject mailing lists they'd like to be added to.
Each subject is in a different table so I want the FROM part of my SQL statement to loop through the values selected from the list box which I've loaded into an array. But I'm getting a Type mismatch error. Code:
Dim strSQL
Dim subjects
Dim subjArray
Dim iLoop
subjects = Request.Form("subjects")
subjArray = split(subjects)
strSQL = "SELECT firstName, lastName, address1, address2, city, state, pcode, country, email FROM"
For iLoop = LBound(subjArray) to UBound(subjArray)
subjArray(iLoop)
Next
|
|
#2
October 13th, 2003, 10:17 PM
|
|||
|
|||
|
Are you having problems with the select statement or are you sure you can't make it one select statement?
If it's just loop it'd be easier to just say Code:
for each item in subArray do something loop
|
|
#3
October 13th, 2003, 10:24 PM
|
|||
|
|||
|
I'm trying to add each item in the array to the FROM part of the select statement
e.g. "SELECT firstName, lastName, address1, address2, city, state, pcode, country, email FROM subjArray(0), subjArray(1), subjArray(2) etc
|
|
#4
October 14th, 2003, 12:01 AM
|
|||
|
|||
|
OK, I worked out what I was doing with the FROM bit. I had split up my array into individual subjects, but for the FROM part of the query, I actually want the input to be exactly as it comes from the form: ie art, biol, chem etc.
I've basically got my SQL statement working, but the looping is still not quite right. This code: Code:
For iLoop = LBound(subjArray) to UBound(subjArray)
strFields = subjArray(iLoop) & ".firstName, " & subjArray(iLoop) & ".lastName, "
Next
strFields = strFields & ", " & strFields
strSQL = "SELECT " & strFields & " FROM " & subjects
gives me: SELECT geog.firstName, geog.lastName, geog.firstName, geog.lastName FROM biol, chem, geog which is repeating the last item selected rather than all the different ones. Also, I only get two iterations. I know that's cos strFields is set to equal itself plus one other, but I couldn't figure out how to set that within the loop too.
|
|
#5
October 14th, 2003, 08:20 PM
|
|||
|
|||
|
I kinda keep answering my own questions on this one, so after another day or so, I might figure out what I'm asking here, but in case I don't....
This code: Code:
Dim strSQL
Dim subjects
Dim subjArray
Dim iLoop
Dim strFieldsZ
Dim strFieldsY
Dim strFieldsX
subjects = Request.Form("subjects")
subjArray = split(subjects, ", ", -1, 1)
strFieldsZ = ""
For iLoop = LBound(subjArray) to UBound(subjArray)
strFieldsY = subjArray(iLoop) & ".firstName, " & subjArray(iLoop) & ".lastName"
if LBound(subjArray) = UBound(subjArray) then
strFieldsX = strFieldsY
else
strFieldsX = strFieldsY & ", " & strFieldsZ
end if
strFieldsZ = strFieldsY
Next
strSQL = "SELECT " & strFieldsX & " FROM " & subjects
gives me all the items selected except the first in the list (which seems to be the last in the array). So, if I select Art, Biology and Chemistry, my SQL query will only contain Chemistry and Biology. So, what am I missing?
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Insert multiple records |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
