Can only update one record in Access DB

[d2fox]

I have this code and it only seems to updates the first record (the first time through the FOR loop).

I retrieve a string of CONFIRMED PARTICIPANTS, separated by commas, from my web page. Then I execute the below to update each CONFIRMED PARTICIPANT. If I display the query each time through, they look fine. Is there some constraint where you can only do one update in a connection?

connectstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("access/mydatabase.mdb") & ";"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Connectstr
Set rsData = Server.CreateObject("ADODB.Recordset")

g_input=split(request("participantselect"),",")
num_inputted=ubound(g_input)
for counter =0 to num_inputted

UserSQL = "Update tblActivities SET confirmed = true where ProgramId = "
UserSQL = UserSQL & "'" & InputProgramID & "'"
UserSQL = UserSQL & " and ParticipantID = "
UserSQL = UserSQL & "'" & g_input(counter) & "'"
Conn.execute UserSQL
next

Conn.Close
End If

ALTERNATIVELY, I've tried:

connectstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("access/mydb.mdb") & ";"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Connectstr
Set rsData = Server.CreateObject("ADODB.Recordset")

g_input=split(request("participantselect"),",")
num_inputted=ubound(g_input)

UserSQL = "Update tblActivities SET confirmed = True where ProgramId = "
UserSQL = UserSQL & "'" & InputProgramID & "'"
UserSQL = UserSQL & " AND ParticipantID IN ("
For counter = 0 To num_inputted
InClause = InClause & "'" & g_input(counter) & "',"
Next
UserSQL = UserSQL & Left(InClause,Len(InClause)-1) & ")"
rsData.Open UserSQL, Conn, 2, 3
response.write (usersql)
rsData.Close
Conn.Close
End If

The SQL displayed is:

Update tblActivities SET confirmed = True where ProgramId = '21' AND ParticipantID IN ('10',' 14',' 5')
(looks ok to me)

This code again updates only the first participant in the list and then I get an error on the Close, saying that the
"Operation is not allowed when the object is closed. "

Can someone identify what I'm doing wrong?

Thanks!

[russell]

Is the ParticipantId a string or an integer? I see that you have white space in the values passed, which might be the problem. Also, you don't show where the ProgramId comes from, but anyway, try bnreaking out the update. Something like this

Dim g_input
Dim ParticipantID
Dim counter

g_input = split(request("participantselect"),",")

If isArray(g_input) Then
For counter = 0 To Ubound(g_input)
ParticipantID = g_input(counter)
updateParticipant ParticipantID
Next
End If

Sub updateParticipant(ParticipantID)
Dim connectstr
Dim conn
Dim sql

connectstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("access/mydatabase.mdb") & ";"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Connectstr

'' You don't show where InputProgramID comes from...
sql = "Update tblActivities SET confirmed = true " &_
"Where ProgramId = '" & InputProgramID & "' " &_
"And ParticipantID = " & ParticipantID

Conn.execute UserSQL
Conn.Close
End Sub