array query with multiple <SELECT> form field

[Burton Figg]

I have a SELECT statement which holds a list of times (for adding
appointments to a database):

e.g.

<select name="time" id="time" size="3" multiple>
<option value="00:00">00:00</option>
<option value="00:30">00:30</option>
<option value="01:00">01:00</option>
<option value="01:30">01:30</option>
<option value="02:00">02:00</option>
<option value="02:30">02:30</option>
</select>

on the process page, I want to loop through the values of the "time" select
field, and enter them into a database.

I have tried this:

strSubject = request.Form("subject")
strDate = request.Form("theDate")
strTime = request.Form("time")
strBody = request.Form("body")
strName = request.Form("name")
strTel = request.Form("tel")
strEmail = request.Form("email")

myArray = Split(Request("time"),",")

For iy = 0 To UBound(myArray)
sql = "INSERT INTO appointments (the_Date, the_time, fldSub, fldBod,
fldName, fldTel, fldEmail) "
sql = sql & " VALUES
('"&strDate&"','"&strTime&"','"&strSubject&"','"&strBody&"','"&strName&"','"
&strTel&"','"&strEmail&"')"

set MyConn = oConn.Execute(sql)
Set MyConn = Nothing
Next

but it only inserts into the database the value of the first "time" record
(e.g. if I selected 00:00, 00:30, 02:30 it inserts "00:00" each of the three
times through the loop.

The output of the "time" field is:

00:00, 00:30, 02:30

so I thought this line:

myArray = Split(Request("time"),",")

would split the "time" field into 3 chunks, and each time through the loop I
was expecting it to take the next value.

Am I missing something obvious?

Thanks

Jim

 

[Ray at]

You're inserting strTime instead of myArray(iy).

Also, you should split with ", " instead of "," since there's that space in
between each time value.

Also, instead of
Set MyConn = oConn.Execute(sql)
just use:
oConn.Execute sql

One other suggestion I have is to get in the habit of using spaces when
concatenating strings. Like, instead of:

&strDate&"','"&strTime&"','"&strSubject&"','"

do:

& strDate & "','" & strTime & "','" & strSubject & "','" ...

In addition to making your code more readable, it'll avoid confusion when
you're using code like so:

Dim h1
h1 = "15 feet"
Response.Write "Joe jumped "&h1

Ray at home