July 24, 2008 01:11am pdt

1. TheLearn... 101,600
2. Dhaest 97,795
3. apeter 84,345
4. jaime_ol... 71,485
5. jpaulino 53,704
6. jcoehoorn 44,175
7. emoreau 42,468
8. angelIII 40,550
9. Fernando... 31,654
10. naspinski 30,000
11. GreenGhost 28,795
12. PockyMaster 28,698
13. TimCottee 25,359
14. gregoryy... 23,000
15. dweppenaar 22,050

1. TheLearn... 289,635
2. Dhaest 149,808
3. jaime_ol... 111,772
4. jpaulino 94,934
5. angelIII 91,855
6. Fernando... 91,181
7. gregoryy... 91,132
8. Idle_Mind 90,105
9. apeter 84,345
10. emoreau 82,387
11. Sancler 68,120
12. VBRocks 67,430
13. jaiganes... 57,145
14. jcoehoorn 52,550
15. surajguptha 44,050

02.17.2008 at 12:04PM PST, ID: 23169909

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

4.6

VB.NET 2008 TableAdapterManager - Update Order

Zones: .Net Editors & IDEs, Microsoft Visual Basic.Net, Visual Studio 2008

I have two related tables InvoiceHeader and InvoiceDetail.
They are related as a one to many with Constraints on and Cascades on.
I am using the TableAdapterManager UpdateAll method.

The InvoiceDetail contains:
ID (PK)
InvoiceHeaderID
ItemID
And while the PK is on the ID, I have a Unique Contraint set on the InvoiceHeaderID & ItemID combo.

Here is the problem. If I enter in the InvoiceDetail Item1, Item2, Item3 and save it. Then go back in and Delete Item1, and Enter Item1 again, and then Save it I get this error:

Violation of UNIQUE KEY constraint IX_tblInvoiceDetail. Cannot insert Duplicate key in object tblInvoiceDetail.
The statement has been terminated.

I tried changing the TableAdapterManager Update Order to UpdateInsert but that changes nothing. I would assume that I need to first do a Delete, but to my surprise there does not seem to be that option.

Where do I go from here?

Start your free trial to view this solution

Question Stats

Zone: Programming

Question Asked By: Jess31

Solution Provided By: jake072

Participating Experts: 3

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
Automotive
New Net Users
New Users

Software
Digital Music
Gaming World
Home Security

Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Displays / Monitors
Handhelds / PDAs
Components
Peripherals
Laptops/Notebooks

Servers
Misc
Apple
Embedded Hardware
Networking Hardware

Storage
Desktops
New Users

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMware
Misc
Web Development

OS
CYGWIN
Voice Recognition
Virtualization
Message Queue
Quality Assurance
Security
Firewalls

MultiMedia Applications
Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals
Devices

Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
Web Computing
WebApplications
IDS
Vulnerabilities
Email Clients
File Sharing

Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Consulting
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMware

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel
Automation

Algorithms
Game
Signal Processing
Project Management
Open Source
Database

Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Web Services

Images
Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration

WebApplications
Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Web Computing

Broadband
Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Lounge
Business Travel
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles
Automotive

Community Support

Suggestions
New to EE
New Topics
CleanUp

Announcements
General
Feedback

Input
EE Bugs

02.19.2008 at 09:54AM PST, ID: 20930734

Rank: Sage

graye:

It sounds like the cascade option isn't set for "cascade on delete"

02.19.2008 at 10:52AM PST, ID: 20931339

Jess31:

When I go into Relation dialog screen - these options are set:
Both Relation and Foreign Key Constraint is checked

Update Rule: Cascade
Delete Rule: Cascade

02.20.2008 at 04:40AM PST, ID: 20937191

Rank: Genius

TheLearnedOne:

Actually, that problem doesn't sound like a very easy one to get. What are the circumstances leading up to the exception?

Bob

02.20.2008 at 05:53AM PST, ID: 20937716

Rank: Guru

jake072:

