|
 |
asp_databases thread: insert data into 2 tables from one form
Message #1 by "Mark Collins" <Mark.Collins@c...> on Sun, 19 Nov 2000 21:53:48 -0000
|
|
I need to be able to create a form which will take values and insert data
into two tables which have a relational link.
Table1: Fields are, ClientID, FirstName, PhoneNumber, Email
Table2: Fields are, SiteID, URL, Username, Password
ClientID and siteID are referentially linked.
I create a form prompting for the above fields, (Less ClientID and
SiteID), then I use a standard sql statement to insert the data into each
table:
*******************************************************************
sql1 = "insert into Table1 (FirstName, PhoneNumber, Email) "
sql1 = sql1 & "values ('"
sql1 = sql1 & FirstName & "', '"
sql1 = sql1 & PhoneNumber & "', '"
sql1 = sql1 & Email
sql1 = sql1 & "')"
sql2 = "insert into Table2 (URL, UserName, Password) "
sql1 = sql1 & "values ('"
sql1 = sql1 & URL & "', '"
sql1 = sql1 & UserName & "', '"
sql1 = sql1 & Password
sql1 = sql1 & "')"
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql1
con.Execute sql2
' Done. Close the connection
con.Close
Set con = Nothing
****************************************
I then get an error telling me that I cannot update a table with a
referential link - but the crazy thing is that it will update th first
table and not the second in the database. If I change the order the
converse happens.
Am I doing this the right way or am I way off the mark?
Message #2 by "Dallas Martin" <dmartin@z...> on Sun, 19 Nov 2000 21:27:45 -0500
|
|
How does the second table know about the related fields if you
don't include them in the second sql statement?
You have to explicity include the required linked fields with the
second sql statement.
You have to write a query to extract this information from the
first insert. Also, I assume that the client ID is an identity field
(autonumber in Access), so it's value is automatically generated
for each new inserted record.
You can retreive its value by modifying your code like this:
sql1 = "insert into Table1 (FirstName, PhoneNumber, Email) "
> sql1 = sql1 & "values ('"
> sql1 = sql1 & FirstName & "', '"
> sql1 = sql1 & PhoneNumber & "', '"
> sql1 = sql1 & Email
> sql1 = sql1 & "')"
sql1 = sql1 & "; select @@IDENTITY as ID;"
>
Set rs = con.execute(sql)
identity = rs("ID")
Now, where does the site_id come from?
Shouldn't it have been included in the first query
if it is related to a record in the second table?
If so, you need to add the proper code to the first and
second query to insert the site_id in the record.
Since you are not getting any error with the first insertion,
I'll bet site_id is not a required field. If its not a required
field then you are going to have orphan records in your
db. Best to change site_id to "required".
If you are using SQL65/70, why don't you write a stored
procedure to handle everything in one trip to the server?
Dallas Martin
----- Original Message -----
From: "Mark Collins" <Mark.Collins@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, November 19, 2000 4:53 PM
Subject: [asp_databases] insert data into 2 tables from one form
> I need to be able to create a form which will take values and insert data
> into two tables which have a relational link.
>
> Table1: Fields are, ClientID, FirstName, PhoneNumber, Email
> Table2: Fields are, SiteID, URL, Username, Password
>
> ClientID and siteID are referentially linked.
>
> I create a form prompting for the above fields, (Less ClientID and
> SiteID), then I use a standard sql statement to insert the data into each
> table:
> *******************************************************************
> sql1 = "insert into Table1 (FirstName, PhoneNumber, Email) "
> sql1 = sql1 & "values ('"
> sql1 = sql1 & FirstName & "', '"
> sql1 = sql1 & PhoneNumber & "', '"
> sql1 = sql1 & Email
> sql1 = sql1 & "')"
>
>
> sql2 = "insert into Table2 (URL, UserName, Password) "
> sql1 = sql1 & "values ('"
> sql1 = sql1 & URL & "', '"
> sql1 = sql1 & UserName & "', '"
> sql1 = sql1 & Password
> sql1 = sql1 & "')"
>
>
> Set con = Server.CreateObject("ADODB.Connection")
> con.Open data_source
> con.Execute sql1
> con.Execute sql2
> ' Done. Close the connection
> con.Close
> Set con = Nothing
>
> ****************************************
>
> I then get an error telling me that I cannot update a table with a
> referential link - but the crazy thing is that it will update th first
> table and not the second in the database. If I change the order the
> converse happens.
>
> Am I doing this the right way or am I way off the mark?
>
Message #3 by "jigs news letter" <newsgroup@h...> on Mon, 20 Nov 2000 09:52:08 +0530
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_00EF_01C052D7.8BE71F40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
hi Mark,
COULD YOU PLEASE SPECIFY WHAT RELATIONSHIP EXISTS
i assume that table1 is the parent and table2 is child
if both the tables are linked (foreign key relationship) then while
inserting data into table2, table1's clientid has to be specified in the
insert statement.
what you have to do is
'*** begin a new transaction
con.begintrans
'*** insert into table 1
sql1 =3D "insert into Table1 (FirstName, PhoneNumber, Email) "
sql1 =3D sql1 & "values ('"
sql1 =3D sql1 & FirstName & "', '"
sql1 =3D sql1 & PhoneNumber & "', '"
sql1 =3D sql1 & Email
sql1 =3D sql1 & "')"
con.execute(sql1)
'*** get the value inserted
sqlselect =3D "select Clientid from table1 where
sqlselect =3D sqlselect & " FirstName =3D '" & FirstName &
"'and '"
sqlselect =3D sqlselect & " PhoneNumber =3D '" & PhoneNumber
& "'and '"
sqlselect =3D sqlselect & " Email =3D '" & Email & "'"
set rsselect =3D con.execute(sqlselect)
if not rsselect.eof then
Clientid =3D rsselect(0)
end if
set rsselect =3D nothing
'**** insert into table2 all the values
sql1 =3D "insert into Table2 (Siteid, URL, UserName, Password) "
sql1 =3D sql1 & "values ('"
sql1 =3D sql1 & URL & "', '"
sql1 =3D sql1 & UserName& "', '"
sql1 =3D sql1 & Password
sql1 =3D sql1 & "')"
con.execute(sql1)
'**** if no errors then commit else rollback
if con.errors.count <> 0 then
conn.rollbacktrans
else
conn.committrans
end if
----- Original Message -----
From: Mark Collins
To: ASP Databases
Sent: Monday, November 20, 2000 3:23 AM
Subject: [asp_databases] insert data into 2 tables from one form
I need to be able to create a form which will take values and insert
data
into two tables which have a relational link.
Table1: Fields are, ClientID, FirstName, PhoneNumber, Email
Table2: Fields are, SiteID, URL, Username, Password
ClientID and siteID are referentially linked.
I create a form prompting for the above fields, (Less ClientID and
SiteID), then I use a standard sql statement to insert the data into
each
table:
*******************************************************************
sql1 =3D "insert into Table1 (FirstName, PhoneNumber, Email) "
sql1 =3D sql1 & "values ('"
sql1 =3D sql1 & FirstName & "', '"
sql1 =3D sql1 & PhoneNumber & "', '"
sql1 =3D sql1 & Email
sql1 =3D sql1 & "')"
sql2 =3D "insert into Table2 (URL, UserName, Password) "
sql1 =3D sql1 & "values ('"
sql1 =3D sql1 & URL & "', '"
sql1 =3D sql1 & UserName & "', '"
sql1 =3D sql1 & Password
sql1 =3D sql1 & "')"
Set con =3D Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql1
con.Execute sql2
' Done. Close the connection
con.Close
Set con =3D Nothing
****************************************
I then get an error telling me that I cannot update a table with a
referential link - but the crazy thing is that it will update th first
table and not the second in the database. If I change the order the
converse happens.
Am I doing this the right way or am I way off the mark?
---
NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS? Is FREE okay?
Visit EarthWeb for the latest in IT Management, Software Development,
Web Development, Networking & Communications, and Hardware & Systems.
Click on http://www.earthweb.com for FREE articles, tutorials,
and discussions from the experts.
$subst('Email.Unsub')
|
|
|
View Cart
