Friday, March 9, 2012

Need a way to handle multiple selections

I have a webform that lists all items (codes) on the left and selected items (codes) on the right. A user selects an item on the left and clicks a button to move it to the right side. An update changes the bit from 0 to 1. This uses the bit column in the table to determine what is listed on the left (0) or right (1) sides.

Then I can filter in my stored procedure on the bit column WHERE (dbo.tblCodes.CodeSelect = 1)

My problem with this is that if two or more users are doing this process on different sessions, they can trip over each others selections.

I'm hoping someone has a suggestion on how I might avoid the users having this problem.

Arent you storing the selections into a temp table before you update the actual tables?

|||

No, the user selects a code from a listbox on the left and clicks a button. This button calls a stroed procedure that changes the bit column of that code to 1 and then on postback it is moved to the right side. Once they have made all their selections another stored procedure is called to get the data based on their selection.

The update procedure looks like this:

ALTER PROCEDURE [dbo].[CodeSelectOne](@.SelectOneint)
AS UPDATE dbo.tblCodeLookup
SET [CodeSelect] = 1
WHERE (Code_ID = @.SelectOne)

I think my issue is in the next procedure that returnes the data to the user. That procedure uses a where clause WHERE tblCodeLookup.CodeSelect = 1, I think this is where I run into trouble. If another user changes the selected codes before this first users data is returned then they will not get there expected data.

Any suggestions?

|||

What if I had a multiselect listbox and put a comma delimited list of codes in a session varaible and then pass that to my stored procedure in a correlated subquery using an IN?

Any thoughts on this?

No comments:

Post a Comment