Unavailable update data

[caulson]

Hi, i had an problem statement as below ASP code, i cannot update the Quantity, kindly advise thank you.



DIM objRS3,objRSD,mySQL3,mySQLD,MDInvDate,MDInvPlanQty ,MDTDelete

mySQL3 = "SELECT * FROM OTH_INV_PLAN Where INV_DATE = '"&TInvDate&"' and INV_ID = '"&TInvID&"'"



Set objRS3 = Server.CreateObject("ADODB.Recordset")

objRS3.Open mySQL3, oConnection






if SInvReason = "Modifty" then

MDInvDate = Request.Form("MInvDate"& Counter)

MDInvPlanQty = Request.Form("MInvPlanQty"& Counter)



if MDInvDate = Request.Form("MInvDate"& Counter) then


mySQLUP = "UPDATE OTH_INV_PLAN set INV_DATE = '"&MDInvDate&"' where INV_ID = '"&TInvID&"'"


Set objRSUP = Server.CreateObject("ADODB.Recordset")

objRSUP.Open mySQLUP, oConnection


Msg = "Plan Adjustment Update Completed !"

Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref3=" & Msg

else


if MDInvPlanQty = Request.Form("MInvPlanQty"& Counter) then


mySQLUP1 = "UPDATE OTH_INV_PLAN set INV_PLANQTY = '"&MDInvPlanQty&"' where INV_ID = '"&TInvID&"'"


Set objRSUP1 = Server.CreateObject("ADODB.Recordset")

objRSUP.Open mySQLUP1, oConnection


Msg = "Plan Adjustment Update Completed !"

Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref3=" & Msg




else


if MDInvDate = Request.Form("MInvDate"& Counter) and MDInvPlanQty = Request.Form("MInvPlanQty"& Counter) then


mySQLUP2 = "UPDATE OTH_INV_PLAN set INV_DATE = '"&MDInvDate&"' , INV_PLANQTY = '"&MDInvPlanQty&"' where INV_ID = '"&TInvID&"'"


Set objRSUP2 = Server.CreateObject("ADODB.Recordset")

objRSUP2.Open mySQLUP2, oConnection


Msg = "Plan Adjustment Update Completed !"

Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref3=" & Msg


else


if SInvReason = "Delete" then

MDTDelete = Request.Form("MTDelete"& Counter)
MDTDelete = MTDelete

if MDTDelete = MTDelete then

mySQLD = "Delete FROM OTH_INV_PLAN Where INV_DATE = '"&TInvDate&"' and INV_ID = '"&TInvID&"'"

Set objRSD = Server.CreateObject("ADODB.Recordset")

objRSD.Open mySQLD, oConnection

msg = "Record Deleted !"

Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref3=" & Msg




else


if SInvReason = "Delete" then

MDInvPlanQty = Request.Form("MInvPlanQty"& Counter)


MDInvPlanQty = MDInvPlanQty


if MDInvDate = 0 then

msg = "Plan Adjustment Delete !"

Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref3=" & Msg

end if
end if
end if
end if
end if
end if
end if
end if


end if

[gk53]

I'm nor sure where you get an error and what you are trying to update, but,
you are open recordset in the beginning of the page.

mySQL3 = "SELECT * FROM OTH_INV_PLAN Where INV_DATE = '"&TInvDate&"' and INV_ID = '"&TInvID&"'" 
Set objRS3 = Server.CreateObject("ADODB.Recordset")
objRS3.Open mySQL3, oConnection

after that you are trying to update one or another field on the same record... and use for that something like
objRSUP2.Open mySQLUP2, oConnection, but if you run update or delete statement you will not get record set, so it should be something like oConnection.execute(mySQLUP2)....
second issue you have recordset for "SELECT * FROM OTH_INV_PLAN Where INV_DATE = '"&TInvDate&"' and INV_ID = '"&TInvID&"'" open and trying to update the same record... you are lock your self...
do your know you can open recordset for update? like objRS3.Open mySQL3, oConnection, 1, 3 (keyset, lock optimistic) and for update all what you need just

objRS3("INV_DATE") = MDInvDate
objRS3("INV_PLANQTY") = MDInvPlanQty
objRS3.update

it will make your page simple... and will work

[sqlconfused]

Those are a lot of if/thens, you might want to use the CASE feature.

also you are aware that your 'modify' is spelled modifty?