Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-06 : 07:59:15
|
| Max writes "This has asp, but it is a sql question. I looked at the articles on transactions and there was one similiar to this. If this is still not appropiate, sorry.The code below seems to not work. one of the tables gets updated and the other does not. Any clue as to why.conn.BeginTrans 'get ticket line set rstMISTicket = server.CreateObject("ADODB.Recordset") rstMISTicket.Open "select * from MISTickets where TicketID=" & strTIC,conn,adopendynamic,adlockoptimistic 'create adjustment recordset set rstAdjustments = server.CreateObject("ADODB.Recordset") rstAdjustments.Open "select * from Adjustments where AdjustmentID = 0",conn,adOpenDynamic,adLockOptimistic 'payment recordset set rstPayments = server.CreateObject("ADODB.Recordset") rstPayments.Open "select * from Payments where PaymentID = 0",conn,adOpenDynamic,adLockOptimistic 'save control data set rstControl = server.CreateObject("ADODB.Recordset") rstControl.Open "select * from ControlLedger where ControlDate>dateadd(day,-1,getDate()) and ControlDate<dateadd(day,+1,getDate()) and ControlPayCodeID=" & rstMISTIcket("PayCodeID"),conn,adOpenDynamic,adLockOptimistic if rstControl.EOF and rstControl.BOF then rstControl.AddNew rstControl("ControlDate") = date() rstControl("ControlPayCodeID") = rstMISTicket("PayCodeID") rstControl("FacilityID") = Request.Cookies("csltoars")("LID") end if rstControl("AdjustmentTotal") = rstControl("AdjustmentTotal") + Request.Form("txtAdjustmentAmount") rstControl.update 'save adjustment data rstAdjustments.AddNew rstAdjustments("PostDate") = date() rstAdjustments("PayCode") = rstMISTicket("PayCodeID") rstAdjustments("PreviousBalance") = rstMISTicket("Balance") rstAdjustments("AdjustmentAmount") = Request.Form("txtAdjustmentAmount") rstAdjustments("AdjustmentReason") = Request.Form("cboAdjustmentReason") rstAdjustments("NewBalance") = cdbl(rstMISTicket("Balance")) + cdbl(Request.Form("txtAdjustmentAmount")) rstAdjustments("EnteredBy") = Request.Cookies("csltoars")("UNAME") rstAdjustments("TicketID") = rstMISTicket("TicketID") rstAdjustments("NotIFR") = (Request.Form("chkIFR") = "1") rstAdjustments("ApplyToPayment") = (Request.Form("chkPayment") = "1") rstAdjustments("AdjustmentNotes") = Request.Form("txtAdjustmentNote") 'if apply to payment journal is selected make entry into the payments table 'this would be done to correct miskey of payment amount. if Request.Form("chkPayment") = "1" then rstPayments.AddNew rstPayments("PostDate") = date() rstPayments("PayCodeID") = rstMISTicket("PayCodeID") rstPayments("PreviousBalance") = rstMISTicket("Balance") rstPayments("PaymentAmount") = cdbl(Request.Form("txtAdjustmentAmount")) * -1 rstPayments("NewBalance") = rstMISTicket("Balance")+ Request.Form("txtAdjustmentAmount") rstPayments("EnteredBy") = Request.Cookies("csltoars")("UNAME") rstPayments("TicketID") = rstMISTicket("TicketID") rstPayments("Adjustment") = (Request.Form("chkPayment") = "1") rstPayments.update end if 'save ticket data rstMISTicket("Balance") = cdbl(rstMISTicket("Balance")) + cdbl(Request.Form("txtAdjustmentAmount")) rstMISTIcket.update rstAdjustments.Update 'commit the transaction on the SQL Server if err.number <> 0 then conn.RollbackTrans else conn.committrans end if rstAdjustments.Close rstMISTicket.close rstPayments.close rstControl.Close set rstControl = nothing set rstAdjustment = nothing set rstMISTicket = nothing set rstPayments = nothing" |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-06 : 09:37:05
|
| What's with all the dynamic SQL lately?Can't you create a stored procedure and pass the parameters to it?I think you would have more control over the transactions (and are you really creatin a transaction with this code?)Plus your doing "optimistic" locking (I really love microsoft with some of the concepts they come up with). That means ( I think) anything goes....which is good, why?Brett8-) |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-06 : 19:57:43
|
quote:
and are you really creatin a transaction with this code?)
yes,http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg03_7jov.aspHowever it's a heck of price to pay for 3 select and 2 inserts.Take X002548's advice stop the insanity, unless you're looking to create a maintenance nightmare for the next poor soul. Read up on ADO Command objects, next create an include file for all your data access in it have functions that receives a command object as input and returns either a recordset or a command object or combine. Two functions or one your choice.Take a look at the example which would be the best way to do it http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=21923T-SQL provides an if construct.A statement like this you would convert to an insert statement.quote:
'this would be done to correct miskey of payment amount. if Request.Form("chkPayment") = "1" then rstPayments.AddNew rstPayments("PostDate") = date() rstPayments("PayCodeID") = rstMISTicket("PayCodeID") rstPayments("PreviousBalance") = rstMISTicket("Balance") rstPayments("PaymentAmount") = cdbl(Request.Form("txtAdjustmentAmount")) * -1 rstPayments("NewBalance") = rstMISTicket("Balance")+ Request.Form("txtAdjustmentAmount") rstPayments("EnteredBy") = Request.Cookies("csltoars")("UNAME") rstPayments("TicketID") = rstMISTicket("TicketID") rstPayments("Adjustment") = (Request.Form("chkPayment") = "1") rstPayments.update end if
Here are some sp basic to read up on.http://www.sqlteam.com/item.asp?ItemID=563http://www.sqlteam.com/item.asp?ItemID=2644http://www.sqlteam.com/item.asp?ItemID=2755http://www.sqlteam.com/item.asp?ItemID=2463Give it a shot and we'll help you through it. |
|
|
|
|
|
|
|
|
SQL transaction appearing to not work