Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder meth's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Thanks
    0
    Thanked 9 Times in 9 Posts

    update command concatenates - not replaces

    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...
    Last edited by meth; 02-26-2003 at 11:41 AM.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    meth,

    this is how i do my updating:
    Code:
    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:
    Code:
    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.

  • #3
    Regular Coder meth's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Thanks
    0
    Thanked 9 Times in 9 Posts
    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...

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Code:
    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.

  • #5
    Regular Coder meth's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Thanks
    0
    Thanked 9 Times in 9 Posts
    repeat region = repeating rows of record set...

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

    Using Forms for Batch Updating

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •