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

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:

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.

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")

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:

Note that's untested, but you get the idea.

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.