Solved

Update a many-to-many join table

Posted on 2004-07-31
14
531 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

624 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