Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net Building Controls (http://www.velocityreviews.com/forums/f59-asp-net-building-controls.html)
-   -   listbox and sql stored procedure (http://www.velocityreviews.com/forums/t756867-listbox-and-sql-stored-procedure.html)

John 06-30-2004 03:31 PM

listbox and sql stored procedure
 
Hi,
First of all sorry for my not perfect english.

I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from items and
pass it to stored procedure to delete 7 rolls in my table. Thats simple,
but what if user select 3 or 30 items in listbox? The problem is that I dont
know the number of the parameters, and how to pass them. can I use array
or is there some different solution?

Of course I can take the collection of items and for every item, I can call
stored procedure, but this is no good in performance reason.
Please help me



DalePres 07-02-2004 11:34 AM

Re: listbox and sql stored procedure
 
The most common method of assing an array to SQL Server is to pass it as a
delimited string and parse that string in the stored procedure.

Here's a sample I use in an application where the application numbers (an
integer) that are installed on a server are sent to the SQL Server stored
procedure in a pipe ("|") delimited list stored in a variable
@ServerApplications. This is cut from the middle of the stored proc so
what's not shown is the variable declaration and the start of the
transaction but that should be clear enough.

Hope it helps,

Dale



-- ****************** Save Server Applications ******************
SET @Value = ''
IF LEN(RTRIM(@ServerApplications)) > 0
BEGIN
IF RIGHT(@ServerApplications,1) <> '|'
SET @ServerApplications = @ServerApplications + '|'
WHILE LEN(@ServerApplications) > 1
BEGIN
SELECT @Value = LEFT(@ServerApplications,CHARINDEX('|',
@ServerApplications) - 1)
SET @ServerApplications = SUBSTRING(@ServerApplications, CHARINDEX('|',
@ServerApplications) + 1, LEN(@ServerApplications) - CHARINDEX('|',
@ServerApplications))
IF (ISNUMERIC(@Value)=1) AND (CAST(@Value AS INT) > 0)
BEGIN
-- Save @Value as a server function for this server
INSERT INTO RequestApplications (
RequestID,
LineNumber,
ApplicationNumber
)
VALUES (
@REQ,
@LINE,
CAST(@Value AS INT)
)
-- Get the error and rowcount results of the INSERT
SELECT @ERR = @@ERROR, @RC = @@ROWCOUNT

-- If there was an error, cancel the save and return
IF @ERR > 0 OR @RC <> 1
BEGIN
ROLLBACK TRAN
RETURN -8
END
END
ELSE
BEGIN
-- @Value is invalid. Cancel the save and return an error code.
ROLLBACK TRAN
RETURN -9
END
END
END

"John" <stomss2003@yahoo.com> wrote in message
news:uyiCXdrXEHA.4092@TK2MSFTNGP11.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
>
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from items

and
> pass it to stored procedure to delete 7 rolls in my table. Thats simple,
> but what if user select 3 or 30 items in listbox? The problem is that I

dont
> know the number of the parameters, and how to pass them. can I use array
> or is there some different solution?
>
> Of course I can take the collection of items and for every item, I can

call
> stored procedure, but this is no good in performance reason.
> Please help me
>
>





All times are GMT. The time now is 08:40 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.