updating a SQL database from a multiple selection listbox

Discussion in 'ASP .Net' started by Ned Balzer, Aug 28, 2006.

  1. Ned Balzer

    Ned Balzer Guest

    Hi,

    Can anyone tell me what is the best approach for passing multiple keys
    from a listbox to a SQL update? I prefer to use stored procedures for
    updates. I am using asp.net 2.0 and SQL 2000, soon to upgrade to SQL
    2005.

    I can envision several ways to do it, such as passing a delimited set
    of keys in a string to the sp, which then parses through them and maybe
    puts them into a temp table and performs the update. or is there maybe
    a cooler way to do it, like passing some custom array object to the sp,
    or even somehow creating the temp table in the asp.net code and passing
    that object as a param to the sp?

    I would prefer to avoid using any iterative approach on the SQL side,
    if that's possible.

    Thanks.

    -- Ned
    Ned Balzer, Aug 28, 2006
    #1
    1. Advertising

  2. Ned Balzer

    KJ Guest

    Hello Ned,

    Thanks to SQL Server's XML support, you can dispense with the
    delimited-string idea and proceed using XML. Build a mini XML document
    for your data, for example:

    <root>
    <item name="itemname" value="itemvalue"/>
    <item name="itemname2" value="itemvalue2"/>
    </root>

    Then, send this string as varchar input to your SP. Using the built in
    SP sp_xml_PrepareDocument (see Books Online) to load your xml. Then
    read the values out using OPENXML (see Books Online).

    Your XML data is "shredded" by OPENXML into a table, whence you can
    perform an update using all the input data. In the example above, each
    item tag would shredded into a row, each attribute name (name, value),
    becomes a column, and the values of the attributes are the field values
    for the columns.

    Ned Balzer wrote:
    > Hi,
    >
    > Can anyone tell me what is the best approach for passing multiple keys
    > from a listbox to a SQL update? I prefer to use stored procedures for
    > updates. I am using asp.net 2.0 and SQL 2000, soon to upgrade to SQL
    > 2005.
    >
    > I can envision several ways to do it, such as passing a delimited set
    > of keys in a string to the sp, which then parses through them and maybe
    > puts them into a temp table and performs the update. or is there maybe
    > a cooler way to do it, like passing some custom array object to the sp,
    > or even somehow creating the temp table in the asp.net code and passing
    > that object as a param to the sp?
    >
    > I would prefer to avoid using any iterative approach on the SQL side,
    > if that's possible.
    >
    > Thanks.
    >
    > -- Ned
    KJ, Aug 28, 2006
    #2
    1. Advertising

  3. Ned Balzer

    Ned Balzer Guest

    Cool, thanks!
    KJ wrote:
    > Hello Ned,
    >
    > Thanks to SQL Server's XML support, you can dispense with the
    > delimited-string idea and proceed using XML. Build a mini XML document
    > for your data, for example:
    >
    > <root>
    > <item name="itemname" value="itemvalue"/>
    > <item name="itemname2" value="itemvalue2"/>
    > </root>
    >
    > Then, send this string as varchar input to your SP. Using the built in
    > SP sp_xml_PrepareDocument (see Books Online) to load your xml. Then
    > read the values out using OPENXML (see Books Online).
    >
    > Your XML data is "shredded" by OPENXML into a table, whence you can
    > perform an update using all the input data. In the example above, each
    > item tag would shredded into a row, each attribute name (name, value),
    > becomes a column, and the values of the attributes are the field values
    > for the columns.
    >
    > Ned Balzer wrote:
    > > Hi,
    > >
    > > Can anyone tell me what is the best approach for passing multiple keys
    > > from a listbox to a SQL update? I prefer to use stored procedures for
    > > updates. I am using asp.net 2.0 and SQL 2000, soon to upgrade to SQL
    > > 2005.
    > >
    > > I can envision several ways to do it, such as passing a delimited set
    > > of keys in a string to the sp, which then parses through them and maybe
    > > puts them into a temp table and performs the update. or is there maybe
    > > a cooler way to do it, like passing some custom array object to the sp,
    > > or even somehow creating the temp table in the asp.net code and passing
    > > that object as a param to the sp?
    > >
    > > I would prefer to avoid using any iterative approach on the SQL side,
    > > if that's possible.
    > >
    > > Thanks.
    > >
    > > -- Ned
    Ned Balzer, Aug 31, 2006
    #3
    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. =?Utf-8?B?TGll?=
    Replies:
    5
    Views:
    675
    =?Utf-8?B?TGll?=
    Dec 14, 2004
  2. Chris Kettenbach

    Listbox selection to populate another listbox?

    Chris Kettenbach, Jun 15, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    1,644
    Chris Kettenbach
    Jun 16, 2005
  3. Replies:
    10
    Views:
    24,951
    S. Justin Gengo [MCP]
    Feb 27, 2006
  4. =?Utf-8?B?V2FyYW4=?=

    Multiple Selection in Listbox when Edit the form

    =?Utf-8?B?V2FyYW4=?=, May 4, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    482
    =?Utf-8?B?V2FyYW4=?=
    May 4, 2006
  5. Clive_
    Replies:
    0
    Views:
    280
    Clive_
    Jul 23, 2007
Loading...

Share This Page