Can't you ask the TableAdapterManager to update the rows with deletions first, then do the rest?

Jake

02.20.2008 at 06:45AM PST, ID: 20938226

Jess31:

Bob,
Actualy I got it 3 minutes into testing. All it takes is say there are three Items:
Organges
Apples
Bananas

I erase top row, Oranges. Then I realize that I didn't want to erase it and I add Oranges, so now the recordset is made up of
Oranages <deleted>
Apples
Bananas
Oranges

02.20.2008 at 06:47AM PST, ID: 20938247

Jess31:

jake072:
your mean to process the Deletetions first? Well that is exactly what I would like to do. And I don't know if this is possible and if so how. I see only an option for Insdert then Update or Update then Insert, both of which process Deletetion last.

02.20.2008 at 10:51AM PST, ID: 20940638

Rank: Guru

jake072:

Jess31,

To Update just the Deleted rows, assuming your using a DataAdapter and DataSets, then you go:

Dim drDeletions As DataRow() = [DataSet].Table([TableName]).Select("", "", DataViewRowState.Deleted)

[DataAdapter].Update(drDeletions)

Voila! This will update ONLY the Deletions.

You should be able to subsequently do a simple .Update to do the rest, or if you want, you can select rows as above to update specific RowStates as needed.

Jake

Accepted Solution

02.20.2008 at 11:32AM PST, ID: 20940984

Jess31:

As I pointed out at the beggining, I'm using the TableAdapterManager for this - cause it (TAM) also wraps all of it into a Transaction. So I need to be able to get into this Transaction. And also what would happen when the TableAdapterManager tries Deleteing these same rows again - while normaly I can't see a problem, wouldn't this cause an error and the resetting to the Transaction?

02.20.2008 at 12:11PM PST, ID: 20941369

Rank: Guru

jake072:

Sorry about that. Should've paid more attention.

From what I can see if you are using the TableAdapterManager, you don't seem to have a choice.

Never used the TableAdapterManager before... If it's giving you so many troubles, perhaps you could use DataAdapters instead? I use DataAdapters and my own Transaction objects myself, which I could help you with. Can't see how you're going to fix this otherwise...

Jake

02.20.2008 at 10:25PM PST, ID: 20945457

Jess31:

Ok. I tried your suggestion and it seems that it will work just fine.
My only challange now is how I can wrap the TableAdapter.Updates inside a Transaction. I looked for some example but I have not found yet anything that seems to apply. I am not establishing a Connection since that allready exists, I'm just calling the TA for the Updates/Inserts.

02.21.2008 at 05:47AM PST, ID: 20947616

Rank: Guru

jake072:

Your not establishing a connection?

Surely you are! When you start the connection, start a new transaction object and then do connection.Open. You'll have to remember to COMMIT the transaction though, before you close the Connection. There's some good examples in the MSDN Library, or I can help further if you need.

Jake

02.22.2008 at 10:30AM PST, ID: 20959990

Jess31:

Let me say that you have been very helpful with your insightful and very to the point help.

Here to explain better my situation.
I have a Form with some DataSet/TableAdapter/BindingSource that were created by the Designer. The forms has a DataViewGrid and other Textboxes that get filled with data by calling a Fill method of the TableAdapter when coming into the Form.

After changes are made, and if the Save button is clicked then I called the TableAdapters Update method.

So I'm sure there are connections. But I don't know where they are, how to get at them, etc.

02.22.2008 at 03:37PM PST, ID: 20962746

Rank: Guru

jake072:

Jess31,

It would be pointless for me to guess at what your connections may be named, etc., because you might have changed them.

What I would suggest, if you'd like to try my method, is to open the Designer.vb file, and do a search for SQLConnection (or whatever type of DB you are connecting to), and then you will find your connection name.

Then you can go from there in your regular code file.

Let me know if you need more help,

Jake

02.23.2008 at 04:27PM PST, ID: 20967534

Jess31:

I'm not understanding. I know that I can get my connection string this way:
MyProject.My.Settings.MyProjectConnectionString
But I'm not sure ths is what you are referring to.

