ASP.NET Discussion - database problem

database problem

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: ASP.NET Discussion
Forum Description: Discussion and chat on ASP.NET related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=21628
Printed Date: 28 March 2026 at 12:37pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: database problem

Posted By: mike7510uk
Subject: database problem
Date Posted: 16 October 2006 at 1:02pm

i have 2 database tables and want the primary key from table 1 to appear in table 2. The tables are connected to two web forms, so in other words, the user enters personal details into form1 which is sent to table1..then the user enters company details into form 2 which is sent to table 2, the trouble is, is that at the moment there is no way of telling which personal details relate to which company details...

any ideas?

Replies:

Posted By: Freon22
Date Posted: 17 October 2006 at 3:22pm

What you need to do is setup a foreign key field in table 2. Then when you add a record into table 1 you get the primary key number and you insert it into the foreign key field in table 2.

So you would do something like this

INSERT INTO table 1 (field1, field2, field2) VALUES (value1, value2, value3); SELECT Scope_Identity()

What we just did was to add a record to table 1 and returned the primary key number from the record that we just added. Now you take that primary key number and add it to the foreign key field in table 2 when you add the record.

Do some google search and here is a link that will help.

http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63 - http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63

Hope this help!

Posted By: mike7510uk
Date Posted: 17 October 2006 at 4:27pm

ok, i think i get the first bit.

How do we now get the primary key number and add it to the foreign key field in table 2?

Posted By: Freon22
Date Posted: 17 October 2006 at 6:00pm

Well there are a few ways, one is the example that I show you.

INSERT INTO table 1 (field1, field2, field2) VALUES (value1, value2, value3); SELECT Scope_Identity()

When you use a insert with a Select Scope_Identity at the end it will return the primary key number for that record. Atleast with sql2005 database if you are using access or someother database you will have to look into what method is used for returning the record number.

Anyway you can then put the id number in a variable and use it when you insert the data into table 2.

Or you can just insert all of the data into both tables at the same time.

I setup a few tables so table_1 has these fields user_id, user_name. table_2 has company_id, user_id, company_name

INSERT INTO table_1 (user_name) VALUES ('Jim');

INSERT INTO table_2 (user_id, company_name) VALUES (Scope_Identity(), 'Help Company')

Now if I run both of these inserts in the same connection it will put the primary key user_id from the record in table_1 into the foreign key field user_id in table_2

Posted By: mike7510uk
Date Posted: 18 October 2006 at 8:55am

Freon22 wrote:

Well there are a few ways, one is the example that I show you.

INSERT INTO table 1 (field1, field2, field2) VALUES (value1, value2, value3); SELECT Scope_Identity()

Anyway you can then put the id number in a variable and use it when you insert the data into table 2.

Or you can just insert all of the data into both tables at the same time.

I setup a few tables so table_1 has these fields user_id, user_name. table_2 has company_id, user_id, company_name

INSERT INTO table_1 (user_name) VALUES ('Jim');

INSERT INTO table_2 (user_id, company_name) VALUES (Scope_Identity(), 'Help Company')

Now if I run both of these inserts in the same connection it will put the primary key user_id from the record in table_1 into the foreign key field user_id in table_2

i have tried this way but am unsure of the correct syntax i should be using. Sorry, im a bit new to this

Posted By: mike7510uk
Date Posted: 18 October 2006 at 9:01am

also, the forms are on 2 different web pages, when the first form is submitted the info goes to table1 of the database,then the response.redirect brings up form2, where the user again enters info, presses submit and sends that data to table 2 in the database.

Posted By: mike7510uk
Date Posted: 31 October 2006 at 9:11am

Freon22 wrote:

Well there are a few ways, one is the example that I show you.

INSERT INTO table 1 (field1, field2, field2) VALUES (value1, value2, value3); SELECT Scope_Identity()

Anyway you can then put the id number in a variable and use it when you insert the data into table 2.

Or you can just insert all of the data into both tables at the same time.

I setup a few tables so table_1 has these fields user_id, user_name. table_2 has company_id, user_id, company_name

INSERT INTO table_1 (user_name) VALUES ('Jim');

INSERT INTO table_2 (user_id, company_name) VALUES (Scope_Identity(), 'Help Company')

Now if I run both of these inserts in the same connection it will put the primary key user_id from the record in table_1 into the foreign key field user_id in table_2

how do i do this bit?

Posted By: michael
Date Posted: 31 October 2006 at 2:29pm

For page one you write a Stored Procedure that inserts the data as required into table one. After the insert statement you do a simple select like

Select Scope_Identity() as myNewPK

In asp, run a query (sql) to get this value and write it to a session, cookie or whatever you prefer.

Then redirect to page 2 and read the cookie there.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: mike7510uk
Date Posted: 01 November 2006 at 9:10am

michael wrote:

For page one you write a Stored Procedure that inserts the data as required into table one. After the insert statement you do a simple select like

Select Scope_Identity() as myNewPK

In asp, run a query (sql) to get this value and write it to a session, cookie or whatever you prefer.

Then redirect to page 2 and read the cookie there.

ah yes, got that to work fine, thank you very much