Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 ADO and multiple table inserts. HELP
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rigger82
Junior Member

USA
311 Posts

Posted - 12 April 2001 :  12:12:01  Show Profile  Visit Rigger82's Homepage  Send Rigger82 an AOL message  Send Rigger82 a Yahoo! Message
Ok, Im using ADO against SQL 7 and Im trying to do a simple addnew/update on 2 tables. The tables are joined by userid and Ive read what I have been able to find and I put all the fields from the parent table first and then called rs.update, then put all the fields from the child and called rs.update again... still getting the following.

Microsoft OLE DB Provider for SQL Server error '80004005'

Cannot insert or update columns from multiple tables.

Does anyone have information on this or another easy way to take form data and dump to multiple related tables?

Please help... getting a headache.

---------------------
Mark Christianson
Gulf War Veteran (82nd Airborne Division)
Owner/Operator of www.paratrooper.net
Rigger@paratrooper.net

PJamieson
Starting Member

United Kingdom
11 Posts

Posted - 12 April 2001 :  17:23:38  Show Profile  Visit PJamieson's Homepage
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
Go to Top of Page

Rigger82
Junior Member

USA
311 Posts

Posted - 16 April 2001 :  11:18:43  Show Profile  Visit Rigger82's Homepage  Send Rigger82 an AOL message  Send Rigger82 a Yahoo! Message
Thanks for the code assist... that should square me away!

---------------------
Mark Christianson
Gulf War Veteran (82nd Airborne Division)
Owner/Operator of www.paratrooper.net
Rigger@paratrooper.net
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000 Version 3.4.07