Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 2010 > BOOK: Professional Visual Basic 2010 and .NET 4
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Professional Visual Basic 2010 and .NET 4
This is the forum to discuss the Wrox book Professional Visual Basic 2010 and .NET 4 by Bill Sheldon, Billy Hollis, Kent Sharkey, Gaston Hillar, Rob Windsor, Jonathan Marbutt; ISBN: 9780470502242
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional Visual Basic 2010 and .NET 4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 2nd, 2011, 08:03 PM
Registered User
 
Join Date: Mar 2011
Posts: 17
Thanks: 4
Thanked 1 Time in 1 Post
Default Exception on submit changes in LINQ to SQL

Hi,

I entered the code for InsertTitle() and, when execution gets to dc.SubmitChanges(), I get an exception "Cannot add an entity with a key that is already in use." When I use the downloaded WROX project, there is no exception.

When I paste the WROX code into my project, there is an exception. When I paste my code into the WROX project, there is no exception.

The only difference I can see between the projects is that the WROX dbml has only the titles table in it. My dbml also contains the stores and sales tables including the relationships. It looks like those relationships are causing some confusion.

The reason my dbml file contains the other tables is because I'm using one project for all the sample code. I figured that this is what the typical LINQ project would need. But what I see here suggests that, if you want to insert to a table in this way, you need a separate dbml file for each one. Pretty limiting.

BTW, the insert that is performed within a transaction works, but that uses an insert query. Anyone know what might be going on here?

Thanks,

mangel
Reply With Quote
  #2 (permalink)  
Old April 3rd, 2011, 11:21 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,948
Thanks: 79
Thanked 1,555 Times in 1,532 Posts
Default

Quote:
But what I see here suggests that, if you want to insert to a table in this way, you need a separate dbml file for each one. Pretty limiting.
That's certainly not the case. You can have many tables in a dbml file.

