Hi,

I am having problem with inserting multiple rows in one table at one time submit.

I Have a ASP Form with

First Name
Last Name

Organization 1
Organization 2
Organization 3

I like to insert this form into one table. Table should look like this..

         FName LName Organization
------------------------------------------------------
Row 1 - First Name Last Name Organization1
Row 2 - First Name Last Name Organization2
Row 3 - First Name Last Name Organization3

I need a code to do this functionality. Much appreciated you help.

Thanks
Raj



Mohanraj

The way you are trying to do this needs to have three insert statements. However, you are not creating a very efficient database this way.

The best way to do this is to create separate tables, one for personal information, last name, first name, etc. and then create another table with organizations. Of course, unless you want to just create a table with three organization columns, Org1, Org2, and Org3. Then you will be limited to only three orgs if that's what you want.

What you can do with the tables below is when you insert into the personal info table, grab the @@identity and use it to insert the organizations into the Organizations table.

SQL = "Set nocount off;INSERT into PersonalInfo (FirstName, LastName) Values ('" & varFirstName & "', '" & varLastName & "');Select PersonalID = @@Identity;Set nocount on;"

Set oRS = oConn.Execute(SQL)
varPersonalID = oRS("PersonalID")

i = 1
Do Until i = 3

If len(varOrg & i) > 0 Then
SQL = "Insert into Organizations (PersonalID, Organization) VALUES (" & varPersonalID & ", '" & varOrg & i "');"
oConn.Execute(SQL)
i = i + 1
Loop

This way you're not limited to organizations. I don't see any other way than to loop through the records but I'm no expert.


CREATE TABLE [PersonalInfo] (
    [PersonalID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_PersonalInfo] PRIMARY KEY CLUSTERED
    (
        [PersonalID]
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Organizations] (
    [OrgID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [PersonalID] [int] NULL ,
    [Organization] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [FK_Organizations_PersonalInfo] FOREIGN KEY
    (
        [PersonalID]
    ) REFERENCES [PersonalInfo] (
        [PersonalID]
    ) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO


Hope this helps.

Richard