Only first record is being updated in ACCESS database via Conn OBJ

[apple17]

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

 

[cmmrfrds]

The number of times through the For loop is determined by what is in num_inputted. Did you display this number right before the For loop?

 

[apple17]

Yes, it is actually going through the loop and generating the appropriate SQL statements (I've displayed the statements). There is no error, it acts as if the records got updated each time, but only the first one is updated. I'm wondering if there's an issue with the Connection, e.g. it needs to be reset, or I need to obtain the record via a SELECT before doing an update?

Is there an error code I can check?

 

[Golom]

Just for variety, try

Dim InClause As String
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) & ")"
Conn.execute UserSQL

 

[apple17]

hmmm...same result, only the first record is updated. Why would that be?

 

[cmmrfrds]

Try explicitly defining the string "UserSQL" before entering the loop.

Dim UserSQL as Variant

 

[apple17]

It is already declared. I'm using ASP (not .Net) and I don't think I can declare a type, but I believe that Variant is the default