Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 20th, 2012, 03:22 AM
Registered User
 
Join Date: Nov 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update 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 as record. the INV_Date show the display DB default

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


AS below is the script.









<!--#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,objRSD2,mySQL3,mySQLD,mySQLD 1,mySQLD2,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 = "Modify" 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




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

'"UPDATE OTH_INV_PLAN set INV_DATE = '"&MDInvDate&"',INV_PLANQTY = '"&MDInvPlanQty&"' where 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&"')"

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





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

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





objRSD1.Open mySQLD1, oConnection

objRSD.Open mySQLD, oConnection


msg = "Record Deleted !"

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



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
%>

Last edited by caulson; November 20th, 2012 at 03:35 AM..
 
Old January 5th, 2013, 02:10 AM
Registered User
 
Join Date: Dec 2012
Posts: 7
Thanks: 1
Thanked 0 Times in 0 Posts
Default

This is a very informative post. And thanks for sharing.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Form to either Update or Insert data in SQL table snteran Classic ASP Basics 2 September 20th, 2012 08:23 PM
INSERT permission failed (p 28) calabresi BOOK: ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008: Problem Design Solutio 3 October 6th, 2011 04:47 PM
how to perform insert ,delete,update, display data in a sharepoint list from infopath charan SharePoint Development 1 June 23rd, 2010 05:27 AM
Failed to update database cothedo BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 4 July 29th, 2008 12:14 PM
Failed to update database edison7 ASP.NET 2.0 Basics 1 June 22nd, 2006 08:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.