March 4, 2004 at 11:22 am
Hi
For my project i am trying to create an online registering system. The users enter all of the required info through the form it is then handled on the server by asp scripts.
I have to tables resident and household
resident table:
----------------------------------------
Personal_ID (PK) | Name | DOB | Household_ID
Household table:
----------------------------------------
Household_ID (PK) | Address | Post Code
The problem i am having is that when i add a new record into the database i have to add details into both tables, I have to increment both the Person_ID and the Household_ID by 1 (as were not allowed to use auto-no.) Which i can do HOWEVER there can be many residents living in the same house. e.g 2 people could both live at the same address and therefore both need the same Household_ID, i am not sure how to do this. Here is my code:
'Increment person_ID column by 1 in resident table
sqlQuery2 = "SELECT MAX(Person_ID) As personID FROM resident"
Set recordSet = connection.Execute(sqlQuery2)
lastRecord = (recordSet("personID")+1)
'Increment household_ID column by 1 in resident table (also used in household table)
sqlQuery3 = "SELECT MAX(Household_ID) As houseID FROM resident"
Set recordSet2 = connection.Execute(sqlQuery3)
lastRecord2 = (recordSet2("houseID")+1)
'Insert values into the tables
sqlQuery1 = "INSERT INTO resident VALUES(" & lastRecord & ", '" & fullName & "', '" & dob & "', " & lastRecord2 & ")"
Set recordSet3 = connection.Execute(sqlQuery1)
sqlQuery4 = "INSERT INTO Household VALUES(" & lastRecord2 & ", '" & addressID & "', '" & fullPostCode & "')"
Set recordSet4 = connection.Execute(sqlQuery4)
Hope this makes sense. Any help would be appreciated.
Thanx
Dan.
March 5, 2004 at 9:18 am
You will need another column (I called it 'rid' ) in the residents table that will be incremented from 1 by one for each household. Each time a new household is entered, rid will begin with the value 1 and be incremented for each resident in the household that is added to the residents table.
I generated these results from the code which follows:
household_id address postcode
------------ ---------------- ----------
1 100 Elm Street 12345
personal_id household_id rid rname dob
----------- ------------ ----- ----------- -----------
1 1 1 Joe 1970-01-01
2 1 2 Jane 1972-01-01
3 1 3 John 1974-01-01
--> BEGIN CODE
/*
drop table household
drop table resident
*/
go
create table household (household_id int PRIMARY KEY, address varchar(50), postcode varchar(10))
create table resident (personal_id int PRIMARY KEY, household_id int, rid int, rname varchar(50), dob datetime)
create unique nonclustered index ix_resident_key on resident (household_id, rid)
go
declare @household_id int, @personal_id int, @rid int
-- One Household row
set @household_id = (SELECT IsNull(Max(household_id) + 1, 1) FROM household)
INSERT household (household_id, address, postcode) VALUES (@household_id, '100 Elm Street', '12345')
-- One row for each resident of the household
--- first person
SET @personal_id = (SELECT IsNull(Max(personal_id) + 1, 1) FROM resident)
SET @rid = 1
INSERT resident (personal_id, household_id, rid, rname, dob)
VALUES (@personal_id, @household_id, @rid, 'Joe', '1/1/1970')
--- second person
--SET @personal_id = (SELECT IsNull(Max(household_id) + 1, 1) FROM household)
SET @personal_id = @personal_id + 1
SET @rid = @rid + 1
INSERT resident (personal_id, household_id, rid, rname, dob)
VALUES (@personal_id, @household_id, @rid, 'Jane', '1/1/1972')
--- third person
--SET @personal_id = (SELECT IsNull(Max(household_id) + 1, 1) FROM household)
SET @personal_id = @personal_id + 1
SET @rid = @rid + 1
INSERT resident (personal_id, household_id, rid, rname, dob)
VALUES (@personal_id, @household_id, @rid, 'John', '1/1/1974')
SELECT * FROM household
SELECT * FROM resident
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply