Delete and insert data failed

[caulson]

HI , as below is an error i facing which is for the first step when i deleted the record , then insert into the data to other database table keep a record as "1900-01-01" and QTy is null 0. May i know is it DB corrput when insert into data in table ? kindly advise thank you.








<!--#INCLUDE VIRTUAL="/MMS-Test/includes/connection.asp"-->



<%
Dim SystemTime,objRSTime

SystemTime = "SELECT CURRENT_TIMESTAMP AS TIME"
Set objRSTime = Server.CreateObject("ADODB.Recordset")
objRSTime.Open SystemTime, oConnection

DIM EmpID,EmpName,TInvDate,TInvID,TInvCategory,MInvDat e,TVarCheck,Counter,SInvReason,TREASONREMARK,EmpLe vel

EmpID = Request.Form("HEmpID")
EmpName = Request.Form("HEmpName")
EmpLevel = Request.Form("HEmpLevel")
TVarCheck = CInt(Request.Form("TVarCheck"))
Counter = 1

SInvReason = Request.Form("SInvReason")
TREASONREMARK = Request.Form("TREASONREMARK")







if TRIM(SInvReason) = "" then

DIM Msg2

Msg2 = "** Mandatory Field not filled, please check !!!"

Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref=" & Msg2


else

'******* Get Each Row Data SQL Start ****************

DO While Counter <= TVarCheck

TInvDate = Request.Form("TInvDate"& Counter)
TInvID = Request.Form("TInvID"& Counter)
TInvCategory = Request.Form("TInvCategory"& Counter)
MInvDate = Request.Form("MInvDate"& Counter)
MInvPlanQty = Request.Form("MInvPlanQty"& Counter)
MTDelete = Request.Form("MTDelete"& Counter)


if MInvDate <> "" OR MInvPlanQty <> "" OR MTDelete <> "" OR IsNumeric(MInvPlanQty) OR IsDate( MInvDate) = TRUE then





'******* Get Inv ID Original Qty Then Calculate the Adjustment SQL Start ****************



DIM objRS3,objRSD,objRSD1,mySQL3,mySQLD,mySQLD1,MDInvD ate,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

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

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



MDInvDate = MInvDate

MDInvPlanQty = MInvPlanQty


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&"'"

mySQLD1 = "INSERT INTO OTH_INV_TRANSACTION_PLAN (INV_TRANS_PLAN_DATE,INV_PLAN_ID,INV_TRANS_PLAN_QT Y,INV_TRANS_PLAN_VENDORS,INV_TRANS_PLAN_REFNO,INV_ TRANS_PLAN_REASON_ID,INV_TRANS_PLAN_REMARK,INV_TRA NS_PLAN_CREATE_DATE,INV_TRANS_PLAN_CREATE_USER) values ('"&MDInvDate&"','"&TInvID&"','"&MDInvPlanQty&"',' "&PVENDOR&"','"&PREFNO&"','"&SInvReason&"','"&PREM ARK&"',GETDATE(),'"&EmpID&"')"



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

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



objRSD.Open mySQLD, oConnection

objRSD1.Open mySQLD1, oConnection


msg = "Record Deleted !"

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




end if
end if


end if




dim mySQLUP,mySQLUP1


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

mySQLUP1 = "INSERT INTO OTH_INV_TRANSACTION_PLAN (INV_TRANS_PLAN_DATE,INV_PLAN_ID,INV_TRANS_PLAN_QT Y,INV_TRANS_PLAN_VENDORS,INV_TRANS_PLAN_REFNO,INV_ TRANS_PLAN_REASON_ID,INV_TRANS_PLAN_REMARK,INV_TRA NS_PLAN_CREATE_DATE,INV_TRANS_PLAN_CREATE_USER) values ('"&MDInvDate&"','"&TInvID&"','"&MDInvPlanQty&"',' "&PVENDOR&"','"&PREFNO&"','"&SInvReason&"','"&PREM ARK&"',GETDATE(),'"&EmpID&"')"



Dim objRSUP,objRSUP1

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

objRSUP.Open mySQLUP, oConnection
objRSUP1.Open mySQLUP1, oConnection





Counter = Counter + 1

else

Counter = Counter + 1

end if


Loop



dim msg

Msg = "Plan Adjustment Update Completed !"

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


end if



%>


<%
oConnection.Close
Set oConnection = Nothing
%>

[gk53]

did you tried to print your sql on screen to verify you have correct one?