Stored Procedure that add a record for ech checkbox checked...

[prover]

I have a dropdown list of managers and a series of checkboxes with divisions.

I need a SP that will add a new record to the tblDivMgrs table for each checkbox that is checked.

the table id designed as follows:

tblDivMgrs
DMID (PK)
MgrName
Division

Page EX:

John Smith (Dropdownbox)
Personnel (checked)
Budget (checked)
MIS (checked)
Legal (checked)
Sales
R & D
Warehouse

So under tblDivMgr you'd see:
1 John Smith Personnel
2 John Smith Budget
3 John Smith MIS
4 John Smith Legal

Any sugestions are greatly appreciated!

Rich O

 

[Guest_imported]

when the selected values are captured in the submit page, in asp if u do request("checkboxname&quot u get them as a comma delimited string, which u can store them in an array using split() in VBScript, so loop thru the array and execute the SP, passing same manager value but different dept valus from the loop, which inserts a row for each of the combination.

 

[essa2000]

Dear ;

IF you need a Stored Procedure to do that then It will help you out.

-- First Create this table for your example ;

Create table tblDivMgrs
(
DMID [Int] IDENTITY (1,1),
MgrName [varchar] (255),
Division [varchar] (255)
)

-- Then there is a SP for you ;


Create Procedure InsertDivisionManagers
@P_ManagerName varchar(255) ,
@P_Divisions varchar(1000)

AS

BEGIN


DECLARE @L_NewDivision varchar(255)
DECLARE @L_NoofCommas int
DECLARE @L_Count int
Declare @L_SQL nvarchar(4000)

SET @L_SQL = ''
SET @L_NewDivision = ''
SET @L_Count = 0

SET @L_NoofCommas = (Len(Ltrim(Rtrim(@P_Divisions))) - Len(Replace(Ltrim(Rtrim(@P_Divisions)) , ',' , '') )) + 1

WHILE @L_Count < @L_NoofCommas

BEGIN

SET @L_Count = @L_Count + 1

SET @L_NewDivision =
CASE
WHEN Charindex( ',' , @P_Divisions ) > 0
THEN left(@P_Divisions , Charindex( ',' , @P_Divisions ) - 1)
WHEN Charindex( ',' , @P_Divisions ) = 0
THEN @P_Divisions
END


PRINT @L_NewDivision -- Optional

SET @L_SQL = 'INSERT INTO tblDivMgrs (MgrName , Division) Values ( '''+ @P_ManagerName + ''', ''' + @L_NewDivision + ''')'

Print @L_SQL

Exec (@L_SQL)

SET @P_Divisions = Right( @P_Divisions , Len(@P_Divisions) -
Len(left(@P_Divisions , Charindex( ',' , @P_Divisions)))
)

PRINT @P_Divisions -- Optional

END

END


-- Execute SP in this way ; You should send divisions in this comma delimated string, you can create this string at VB end using loop thru the control array of checked check boxes.

Exec InsertDivisionManagers
'John Smith , 'Personell,Budget,MIS,Legal'


-- See the results.

Select * from tblDivMgrs


Print Statements will explain you the flow of this SP.



Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com