Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Delete and insert data failed

Results 1 to 2 of 2
Share This Thread →
  1. #1
    caulson is offline Registered User
    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
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    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

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates