Hi

I have the follwoing code:

<%
If Request.Form("Process")="True" Then

intRecIDs = request("TypeID")
arrRecIDs = Split(intRecIDs, ", ")
For i = 0 to Ubound(arrRecIDs) ' Loop trough the array
strText = request("txtValue")
strNum = request("PlanType")

set commUpdate = Server.CreateObject("ADODB.Command")
commUpdate.ActiveConnection = MM_login_STRING
commUpdate.CommandText = "UPDATE all_customer_apollo SET Total_QTR = " & strText & " AND Rec_Term = " & strNum & " WHERE OrderRef = " & intRecIDs

commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next

End If
%>

Everytime i run the code it comse back with the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

If i run the update on just one file it works fine but when done on multiple records it kicks back the error.

Does anyone have any ideas

Cheers

Simon

God do i hate ASP's debugger. Anyway. Your WHERE clause is messed up because, from the way your loop is structure, you are looping based on the upper bound indecy of arrRecIDs but you are not updating based on the current value of the array, you are trying to update based on a single integer (though since you are calling split() on it its really a string) thats where your problem is.

Take this for example

intRecIDs = "1,2,3,4"
arrRecIDs = Split(intRecIDs, ",")

your array looks like this

arrRecIDs(0) = 1
arrRecIDs(1) = 2
arrRecIDs(2) = 3
arrRecIDs(3) = 4

the 2 solutions here are 1) change your where clause to this: WHERE OrderRef = " & arrRecIDs(i) or
2) WHERE OrderRef IN(" & intRecIDs &")"

If you decide with the latter of the 2, drop your for loop. (You wont need to iterate through each RecID because an IN clause works like this: if this column matches any one of these values, UPDATE. )

That should get you going. (And use commas instead of AND when seperating columns, looks more professional ;])

"The one language all programmers understand is profanity."