Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Building Controls > listbox and sql stored procedure

Reply
Thread Tools

listbox and sql stored procedure

 
 
John
Guest
Posts: n/a
 
      06-30-2004
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


 
Reply With Quote
 
 
 
 
DalePres
Guest
Posts: n/a
 
      07-02-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Stored Procedure and SQL String???? =?Utf-8?B?VGltOjouLg==?= ASP .Net 5 03-01-2005 07:49 PM
listbox and sql stored procedure John ASP .Net 2 07-09-2004 09:58 AM
listbox and sql stored procedure John ASP .Net Web Controls 2 06-30-2004 07:28 PM
listbox and sql stored procedure John ASP .Net Security 0 06-30-2004 03:32 PM



Advertisments