troubleshooting Question

ADO Update Recordset ASP

Avatar of jayh99
jayh99 asked on
ASP
14 Comments1 Solution597 ViewsLast Modified:
I am trying to update multiple records in a database in one page.  The page is looping through, and attempting to make all the updates.  However, the problem I am having is that only the last update is working.  Is there a problem with the page looping through before the transaction completes?  If so, how do you commit the transactions before continuing through the loop?  Thanks in advance for the help.



<%
'Dimension variables
Dim adoCon         'Holds the Database Connection Object
Dim strSQL          'Holds the SQL query to query the database
Dim n 'Integer
Dim connStr

'Creates Database Connection Object
Set adoCon = Server.CreateObject("ADODB.Connection")

connStr = "DRIVER={SQL Server}; SERVER=GEOAPP; Database=TPS; UID=jharris; PWD=P@$sW0r9;"
adoCon.Open(connStr)

arrOrder = Split(Request("NewOrder")," ")
For i = 0 to Ubound(arrOrder)
If not arrOrder(i) = "undefined" Then
If not arrOrder(i) = "" Then
arrOrder(i) = Replace(arrOrder(i), "undefined", "")
n = n + 1
If not int(arrOrder(i)) = int(n) Then
strSQL="UPDATE Project SET Priority = '" & n & "' WHERE Priority = '" & arrOrder(i) & "';"
adoCon.BeginTrans
adoCon.Execute(strSQL)
adoCon.CommitTrans

Response.Write strSQL & "<br/>"
End If
End If
End If
Next
%>
ASKER CERTIFIED SOLUTION
Computer101

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros