Hi,
Uses: SQL Server 2000, ASP.NET 1.1;
I've the following tables which has a 1:M relationship within them:
Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)
I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:
LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.
Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?
Eagerly awaiting a response.
Thanks,
The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html
But if you have a max of 3, then just write the proc with 3 parameters (something like):
create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error
begin tran
insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)
declare @.newContactId int
set @.newContactId = scope_identity()
insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null
commit tran
|||Hi Louis,
Thanks for the Response, this cleared my mind and the problem. Thank you again!
sql