updating a SQL database from a multiple selection listbox

[Ned Balzer]

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]

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]

Cool, thanks!

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.