Solved

Update a many-to-many join table

Posted on 2004-07-31
14
528 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 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