Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    21

    Unanswered: Syntax Error in update statement

    Hello All,

    So, I have a small problem with an asp page I have, I am trying to update an access backend from an asp page. For some reason it doesn't seem to know which cell in the table should be updated though the where clause should be working???

    Here are the elements that I've used in my page:

    <%
    dim rsdeposited, rsDeposited_Uprovcreddate

    set rsdeposited = Server.CreateObject("ADODB.RecordSet")
    set rsDeposited_Uprovcreddate = Server.CreateObject("ADODB.RecordSet")

    strSQL = "SELECT tblPaidAmounts.PaidID, tblPaidAmounts.PaidDate," & _
    "tblPaidAmounts.Branch, branch.branch AS Branch, tblPaidAmounts.Collect, tblPaidAmounts.Discount," & _
    "[collect]-[discount] AS AfterDiscount, tblPaidAmounts.ExpAmt, tblPaidAmounts.DepositAmount " & _
    "FROM tblPaidAmounts INNER JOIN tblbranches AS Branch ON branch.branchid = tblpaidamounts.branch " & _
    "WHERE tblpaidamounts.Branch = " & Branchid

    rsdeposited.Open strSQL, oConn, 1, 3
    if Request.Form <> "" then

    rsDeposited_Uprovcreddate.Open "SELECT paidID, paiddate, branch, collect, discount, expamt, Depositamount FROM tblpaidamounts", oConn, 2, 3

    do while not rsDeposited_Uprovcreddate.EOF

    oConn.Execute "UPDATE tblpaidamounts SET Depositamount = " & Request.Form("depositamount" & rsDeposited_Uprovcreddate("paidID")) & " WHERE paidID = " & rsDeposited_Uprovcreddate("paidID")

    rsDeposited_Uprovcreddate.MoveNext
    loop

    rsDeposited_Uprovcreddate.Close : set rsDeposited_Uprovcreddate = nothing

    end if

    rsdeposited.Requery
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''
    if not rsdeposited.EOF then
    dim tempArray : tempArray = rsdeposited.GetRows()
    intRowCount = UBound(tempArray , 2) + 1

    rsdeposited.Requery
    rsdeposited.Move(intStart - 1)
    else
    intRowCount = 0
    end if

    intPages = Pages(intRowCount, intPage_Per_Count)
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''
    %>

    <td class="td_results"><input type="integer" maxlength="6" id="depositamount<%=rsdeposited("paidID")%>" name="depositamount<%=rsdeposited("paidID")%>" border="0" value="<%=rsdeposited("depositamount")%>"></td>

    <%rsdeposited.MoveNext
    end if
    next%>

    Any Ideas??

    I have attached the source file, too, thanks in advance by the way
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm a little troubled that you referred to fields as cells, that usually has some implications regarding your db design... But I digress...

    Can you elaborate on the behavior you're seeing vs. the behavior you're expecting?

    Have you debugged the sql string to make sure it's what you think it is? What do you get at the top of the page if you do this:

    ...
    'oConn.Execute "UPDATE tblpaidamounts SET Depositamount = " & Request.Form("depositamount" & rsDeposited_Uprovcreddate("paidID")) & " WHERE paidID = " & rsDeposited_Uprovcreddate("paidID")
    Response.Write "UPDATE tblpaidamounts SET Depositamount = " & Request.Form("depositamount" & rsDeposited_Uprovcreddate("paidID")) & " WHERE paidID = " & rsDeposited_Uprovcreddate("paidID")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2010
    Posts
    21
    the database is pretty much normalised and relational, I just used cells with reference to other values.

    I pasted the update sql into access and got a missing operator error since the values are not present outside of asp.

    The update statement works with the where clause but without the value from the form, i.e if I replace:

    oConn.Execute "UPDATE tblpaidamounts SET Depositamount = " & Request.Form("depositamount"& rsDeposited_Uprovcreddate("paidID")) & " WHERE paidID = " & rsDeposited_Uprovcreddate("paidID")


    with:

    oConn.Execute "UPDATE tblpaidamounts SET Depositamount = 0 WHERE paidID = " & rsDeposited_Uprovcreddate("paidID")

    then it updates the whole recordset which is also unusual, kind of lost, any other ideas?

    Thanks for your response by the way.

  4. #4
    Join Date
    Feb 2010
    Posts
    21
    OK, got it

    I should have used:

    if Request.Form("depositamount" & rsDeposited_Uprovcreddate("paidID")) <> "" then
    oConn.Execute "UPDATE tblpaidamounts SET Depositamount = " & Request.Form("depositamount" & rsDeposited_Uprovcreddate("paidID")) & " WHERE paidID = " & rsDeposited_Uprovcreddate("paidID")

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Response.Write() is your best friend when debugging dynamically constructed strings. I can't tell you how many times just double checking the query I'm sending has revealed it wasn't quite what I expected... Like in this case.

    Edit: Forgot to mention... I've nipped a lot of issues like this using the Firebug extension for Firefox too. You can see the form values you're about to post and get some immediate feedback on what's REALLY going to be populated in to the Request.Form() collection. Also handy is creating a quick and dirty ASP page that will rifle through the entire form var collection and spit out a table with the param name and value. Something like so:

    Code:
    <&#37; 
        Response.Write("<table>")
        for each p in Request.Form 
            Response.Write("<tr><td>" & p & "</td><td>" & Request.Form(p) & "</td></tr>") 
        next 
        Response.Write("</table>")
    %>
    Note that's untested, but you get the idea.
    Last edited by Teddy; 03-11-10 at 12:05.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Feb 2010
    Posts
    21
    Thanks Teddy,

    I do have another issue, I am kind of new to asp so... I will start a new thread though as it might be useful to other people but I appreciate the help you have given me.

Posting Permissions

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