Solved

LINQ statement does not updated record in database

Posted on 2013-05-15
8
511 Views
Last Modified: 2013-05-16
Using LINQ, I have created a query to locate a record in a table.  The record is successfully found.  I have verified that in debug.  I then need to update one field on that record called Accepted.  I need to set this field to "Y".  I perform a submitchanges and it acts like everything is fine with no problems.  I go back to my database and the record is left unchanged.  Can someone steer me in the right direction as to what the problem may be?  Any help is greatly appreciated.

DBDataContext dc = new DBDataContext();

                    Referral myAddNewReferral = new Referral();


                    // code for Accept button
                    string mguid;
                    
                    mguid = oRow.Items[0].Value.ToString();
                    Guid resultguid = new Guid(mguid);


                    var ReferralResult = from ReferralData in dc.Referrals
                                         where ReferralData.REFGUID == resultguid
                                        select new
                                        {
                                            ReferralData.RefReason,
                                            ReferralData.Insurance,
                                            ReferralData.Accepted
                                        };



                    foreach (var ReferralData in ReferralResult)
                        // Update Referral Record
                        
                             myAddNewReferral.Accepted = "Y";

                             UpdateTable(dc, myAddNewReferral);
							 
							 
		private static void UpdateTable(DBDataContext db, Object record)
        {


            // Get the type of the record to insert
            string type = record.GetType().Name;




            if (type == "Referral")
            {


                try
                {
                    
                    db.SubmitChanges();
                    

                }

Open in new window

0
Comment
Question by:kwh3856
[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 Comments
 
LVL 14

Accepted Solution

by:
Vel Eous earned 250 total points
ID: 39170659
If only a single record is being returned by your LINQ to SQL statement, then there is no requirement for your foreach loop.  You also need to alter the value of the returned object (ReferralResult), not of the newly created (newAddNewReferral) if you are looking to update rather than insert.

With that in mind:
var ReferralResult = dc.Referrals.Where(r => r.REFGUID == resultguid).Single();
ReferralResult.Accepted = "Y";
dc.SubmitChanges();

Open in new window

0
 
LVL 10

Expert Comment

by:eguilherme
ID: 39171062
Have you stepped into the UpdateTable method? Does the if condition is met?

If so, the problem might be something else, does the Referral table have a primary key setted? Linq2Sql does not update records if it doesnt have a primary key
0
 
LVL 23

Assisted Solution

by:Ioannis Paraskevopoulos
Ioannis Paraskevopoulos earned 125 total points
ID: 39171088
Hi,

I would change:
foreach (var ReferralData in ReferralResult)
                        // Update Referral Record
                        
                             myAddNewReferral.Accepted = "Y";

                             UpdateTable(dc, myAddNewReferral);

Open in new window


To
foreach (var ReferralData in ReferralResult)
                        // Update Referral Record
                        
                             ReferralData .Accepted = "Y";

                             UpdateTable(dc, ReferralData );

Open in new window


Giannis
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:kwh3856
ID: 39171393
Tchuki and Jyparask,
I will give that a try.  I think both of you are saying the same thing and that is most likely the problem.  I will let you know.

Thanks
0
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 125 total points
ID: 39172680
Hi Kenny;

The record myAddNewReferral is created at the beginning of the code example and at that time is a empty record. You then make a query to the database searching for the record that has the ReferralData.REFGUID == resultguid and when it is found you return three fields of the record and which is an anonymous type. In the foreach loop, which is missing a { so I am not sure that the next three statement are in that loop. You modify the myAddNewReferral.Accepted with "Y". Now myAddNewReferral is the empty record you created at the beginning of the code and now you have initialized the Accepted field with "Y". You then call UpdateTable with the data context and the partially initialized record myAddNewReferral. In that method you test to see if it is the correct record type and then you save the record to the database. Is that what you wanted to do was save a new record with only one field initialized to the database?
0
 

Author Comment

by:kwh3856
ID: 39173721
Fernando,
That is what I needed to understand what I was actually doing.  I now understand that

Referral myAddNewReferral = new Referral();

creates a new record.  I thought I had to do that just so I could get access to reference the fields in the table.

I tried the code from tchuki and it worked perfect.  I tried the code from jyparask and there was a problem with the the two statements below.

ReferralData .Accepted = "Y";

UpdateTable(dc, ReferralData );

From reading each of your thoughts on how to solve this problem, I have learned greatly.  I would like to award each of you points for helping me to get a better understanding of LINQ.
0
 

Author Closing Comment

by:kwh3856
ID: 39173723
Thank you very much.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39173777
Not a problem, glad to help.
0

Featured Post

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!

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

749 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