02.25.2008 at 06:20AM PST, ID: 20975413

Rank: Guru

jake072:

What kind of DB are you connecting to?

Jake

02.25.2008 at 08:31AM PST, ID: 20976884

Jess31:

SQL Server 2000

02.25.2008 at 09:58AM PST, ID: 20977620

Rank: Guru

jake072:

Ok,

As I mentioned before I've never personally used a TableAdapter, but I have used normal DataAdapters.

To start this off, I would right-click your toolkit under the Data Tab, and add all SQL Data Components (.NET Tab, System.Data.Sql.

Now, to start, simply drag a DataAdapter on your form, and follow the wizard. It will create the SQLConnection object for you, as well as typed datasets if you ask it to.

Once you've got that all done, to do an update, you'll follow these steps:

Create SQLTransaction object
assign it to the SQLConnection object.

Open your connection
run the update via DataAdapter.Update() <- here is where you can use the code above to get only deleted rows.
Commit the transaction
Close the connection.

Let me know if you need more help, but the MSDN help should be able to guide you through, as well as the wizards.

Jake

02.26.2008 at 10:31AM PST, ID: 20987178

Jess31:

Ever since you gave me the code for extracting the Deleted/Modfied/Inserted, etc. I have been trying various things, and managed (I think, still need to test) get it working this way:
When need to save:
-Create Transactionscope
-Try
-Establish Connection
-Create SQL Command object
-Updated using stored procedures
-Complete Transaction
-Catch
This is the high level logic.

I was trying to do it the way in your last message, for one it would be easier since I could leverage off of adapter methods...
But (are you using vs 2008?) I could not find DataAdapter or even Toolkit (unless you mean Toolbox in which case I still don't find DA in Toolbox)

02.26.2008 at 11:01AM PST, ID: 20987482

Rank: Guru

jake072:

It's actually a SQLDataAdapter, available when you choose to add items from System.Data.SqlClient [Or just Sql can't remember, and I'm not at my dev machine).

Jake

02.28.2008 at 10:37AM PST, ID: 21006715

Jess31:

jake072,
I have taken what you taught me here but applied it to Table Adapter and came up with these few lines of code which seems to work. Thank you so much for your persistent help.
I include the code here, if you can have a look and tell me if this looks right to you or if you have any comments I would love to hear.

                       
         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

           
           
                     TblSupplyItemBindingSource.EndEdit()
        TblVendorSupplyItemBindingSource.EndEdit()
        Using tr As New Transactions.TransactionScope
            Try
                If Me.SupplyItemViewEditDataSet.tblSupplyItem.GetChanges.Rows.Count > 0 Then
                    TblSupplyItemTableAdapter.Update(Me.SupplyItemViewEditDataSet.tblSupplyItem)
                End If
                If Me.SupplyItemViewEditDataSet.tblVendorSupplyItem.GetChanges.Rows.Count > 0 Then
                    TblVendorSupplyItemTableAdapter.Update(Me.SupplyItemViewEditDataSet.tblVendorSupplyItem.Select("", "", DataViewRowState.Deleted))
                    TblVendorSupplyItemTableAdapter.Update(Me.SupplyItemViewEditDataSet.tblVendorSupplyItem.Select("", "", DataViewRowState.ModifiedCurrent Or DataViewRowState.Added))
                End If
                tr.Complete()
            Catch ex As Exception
                MsgBox(ex.Message)
                tr.Dispose()
            End Try
        End Using
        Exit Sub

           
         

Open in New Window

02.28.2008 at 12:51PM PST, ID: 21007899

Rank: Guru

jake072:

Jess31,

That looks great, glad I could help.

Is there anything else?

Thanks,

Jake

02.28.2008 at 02:41PM PST, ID: 21009011

Jess31:

Thanks again, I'm sure there is more, but I will open new question for that and meanwhile close this one.

20080236-EE-VQP-29 / EE_QW_2_20070628