How are the other tables linked? And how does your code look for this example? And can you show the database table schema? (I don't have the book).

Cheers.

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #3 (permalink)  
Old April 3rd, 2011, 03:01 PM
Registered User
 
Join Date: Mar 2011
Posts: 17
Thanks: 4
Thanked 1 Time in 1 Post
Default

Quote:
Originally Posted by Imar View Post
That's certainly not the case. You can have many tables in a dbml file.

How are the other tables linked? And how does your code look for this example? And can you show the database table schema? (I don't have the book).

Cheers.

Imar
That's what I thought. I later tried to create a second dbml with the titles table in it alone, but ran into ambiguity issues. So I know one dbml should work.

I created a png file of the schema but I can't figure out how to post it. I see that there is an img tag but my screen says it is off. How can I get that to you?

Anyway, to create it I just dragged the three tables (titles, stores, and sales) from the database reference to the design surface. The relationships appeared as they were in the database. An arrow from the store to the sale table and another arrow from the title to the sale table. There is also the GetAuthorsByStates method included. That is it.

P.S. You should change your image to the newer book. But it doesn't show your face. Blame WROX......

Last edited by mangel; April 3rd, 2011 at 04:50 PM.
Reply With Quote
  #4 (permalink)  
Old April 4th, 2011, 02:54 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,948
Thanks: 79
Thanked 1,555 Times in 1,532 Posts
Default

Hi there,

I don't think you can post images here as a regular contributor. You could post it on an external site and then post a link here.

Maybe you can also post a zipped version of your project somewhere on-line so we can take a look at the actual source and database?

Quote:
P.S. You should change your image to the newer book. But it doesn't show your face. Blame WROX......
I know; that's why I still use the old one as that one has my face on it. I am not too sad that my mug shot is no longer on the cover though..... ;-)

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #5 (permalink)  
Old April 4th, 2011, 11:14 AM
Registered User
 
Join Date: Mar 2011
Posts: 17
Thanks: 4
Thanked 1 Time in 1 Post
Default ftp

Can I do an anonymous ftp to your website? If so, what steps would I take? I have FileZilla.

Thanks,

mangel
Reply With Quote
  #6 (permalink)  
Old April 4th, 2011, 11:21 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,948
Thanks: 79
Thanked 1,555 Times in 1,532 Posts
Default

Nope, but you can send me a message through my contact page: http://imar.spaanjaars.com/contact

I'll reply so you know my e-mail address.

If you want me to look at it, bring down the source of the application to the bare minimum needed to demonstrate the problem. E.g. please don't let me wade through hundreds of unused files, pages, code, tables or what have you.....

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #7 (permalink)  
Old April 4th, 2011, 02:26 PM
Registered User
 
Join Date: Mar 2011
Posts: 17
Thanks: 4
Thanked 1 Time in 1 Post
Default

Hi Imar,

I believe I know what is going on but I don't know how to fix it.

The book includes sample code to add two titles within a transaction. A few examples later it shows sample code to add a title not in a transaction. If the transaction is successful, the second sample code works. If the transaction is not successful, maybe because a title had already been added, the second sample code fails. Also, if I reverse the order of the of execution, the non-transaction add works unless its title had already been added. Here is the code:

Code:
Module Main
    Dim dc As New PubsDataContext

    Sub Main()
        Dim Titles As Table(Of title) = dc.titles
        Console.WindowWidth = 120
        AddTitleTx()
        AddTitle()
    End Sub

    Private Sub AddTitleTx()
        Using TxScope As New TransactionScope
            Try
                Dim Title1 As New title With
                    {
                        .title_id = "EEEE",
                        .title = "Professional XML",
                        .type = "Programming",
                        .pubdate = CDate("April 15, 2010")
                    }
                dc.titles.InsertAllOnSubmit({Title1})
                Dim Title2 As New title With
                    {
                        .title_id = "FFFF",
                        .title = "Professional VB 2010",
                        .type = "Programming",
                        .pubdate = CDate("June 15, 2010")
                    }
                dc.titles.InsertAllOnSubmit({Title2})
                Console.WriteLine("Before Insert: {0} Titles", dc.titles.Count)
                dc.SubmitChanges()
                Console.WriteLine("After Insert: {0} Titles", dc.titles.Count)
                TxScope.Complete()
            Catch ex As Exception
                'This will happen on the second+ execution of the transaction because the title had already been added.
                Console.WriteLine("Error: {0}", ex.Message)
                TxScope.Dispose()
            Finally
                Console.WriteLine(vbCrLf)
                Console.WriteLine("Press Enter to exit.")
                Console.ReadLine()
                GC.Collect()
            End Try
        End Using
    End Sub

    Private Sub AddTitle()
        Try
            Console.WriteLine("Now for sub Addtitle() ************" & vbCrLf & "Titles before insert: {0}", dc.titles.Count)
            Dim NewTitle As New title
            With NewTitle
                .title_id = "XXXX"
                .title = "Some New Title"
                .type = "test"
                .pubdate = New DateTime(2010, 1, 12)
                .notes = "Added via LINQ"
                .price = 50
            End With
            dc.titles.InsertOnSubmit(NewTitle)
            Console.WriteLine("Titles after insert, but before submit: {0}", dc.titles.Count)
            dc.SubmitChanges()
            Console.WriteLine("Titles after insert, but before submit: {0}", dc.titles.Count)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            Console.WriteLine(vbCrLf)
            Console.WriteLine("Press Enter to exit.")
            Console.ReadLine()
        End Try
    End Sub

End Module
I suspect that there is something about the transaction that is sticking around in memory if it isn't successful. My experience with transactions is that, if no exception occurs, you commit it; otherwise, it's reversed. I don't see anything like that for a transaction scope in LINQ.

The error message says here: "Cannot insert duplicate key in object...." This is different from first message. There are actually two issues and I know how to cause both. But I want to solve the transaction one first and then the other.

Thanks,

mangel

Last edited by mangel; April 4th, 2011 at 03:00 PM.
Reply With Quote
  #8 (permalink)  
Old April 4th, 2011, 03:06 PM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,948
Thanks: 79
Thanked 1,555 Times in 1,532 Posts
Default

Quote:
I suspect that there is something about the transaction that is sticking around in memory if it isn't successful.
Not the TransactionScope (as it's disposed off) but the data context. If I understand your problem correctly, I think this all makes sense. What failed the fiirst time (and caused the transacton to be rolled back) will fail again the second time as the changes are still part of the data context. So calling SubmitChanges a second time will try again to save the same, faulty, changes.

You could recreate the data context or manually revert the changes: http://graemehill.ca/discard-changes...ql-datacontext


Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
The Following User Says Thank You to Imar For This Useful Post:
mangel (April 4th, 2011)
  #9 (permalink)  
Old April 4th, 2011, 04:19 PM
Registered User
 
Join Date: Mar 2011
Posts: 17
Thanks: 4
Thanked 1 Time in 1 Post
Default Issue Solved

Hi Imar,

Thanks for your last reply. I know now that the best way to work with the LINQ data context is to instantiate, use, and then dispose of it each time. I put each of the samples provided by the book into a separate procedure. In each one I instantiate the data context, do what the sample code says, and then dispose of it by calling its Dispose method.

I can't thank you enough for sticking with me on this.

Thanks again,

mangel
Reply With Quote
  #10 (permalink)  
Old April 5th, 2011, 03:19 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,948
Thanks: 79
Thanked 1,555 Times in 1,532 Posts
Default

Hi mangel,
Quote:
I know now that the best way to work with the LINQ data context is to instantiate, use, and then dispose of it each time.
Well, it depends..... ;-)

For Web Application this is certainly so. You should create an object context per request and try to limit its scope and lifetime.

For desktop application you may want to keep the contexts around longer. There's a performance overhead associated with setting it up. Additionally, if you keep the context around and query the same object(s) often, you get caching benefits. So, trashing the context with each method call may not be an ideal situation.

In your situation, you could keep the context around and only recreate it when the transaction fails. E.g. in your Catch block you can reassign dc a value of New PubsDataContext which means you start with a fresh context without data. Alternatively, you can wrap the entire data context in something like a Repository that handles stuff like this. More work and slightly more complex, but may be beneficial in the long run.

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
The Following User Says Thank You to Imar For This Useful Post:
mangel (April 5th, 2011)
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linq to SQL and the Entity Framework marko_ln BOOK: ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008: Problem Design Solutio 4 February 25th, 2010 10:20 AM
LINQ to SQL or not? gvdamn BOOK: Professional DotNetNuke Module Programming ISBN: 978-0-470-17116-5 2 August 20th, 2009 05:08 AM
LINQ to SQL bakm04 BOOK: Professional C# 2008 ISBN: 978-0-470-19137-8 0 August 17th, 2009 04:07 PM
Linq: 'Specified cast is not valid' error exception. mksingh Visual Studio 2008 2 February 4th, 2009 10:06 PM
LINQ to SQL in TheBeerHouse zhoux BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 October 1st, 2008 07:37 AM



All times are GMT -4. The time now is 03:55 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.