Hi All,
I am using asp. I have a series of Hidden Fields that need to
be inserted
into a table in my SQL Server 2000 db. The table stores the
ID (PK),
BookingID (FK), Firstname, Surname, PickUp Address and
DropOff Address of
Passengers who have booked a vehicle with the Chauffeur
Company I am
designing the web app for. This table is a child table of the
'Bookings'
table which holds BookingID, BookingDate, BookingTime
etc.etc. The values
for the child table are held in the series of hidden fields.
The hidden fields are:
Firstname1, Firstname2, Firstname3, Firstname4, Firstname5,
Firstname6,
Surname1, Surname2, Surname3, Surname4, Surname5, Surname6,
PickUp1, PickUp2, PickUp3, PickUp4, PickUp5, PickUp6,
DropOff1, DropOff2, DropOff3, DropOff4, DropOff5, DropOff6
The number of passengers can vary between 1 to 6 so some of
these hidden
fields can sometimes contain empty values.
I originally stored this data in the main Bookings table with
each Passenger
Name, Pickup Address etc as a seperate column but as this
violates the First
Normal Form I changed the structure to include this new child
table.
My problem is I am not sure how is best to handle the INSERT.
It will be
part of a Stored Proc. that firstly inserts to the Bookings
table, grabs the
BookingID for it to be stored as FK in the child table and
then Inserts the
hidden field values into the child table. I am not sure
though, whether to
insert the values as seperate statements as every hidden
field for every
passenger has to be named differently eg. Firstname1,
Firstname2, etc. so
should I do something like:
INSERT INTO TheChildTable (BookingID, Firstname, Surname,
PickUp, DropOff)
VALUES ( @BookingID, @Firstname1, @Surname1, @PickUp1,
@DropOff1)
Then
INSERT INTO TheChildTable (BookingID, Firstname, Surname,
PickUp, DropOff)
VALUES ( @BookingID, @Firstname2, @Surname2, @PickUp2,
@DropOff2)
And so-on, all the way up to Passenger 6 (bearing in mind
that there could
only be 1 or 2 passengers and the rest of the hidden fields
could be empty.
This would then mean that I have empty rows in the table all
linked to a
BookingID. This, I don't want). I do have a hidden field that
holds the
number of passengers that are to be inserted, so maybe this
could somehow be
utilized to control the number of Inserts??
Although this method is flawed, I cannot think of another way
to do this
Insert, given the circumstances around it. Can anyone offer
any advice
please.
1
Reply
AdChoices