Mass Insert :: Prog Loop Form and pass to SP?

Mass Insert :: Prog Loop Form and pass to SP?

am 23.08.2005 16:41:02 von jason

I would appreciate any help in finding a way to do a MASS insert into a
table that reconciles a many-2-many relationship:

TABLE
--------

CREATE TABLE [AlertSelection] (
[AlertSelectionID] [int] IDENTITY (1, 1) NOT NULL ,
[StaffID] [int] NULL ,
[StatusID] [int] NULL ,
[AlertBLN] [int] NULL ,
CONSTRAINT [PK_AlertSelection] PRIMARY KEY CLUSTERED
(
[AlertSelectionID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sql server 7.0
Asp Classic 3.0

How does one programitically loop through a web form that will insert
multiple selections for a particular user and then pass it to the stored
procedure:

STORED PROCEDURE
----------------------------
CREATE Procedure spr_AddStaffAlerts
@StaffID int,
@StatusID int
As
INSERT INTO AlertSelection(StaffID, StatusID, AlertBLN)
VALUES(@StaffID, @statusID, 1)
GO

ASP CODE
---------------
This is the part I need help with:

......
set oConn = GetConnection()

'//I realise I have to loop through a form that has MULTIPLE selections but
not sure of the correct way to to do this and pass to SP..

Maybe something like this?:

For i = Request.Form.Count
counter=counter+1
StaffID=Request.Form.item("StaffID")
StatusID=Request.Form.item("StatusID")
Set oCmd = GetStoredProcedure(oConn,"spr_AddStafflAlerts")
oCmd.Parameters.append oCmd.CreateParameter("@StaffID", adInteger,
adParamInput, 0, StaffID)
oCmd.Parameters.append oCmd.CreateParameter("@StatusID", adInteger,
AdParamInput,0, StatusID)
Next

Is this the correct approach?

Thanks in advance
Jason