#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2012
    Posts
    19
    Rep Power
    0

    Delete and insert data failed


    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
    %>
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,548
    Rep Power
    278
    did you tried to print your sql on screen to verify you have correct one?
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Similar Threads

  1. Delete and Insert at same time
    By phoenixaz in forum ASP Development
    Replies: 2
    Last Post: November 6th, 2012, 03:34 PM
  2. Odbc linked tables failed on insert
    By fsrebot in forum Microsoft SQL Server
    Replies: 0
    Last Post: February 1st, 2008, 06:27 PM
  3. Insert and delete of the same row
    By dhaneshrs in forum .NET Development
    Replies: 0
    Last Post: August 16th, 2007, 12:42 AM
  4. Insert,Update,delete data from a table
    By Salman8506 in forum ASP Development
    Replies: 11
    Last Post: April 9th, 2007, 06:34 AM
  5. Insert , Delete ,Update Data Using SQL Connection
    By e179 in forum Microsoft SQL Server
    Replies: 3
    Last Post: January 10th, 2003, 04:40 AM

IMN logo majestic logo threadwatch logo seochat tools logo