Solved

Update a many-to-many join table

Posted on 2004-07-31
14
526 Views
Last Modified: 2012-05-05
Hi,

I'm up against a simple difficulty I didn't think of until I tried to do it. I've got a many-to-many relationship which is implemented with a "join" table (two columns, FKs from the two linked tables). I've got my client-side checkbox list working, but what's the best way to update the table to what the user has selected (in a stored procedure)? This must be a common scenario.

The thought I had was to pass two params;
@table1_Id (self-explanatory), and
@selectedValues -a comma-separated varchar of all the values selected in the checkbox list, i.e. all the table2 Ids.

But I don't know how to update the join table in TSQL with this. Could someone provide a real solution to this outline solution, or even beter tell me what is good practice for this scenario?

Thank you.
0
Comment
Question by:sumo_the_cat
  • 8
  • 6
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685072
>>The thought I had was to pass two params;
@table1_Id (self-explanatory), and
@selectedValues -a comma-separated varchar of all the values selected in the checkbox list, i.e. all the table2 Ids.<<

This is the way that most people do it.  Search for array or delimited list in the Topic Area and you will find many solutions.

Since my company is committed to Xml, we have opted for a different alternative using OpenXml.  One advantage of this solution is that you do not have to parse a delimited list or have multiple inserts, but rather just one single insert for all the multiple values.  

Let me know if you need examples using either option.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685113
Yes, I'd love to learn to do this with XML. The thing which made me ask the q was that I didn't want to do multiple inserts (ugly). I'm using ASP.NET as the client application for this database, so it ought to be able to generate some XML from the CheckBoxList control...?
Peter.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685156
I'm looking up OpenXml on the net, but could you give me a syntax example for this scenario? The other problem is how to generate the XML on the client. I can't do either of these things!
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685177
Could I generate the XML manually, like this? Just loop through the checkBoxList and add a new <item> each time I come accross a selected checkbox?
<root>
      <item>
            <id>x</id>
      </item>
</root>
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 11685345
>>Just loop through the checkBoxList and add a new <item> each time I come accross a selected checkbox?<<
You could certainly do it that way.  In any case, supposing your Xml looks like this:
<IDs>
   <ID>123456</ID>
   <ID>654321</ID>
   <ID>111111</ID>
   <ID>666666</ID>
</ID

And your Stored Procedure looked like this (untested):

Create Procedure usp_Test
                    @IDList text
AS

Declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @IDList

--INSERT     Table2 (ID)                        -- Uncomment this line when you are getting the right results and ready to insert.
Select       ID
From      OpenXML (@idoc, 'IDs\ID', 2) With (ID  int '.')

EXEC sp_xml_removedocument @idoc

Than you would execute the Stored Procedure as follows:

exec usp_test '<IDs>
                            <ID>123456</ID>
                            <ID>654321</ID>
                            <ID>111111</ID>
                            <ID>666666</ID>
                      </ID'

As you can see this very powerful as you can now modify the Xml to include other values that you need to pass.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685514
Great! Thanks.
My generated XML looks like this:
<values><id>1</id><id>3</id><id>7</id></values>

The relevant part of my sproc looks like this:
---------------------------------------------------------------
Declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @selectedValues

INSERT INTO dbo.[JoinTable] (Table2_Id)                        
Select       [id]
From     OpenXML (@idoc, 'values\id', 2) With ([id]  int '.')

EXEC sp_xml_removedocument @idoc
---------------------------------------------------------------
but I'm getting this SQL error:
XML parsing error: Unexpected character in query string. values-->\<--id

Pete.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685543
Change this:
From     OpenXML (@idoc, 'values\id', 2) With ([id]  int '.')

To:
From     OpenXML (@idoc, 'values/id', 2) With ([id]  int '.')
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685545
P.S. My code was wrong.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685547
Should be forward slash with leading slash:
---------------------------------------------------------------
From     OpenXML (@idoc, '/values/id', 2) With ([id]  int '.')
---------------------------------------------------------------
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685558
You do not need the leading forward slash, on the other hand it does not hurt.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685565
Jinks! I'm now trying to work out how to add the other column.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685579
Got it - perfect! Thank you so much. I've really learnt something useful and elegant.
Peter.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685591
>> I'm now trying to work out how to add the other column.<<
Feel free to post all your input parameters for this stored procedure, the actions to be taken (Insert and table structure) and I can make suggestions.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685645
Nah, I was just being thick. Thanks again!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question