Here's some code - cut and pasted - from a one of my sites that carries our 5 updates one after the other. It's using SQL7. Perhaps it will help...I've only posted 2 of the updates. There's a lot of record re-ordering going on with this hence the messy array in the middle. But it works!
If IsObject(conn) then 'If there is an open connection, use it
if Conn.state=0 then Conn.open
connection_exists=True
else 'If there is not a connection available, open one up
Set conn=Server.CreateObject("ADODB.Connection")
%>
<!--#include file="../connstring.asp" -->
<%
conn.open connstring
connection_exists=False
end if
Conn.BeginTrans
set RS_Insert = Server.CreateObject("ADODB.Recordset")
With RS_Insert
.CursorLocation = adUseClient
.Activeconnection=Conn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Datamember=adCmdTableDirect
.open "Form_"&FormType&"" ' , Conn, adOpenDynamic,adLockOptimistic,adCmdTableDirect
.AddNew
.Fields("active")=active
.Fields("title")=title_2
.Fields("abstract")=abstract_2
.Fields("text")=Text_2
.Fields("link1")=Link1_2
.Fields("link2")=Link2_2
.Fields("link3")=Link3_2
.Fields("datemodified")=getshortdatetime(Now())
.Update
'# Output required fields - SHOULD NOT REQUIRE CHANGES OR ADDITIONS
NewID=.Fields("ID") '#Get New RecordID
Datemodified=.Fields("datemodified") '#Get Creation Date
end with
'#Create a Flag for use with media attachments
ShowSubmit=False '#Reset in case of caching
If NewID<>"" then ShowSubmit=true
'# Close down recordset object - DB Connection is closed by templates.asp page
RS_Insert.close
set RS_Insert=nothing
Call DBCheck() '#Check for DB Errors
'# Insert area attachments to database
Split_Link_Array=Split(AppearsIn,",")
For i=0 to UBound(Split_Link_Array)
If Trim(UCASE(Split_Link_Array(i)))<>"HOMEPAGE" then
sSQL_HighestRecord="SELECT Top 1 SortOrder FROM Link_Subsection_"&FormType&" WHERE SubSectionID="&Trim(Split_Link_Array(i)) &"ORDER BY SortOrder DESC"
set RS_HighestRecord = Server.CreateObject("ADODB.Recordset")
Set RS_HighestRecord.ActiveConnection=Conn
RS_HighestRecord.open sSQL_HighestRecord, Conn, 2,2
If RS_HighestRecord.Eof then '#In case it is the first record
HighestRecord=1
else
If IsNull(rs_HighestRecord("SortOrder")) then '#Incase there is a NULL Record in the Table
HighestRecord=1
else
HighestRecord=CInt(rs_HighestRecord("SortOrder"))+1
end if
end if
RS_HighestRecord.close
Set RS_HighestRecord=nothing
SQL_Create_Link= "INSERT INTO Link_Subsection_"&FormType&" (ArticleID, SubSectionID, SortOrder) VALUES('" &NewID& "','" &Trim(Split_Link_Array(i)) & "',"&HighestRecord&")"
else
SQL_Create_Link= "INSERT INTO Link_Homepage_Tables (ArticleID, Table_Type) VALUES('" &NewID& "','" &FormType& "')"
end if
conn.execute SQL_Create_Link
next
Call DBCheck() '#Check for DB Errors
IF strStatus = "" THEN '#NO ERRORS in Insert
strFeedBack = "A new record has been entered into the database"
ShowSubmit=True
Conn.CommitTrans
ELSE
strFeedBack = strStatus '#Error have occured
Conn.RollBackTrans
END IF
Edited by - PJamieson on 12 April 2001 17:28:54