473,322 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

updating a SQL database from a multiple selection listbox

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

Aug 28 '06 #1
2 1694
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 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
Aug 28 '06 #2
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
Aug 31 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: theoryboy | last post by:
I'm trying to implement multiple selection functionality in a Tix Hlist using a control-click. I've bound control-click to a function that uses selection_set to add to the selection, but it doesn't...
2
by: DK | last post by:
Hello everybody ! I need little help from Your side. I have form called Form2, and ListBox called List 20. Property of List 20 allow multiple selection. Also I have query called Distribution as...
2
by: Big E | last post by:
I'm using ASP.Net and SQL Server 2000. I have a Listbox that users can select multiple values. Selection Mode = Multiple. The multiple values are associated with one record. I need to insert into...
5
by: Lie | last post by:
Hi all, I have problem in getting selectedindex of multiple listbox selection in a datagrid. I have a listbox with multiple selection mode inside datagrid. In Edit mode, I need to get back all...
2
by: Dolorous Edd | last post by:
Hi, for a program I'm working on I need to be able to drag multiple files between Windows Explorer and a ListBox, in both directions. Implementing the "drag in" was pretty easy, but I can't find...
9
by: Gummy | last post by:
Hello, I created a user control that has a ListBox and a RadioButtonList (and other stuff). The idea is that I put the user control on the ASPX page multiple times and each user control will...
2
by: julie18881 | last post by:
I may be being really stupid here, i have spent the last 3 hours looking round your site and some other for answers to my problem, but have not had much luck (possibly cuase my brain just isn't...
2
by: billa856 | last post by:
Hi, My Project is in MS Access. In that I have one form in which I have some textboxes,comboboxes and listboxes. Now when I select value from 1st combobox(CustomerID) then it wil generate list for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.