update command concatenates - not replaces

[meth]

ASP/VB update command to access database.

This update command manipulates the correct access cell, however instead of replacing the value, it concatenates it to the character-limit of the cell.

e.g. "original", becomes "original, substitute"

I have a few ideas about what's going on, but anything I try to correct the command just throws an error. Please, any advice on an edit will be welcome.

code:
-------------------------------------------------------------------------
<%

if(Request("someValue") <> "") then cmdDoIt__varsomeValue = Request("someValue")

if(Request("someID") <> "") then cmdDoIt__varsomeID = Request("someID")

%>

----truncated------------
<%

IF Request("Submitted") <> "" THEN
IF (Request("someID") <> "") THEN
set cmdDoIt = Server.CreateObject("ADODB.Command")
cmdDoIt.ActiveConnection = MM_connGGG2_STRING
cmdDoIt.CommandText = "UPDATE someTable SET someValue= '" + Replace(cmdDoIt__varsomeValuel, "'", "''") + "' WHERE someID IN (" + Replace(cmdDoIt__varsomeID, "'", "''") + ")"
cmdDoIt.CommandType = 1
cmdDoIt.CommandTimeout = 0
cmdDoIt.Prepared = true
cmdDoIt.Execute()
END IF
Response.Redirect("samePage.asp")
END IF

%>

----------------------------------------------------------------------------

The conditionals ensure the multiple list of records is updated for only the records where the checkbox (someValue) is selected.

I'd simple like to know how to edit this command to do a true replace, and if poss - why this code isn't doing the job properly...

[raf]

meth,

this is how i do my updating:

sql="update table set variable='newvalue' where conditionvalue=condition"
sql=replace(sql,"newvalue",replace(request.form("value"),"'","''"))
sql=replace(sql,"condition",replace(request.form("conditionID"),"'","''"))
connection.Execute sql, numupdated


if you only want to run the updatecommand when a checkbow was checked, then use and if then statement:

if request.form("check") = 1 then
sql="update table set variable='newvalue' where conditionvalue=condition"
sql=replace(sql,"newvalue",replace(request.form("value"),"'","''"))
sql=replace(sql,"condition",replace(request.form("conditionID"),"'","''"))
end if

if you want to update multiple records (say you had a page on the previous screen where you see multiple records and each record has a checkbox, you can use a
for each box in request.form loop. You then have to name your fields on that form so that you can identify the checkboxes and corresponding textfields. (usually involves also a nested for each-next control of flow)
Or you can use an array and write a function that runs an update for each row of the array.

[meth]

hmmm

the trouble is, this form is a repeating region for all the rows contained in the table.

the checkbox enables it's bound ID value anf this part of the script works fine. However the submitted value for the menu value in the same row of the form is repeated for every row in the region e.g.

"UPDATE someTable SET someValue = 'substitute, substitute, subsitiute, substitute, substitute, subsitiute, substitute, substitute, subsitiute,' WHERE someID IN (5)"

can your soulution work with a form compiled by a repeat region do you think?

/me has gots to go to work - check back in a few hours...

[raf]

What’s a repeat region ?
What I do is:
- in the form: insert checkbox with name=”cbIDn°” (like cb1, cb2, cb3) and then I have a textfield with name=”tfIDn°( (like tf1, tf2, …) and the value. These fields are generated by looping through a recordset, so I just store the id for each record as part of the name of the corresponding fields.
- After submitting, I run through the request.form collection. Normally, only the fields that contain values are included.


For each box in request.form
If left(box,2)=’cb’ and request.form(box)=1 then ‘if we have a checked box
Id=right(box,len(box)-2) ‘get the number
For each box in request.form
If left(box,2) = “tf” and right(box,len(box)-2) = id then ‘get the right corresponding textfield
Value = request.form(box)
End if
Next
End if
If id <> “” and value <>”” then
Sql =”update table set variable=’avalue’ where id=anid”
Sql=replace(sql,”avalue”,value)
Sql=replace(sql,”anid”,id)

Etc

End if
Next



I confes: it’s maybe to complicated. Maybe to many looping. But it’s still fast + creates some oportunitys (for instance: after the first condition: a insert a counter like numchecked = numchecked + 1 and after the execute command, I insert a second counter totnumupdated=totnumupdated + numipdated so you can do some checking and give some ino back to the user)

I’m wondering if you don’t have fields in your form that have the same name. It looks like that. If you have five fields that have name=”test”, then the value that is posted for this variable will be “value1, value2, …, value5”. Maybe that's causing your problem.

[meth]

repeat region = repeating rows of record set...

anyway, someone has pointed me in the right direction with "batch updating"...

Using Forms for Batch Updating (http://www.4guysfromrolla.com/webtech/100199-2.shtml)

Thanks heaps for ur help - I'll try your ideas and see what I can pick up :)