Multiple Update from text fields

[Roy Adams]

Hello people I've recently been woring with multiple insert from text
fields and got that woking fine thanks to the help from people from
this forum
now i'm trying to deal with multiple update, I'm pretty much using the
same script but it isn't working properly
I have a text field in a form with a text field called color, in a
repeated region, that shows the amount of colours for the item that
your viewing
it submits to another page that handles spliting the querystring
putting it into an array and looping through them.
It does update all the records that have the same productID value but,
only with the last item in the array if the last text field contains
the value red it updates all fields with red that have the productID
of 32
here's the code
maybe the problem could be with my sql statement

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<!--#include file="../../Connections/conn.asp" -->
<%

var colorForm = String(Request.Form("color"));//get the form field and
put into var
colorForm = colorForm.replace(/'/g, "''");
colorForm_array = colorForm.split(",");//split at ","

if( colorForm !="undefined" ){
for( i=0 ; i < colorForm_array.length ; i ++){// loop through the
array


conn = Server.CreateObject('ADODB.Command');
conn.ActiveConnection = MM_conn_blenz_STRING;

conn.CommandText = ("UPDATE color SET color='" + colorForm_array +
"' WHERE ProductID = 32" )

conn.Execute();
conn.ActiveConnection.Close();

}
}
Response.Redirect("UpdateMenu.asp?ProductID=32&uc=1");





%>

 

[Manohar Kamath [MVP]]

Roy,

First of all, what are you trying to do? Second, what does your table color
look like? From what you showed, a sample SQL string could be

UPDATE color SET color = 'White' WHERE ProductID = 32

This updates ALL records that have ProductID of 32. Therefore, the last such
SQL statement overrides all the ones before. You might want to have
additional WHERE conditions, something like:

UPDATE color SET color = 'White' WHERE ProductID = 32 AND ColorID = 55

Where 55 is dynamic.