Solved

Update a many-to-many join table

Posted on 2004-07-31
14
529 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Suggested Solutions

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
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

733 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