listbox and sql stored procedure

Discussion in 'ASP .Net Building Controls' started by John, Jun 30, 2004.

  1. John

    John Guest

    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
     
    John, Jun 30, 2004
    #1
    1. Advertising

  2. John

    DalePres Guest

    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" <> wrote in message
    news:...
    > 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, Jul 2, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John
    Replies:
    2
    Views:
    517
    =?Utf-8?B?RGVlcA==?=
    Jul 9, 2004
  2. =?Utf-8?B?VGltOjouLg==?=

    Stored Procedure and SQL String????

    =?Utf-8?B?VGltOjouLg==?=, Mar 1, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    2,523
    =?Utf-8?B?VGltOjouLg==?=
    Mar 1, 2005
  3. Mike P
    Replies:
    0
    Views:
    3,375
    Mike P
    Jun 19, 2006
  4. John

    listbox and sql stored procedure

    John, Jun 30, 2004, in forum: ASP .Net Security
    Replies:
    0
    Views:
    133
  5. John

    listbox and sql stored procedure

    John, Jun 30, 2004, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    133
    Scott G.
    Jun 30, 2004
Loading...

Share This Page